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

December 21, 2017 | 4 minute read
Beda Hammerschmidt
Text Size 100%:

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 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 nested JSON fragment (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 will see more vendors supporting these operators since they're now part of the SQL 2017 standard.)

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));

  "name": "black",
  "rgb": [0,0,0],
  "hex": "#000000"

  "name": "orange red",
  "rgb": [255,69,0],
  "hex": "#FF4500"

"name": "dark orange",
"rgb": [255,140,0],
"hex": "#FF8C00"


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

Lets run this Select statement:

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

This query expression is probably the simplest version of a JSON_VALUE expression. The first argument (color) to the JSON_VALUE operator is the input (column name) which provides the JSON data – in this case it is the column named ‘color’.  Typically the input is a column name of a table in the FROM clause.  
The second parameter ('$.name') 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:

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

This returns

JSON_VALUE(color, '$.hex') 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;

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) so that 'dark orange' fits.
  • Truncate the output. This gives us a partial value instead of NULL. This *may* be useful in *some* use cases.
    FROM colorTab;
    Now the result is 'dark orang'    instead of NULL   (e is missing, got truncated) 
  • Tell the error handler to not suppress the error but raise it instead
    FROM colorTab; 

    We now should see #BADCOLOR# instead of the NULL value.



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

FROM colorTab;

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(

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

29-APR-15 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 USA but 2nd January in Germany or Mexico). Therefore we strongly recommend to only use one format (ISO 8601).


The opposite situation exists for Boolean values: JSON supports those. Oracle SQL does not. We return these values as VARCHAR2 strings:

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


You find more information int the JSON Developer's Guide:

Beda Hammerschmidt


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

First Steps with JSON: Simple Queries

Beda Hammerschmidt | 2 min read

Next Post

Oracle Database 18c : Now available on the Oracle Cloud and Oracle Engineered Systems

Guest Author | 14 min read