X
  • Friday, May 8, 2015

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

By: Beda Hammerschmidt | Consulting (Coding) Member of Technical Staff

The simplified syntax shown in the last blog allows easy but limited functionality to work with JSON data. This is why Oracle has added more powerful SQL operators to work with JSON data. Starting with database release 12.1.0.2 Oracle provides these new SQL operators:  

  • JSON_VALUE:  to select one scalar value in the JSON data and return it to SQL. (JSON_VALUE is the ‘bridge’ from a JSON value to a SQL value).
  • JSON_EXISTS: a Boolean operator typically used in the WHERE clause to filter rows based on properties in the JSON data.
  • JSON_QUERY: an operator to select (scalar or complex) value in the JSON data. In contrast to JSON_VALUE which always returns one scalar value, JSON_QUERY returns a JSON value (object or array). With JSON_QUERY a user can also select multiple values and have them wrapped inside a JSON array.
  • JSON_TABLE: the most powerful operator that exposes JSON data as a relational view. With JSON_TABLE you can turn your JSON data into a relational representation.

All operators have in common that they accept one (or more) path expressions to select values or fragments in the JSON data.
The operators have an ‘error handler’ which specified what to do in case an error is encountered, and options how selected values are being returned.
(You may see more relational vendors supporting these operators in the future since it's a standard proposal: goo.gl/SLT4SQ)

Before we look at the operators let’s create a table and insert some sample data.

DROP TABLE colorTab;

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

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

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

INSERT INTO colorTab VALUES('
{
"name": "dark orange",
"rgb": [255,140,0],
"hex": "#FF8C00"
}');

commit;

Now, let’s look at the new operator  JSON_VALUE.
I will explain error handlers and other concepts in a little more detail, this makes this posting a little longer. But since these concepts also apply for the other operators it makes sense to present them properly.

SELECT JSON_VALUE(color, '$.name') from colorTab;
---------------------------------------------------------
black
orange red
dark orange

This query expression is probably the simplest version of a JSON_VALUE expression. The first argument to the JSON_VALUE operator is the input (column name) which provides the JSON data – in this case it is the column named ‘color’.
The second parameter is a path expression. A path expression always starts with ‘$’. ('$' alone refers to the input data, later I will show how you can pass variables and refer to them using '$var'...).
After this you use dots and square brackets (similar to JavaScript) to navigate inside to the desired key/value pair.

As a regular SQL operator JSON_VALUE can also be used outside the SELECT list, for instance in the WHERE clause or ORDER BY clause and combined with other SQL operators. Let’s illustrate this in the next example:

SELECT
  JSON_VALUE(color, '$.hex'),
  JSON_VALUE(color, '$.name') from colorTab
WHERE UPPER(JSON_VALUE(color, '$.name')) like '%ORANGE%'
ORDER BY JSON_VALUE(color, '$.name');

---------------------------------------------------------
#FF8C00 dark orange
#FF4500 orange red

The default output of JSON_VALUE is a VARCHAR2(4000). Since names of colors are typical much shorter than 4000 characters lets make the output shorter. For this we use the (optional)RETURNING clause:

SELECT JSON_VALUE(color, '$.name' RETURNING VARCHAR2(10)) from colorTab;

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

What happens now? We only see two colors (‘black’ and ‘orange red’) . Instead of ‘dark orange’ we see a NULL. Why is that?
The color's name (
dark orange) does not fit into the 10 character wide output we specified. We encountered an error but the default error handler suppressed it and gave us a NULL instead.

At this point we have four options:

  • Bump the output to a larger value, e.g VARCHAR2(20)
  • Truncate the output , this gives us a partial value instead of NULL
    • SELECT JSON_VALUE(color, '$.name' RETURNING VARCHAR2(10) TRUNCATE)
      FROM colorTab;
    • ----------
      black
      orange red
      dark orang
             (e is missing) 

  • Tell the error handler to not suppress the error but raise it instead
    • SELECT JSON_VALUE(color, '$.name' RETURNING VARCHAR2(10) ERROR ON ERROR) FROM colorTab;
    • ERROR:
      ORA-40478: output value too large (maximum: 10)

  • Tell the error handler to return a default value instead of NULL
    • SELECT JSON_VALUE(color, '$.name' RETURNING VARCHAR2(10) DEFAULT '#BADCOLOR#' ON ERROR)
      FROM colorTab; 
    • ----------
      black
      orange red
      #BADCOLOR#

At this point you may understand why we call the simplified syntax ‘simplified’ ;-) But keep in mind that all these options have valid use cases and you may appreciate that they exist!

The RETURNING clause also allows us to return non-textual values, for instance a number:

SELECT JSON_VALUE(color, '$.rgb[0]' RETURNING NUMBER) "RED",
JSON_VALUE(color, '$.rgb[1]' RETURNING NUMBER) "GREEN",
JSON_VALUE(color, '$.rgb[2]' RETURNING NUMBER) "BLUE"
FROM colorTab;

       RED GREEN    BLUE
---------- ---------- ----------
        0    0       0
       255   69       0
       255  140       0

Please note that this example also shows how an array access is specified in a path expression using the square brackets [], like in JavaScript.

Other return data types you can chose from include RAW, DATE and TIMESTAMP (there is no format clause for Date and Timestamp!!, the date has to follow ISO 8601 format(http://en.wikipedia.org/wiki/ISO_8601)).

SELECT JSON_VALUE ('{"created" : "2015-04-29T15:16:55"}', '$.created' RETURNING TIMESTAMP)
FROM DUAL;

---------------------------------------------------------------------------
29-APR-15 03.16.55.000000 PM

The last output might look differently for you if you NLS settings are different than mine. 

Dates, Timestamps 

You may ask now why there is there no format clause? The reason is that JSON has no syntax to represent a date. So in JSON a date would be represented as a string. If different formats were used the JSON date value would not be self-contained anymore, one would need additional information to interpret it. We believe that this has the potential of creating headaches down the road where
people misinterpret dates (e.g. “2-1-2015” is February, 1st in the US but 2nd January in Germany). Therefore we strongly recommend to only use one format (ISO 8601).

Booleans

The opposite situation exists for Boolean values: JSON supports those. Oracle not (at least not in SQL, PL/SQL is different). We gave you two choices: as string you see the values ‘true’| ‘false’ or when the RETURN CLAUSE specifies a number we return 1 | 0.

SELECT JSON_VALUE('{"a":true}', '$.a') FROM DUAL;
----------------------------------------------

true

SELECT JSON_VALUE('{"a":true}', '$.a' RETURNING NUMBER) FROM DUAL;
----------------------------------------------

1

We invite you to provide feedback; for instance as a comment under this 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.Captcha