Example Queries on the exampleJsonTable

To use Oracle Big Data SQL to query data in the Oracle NoSQL Database exampleJsonTable, you can execute queries like the following on the Oracle Database external table you mapped to that table in the Oracle NoSQL Database store. For example,

set linesize 500;

col id format 9999; 
col jsonfield format A1000;

SELECT * FROM exampleJsonTable WHERE ROWNUM <= 5;

The following queries use various combinations of JSON dot notation, the JSON_VALUE operator, and the JSON_QUERY operator to query and display only specific attributes of the JSON document in each row of the Oracle NoSQL Database table.

Query Using Only JSON Dot Notation

col personal format A15; 
col party format A15;

SELECT id, j.jsonfield.personal.firstname, 
    j.jsonfield.personal.lastname, j.jsonfield.party
    FROM exampleJsonTable j 
    WHERE j.jsonfield.party = 'Independent'
    ORDER BY j.jsonfield.person.lastname;

Query Using JSON Dot Notation and the JSON_VALUE Operator

col firstname format A15; 
col lastname format A15; 
col homephone format A12; 
col workphone format A12;

SELECT id,
    JSON_VALUE(j.jsonfield, '$.personal.firstname') firstname, 
    JSON_VALUE(j.jsonfield, '$.personal.lastname') lastname, 
    JSON_VALUE(j.jsonfield, '$.personal.party') party, 
    JSON_VALUE(j.jsonfield, '$.personal.address.home.phone') homephone, 
    JSON_VALUE(j.jsonfield, '$.personal.address.work.phone') workphone
    FROM exampleJsonTable j 
    ORDER BY j.jsonfield.party;

Query Using JSON Dot Notation and the JSON_QUERY Operator

col committee format A25; 
col caucus format A25;

SELECT id, j.jsonfield.personal.firstname, 
    j.jsonfield.personal.lastname, 
    JSON_QUERY(j.jsonfield, '$.duties.committee' PRETTY WITH WRAPPER) committee, 
    JSON_QUERY(j.jsonfield, '$.duties.caucus' PRETTY WITH WRAPPER) caucus
    FROM exampleJsonTable j 
    WHERE j.jsonfield.party = 'Democrat' AND ROWNUM <= 5;

Query Using JSON Dot Notation With Both JSON_VALUE and JSON_QUERY

col contrib format A12;
col committee format A50;
col contrib format A50;

SELECT 
    JSON_VALUE(j.jsonfield, '$.personal.firstname') firstname,
    JSON_VALUE(j.jsonfield, '$.personal.lastname') lastname,
    j.jsonfield.contrib, 
    j.jsonfield.party, 
    JSON_QUERY(j.jsonfield, '$.duties.committee' PRETTY WITH WRAPPER) committee, 
    JSON_QUERY(j.jsonfield, '$.duties.caucus' PRETTY WITH WRAPPER) caucus
    FROM exampleJsonTable j 
    WHERE j.jsonfield.party = 'Republican'  AND ROWNUM <= 5;