X

A blog about JSON in the Oracle database Technology

  • May 7, 2015

Simple Queries

Beda Hammerschmidt
Consulting (Coding) Member of Technical Staff

In the previous blogs we learned how to store JSON data in
Oracle. 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 STRICT));

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;

Simplified Syntax

The so called ‘simplified syntax’ allows to select keys
inside the JSON data and to return their corresponding value. You select a key
by navigating to it using a ‘path expression’. Sounds complex but it’s not
really: you uses a dot (‘.’) to navigate inside an object and square brackets
(‘[]’) to navigate inside an array. Sounds familiar? It’s pretty much the same
as you would write in JavaScript. So let’s go:

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 (you must give the table an alias to use it to query the json), ‘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 three columns: the values of the three color
components Red, Green and Blue.

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 next.

 

We invite you to give us feedback; for instance as a comment under each posting or in our LinkedIn group (https://www.linkedin.com/grp/home?gid=6966766). You can also follow the Twitter hashtag #OracleJSON and #OracleDocDB.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.