First Steps with JSON: Simple Queries

December 20, 2017 | 2 minute read
Beda Hammerschmidt
Architect
Text Size 100%:

In the previous posting of this series we learned how to store JSON data in the Oracle database.
Now let’s query it.

First let’s run these statements to  create a table and inserts two rows with JSON data:

DROP TABLE colorTab;

CREATE TABLE colorTab (
    id NUMBER, 
    color VARCHAR2(32767),
    CONSTRAINT ensure_json CHECK (color IS JSON));

INSERT INTO colorTab VALUES(1, '
 {
  "name":"black",
  "rgb":[0,0,0],
  "hex":"#000000"
 }
');

INSERT INTO colorTab VALUES(2, '
 {
  "name": "orange red",
  "rgb":[255,69,0],
  "hex": "#FF4500"
 
}');

commit;

 

You query the data by simple 'dotting' you way to the desired field to select the associated value. This is basically the same as you would do it in Java Script.

 

SELECT c.color.name from colorTab c;

NAME
--------------------------------------------------------------------------------
black
orange red

returns us two rows with the values ‘black’ and ‘orange red’.

Please note that in the SELECT clause ‘c’ is the table
alias, ‘color’ is the column name and ‘name’ is a field inside the JSON data.

SELECT
 c.color.rgb[0] "RED",
 c.color.rgb[1] "GREEN", 
 c.color.rgb[2] "BLUE" 
FROM colorTab c;

This returns values of the three color components Red, Green and Blue in a table that has a separate column for each component:

RED GREEN BLUE
0 0 0
255 69 0

 

So what happens if we use a key name that does not exist in
the data, let’s say ‘description’?

SELECT c.color.description from colorTab c;

We get back a NULL value. No error message. This is because
in many JSON use cases there are optional fields which do not exist in every
instance . It would be inconvenient if such flexible schema would force us to write
different queries. This is why we return NULL for such a case (you will learn
later how such an error can be reported).

The simplified syntax also allows us to select a complex
value (i.e. an inner array or object).

select c.color.rgb from colorTab c;

will return the whole array as a value for each row.

RGB
--------------------------------------
[0,0,0]
[255,69,0]

 

The simplified syntax works very well for simple queries like the one illustrated above but it comes with some limitations: The output is always
a VARCHAR2(4000). Any error will be masked by NULL. And it requires the presence of the check constraint that marks a column as a JSON column. For more
generic JSON processing we have added a set of new operators that I will describe in the next posting.

You find the JSON developer's Guide here: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/toc.htm

Beda Hammerschmidt

Architect

Software developer @ Oracle Server Technologies with 15+ yrs experience with JSON and XML. Coding JSON features in the Oracle Database. Co-author of the SQL/JSON standard (SQL 2016). 


Previous Post

Storing JSON data in the Oracle database

Beda Hammerschmidt | 3 min read

Next Post


The new SQL/JSON Query operators (Part1): JSON_VALUE

Beda Hammerschmidt | 4 min read