SQL JSON query operators – Part I

The simplified syntax shown in the last blog allows easy but limited functionality with JSON data, Hence Oracle has provided more powerful SQL operators to work with JSON data starting 12c(12.1.0.2)

• JSON_VALUE: to select one scalar value in the JSON data and return it to the SQL
• JSON_EXISTS: a Boolean operator typically used in the WHERE clause to filter rows based on the property in the JSON data.
• JSON_QUERY: an operator to select value in the JSON data, in contrast to JSON_VALUE which always return one scalar value, JSON_QUERY returns JSON value (object or array), with JSON_QUERY user can 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 could turn a JSON data into a relational representation.

These operators have and ‘error handler’ which specified what to do in case of an error is encountered, and options how the selected values are being returned.

create table t(

doc_id int,

doc_name clob,

constraint is_valid_json check

(doc_name is json) );

insert into t(doc_id,doc_name)

 values(1, '{

 "ColorName": "black",

 "rgb": [0,0,0],

 "hex": "#000000"

 }');

insert into t(doc_id,doc_name)

 values(2, '{

 "ColorName": "light blue",

 "rgb": [255,69,78],

 "hex": "#FFFFFF"

 }'); 

insert into t(doc_id,doc_name)

 values(3, '{

 "ColorName": "dark yellow",

 "rgb": [72,53,68],

 "hex": "#AB02CD"

 }');  

commit;

Using simple dotted notation, the query to access the “colorname” attribute would be like this.

demo@ORA12C> select t1.doc_name.ColorName from t t1;

COLORNAME

--------------------

black

light blue

dark yellow

3 rows selected.

demo@ORA12C> 

If you drop the check constraint, then the simple dotted notation will not even work and result in errors.

demo@ORA12C> alter table t drop constraint is_valid_json;

Table altered.

demo@ORA12C> select t1.doc_name.ColorName from t t1;

select t1.doc_name.ColorName from t t1

       *

ERROR at line 1:

ORA-00904: "T1"."DOC_NAME"."COLORNAME": invalid identifier

But when using SQL JSON query operator to access the JSON document, it don’t even require the check constraint, even without the check constraint in place, it could execute successfully.

demo@ORA12C> select json_value(doc_name,'$.ColorName') as colorname

  2  from t;

COLORNAME

--------------------

black

light blue

dark yellow

3 rows selected.

demo@ORA12C>

This above query uses the simplest version of JSON_VALUE expression. The first argument to the JSON_VALUE operator is the input column_name that provides JSON data. The second argument is the path expression. A path expression starts with $ after which you can use either dot or square brackets (similar to Java script) to navigate inside to the desired key/value pairs.

Similar to the other SQL operators JSON_VALUE can be used anywhere in the SQL statements.

demo@ORA12C> select doc_id

  2  from t

  3  where json_value( doc_name ,'$.ColorName') like '%b%'

  4  order by json_value( doc_name ,'$.ColorName') desc;

    DOC_ID

----------

         2

         1

2 rows selected.

demo@ORA12C>

The default output of JSON_VALUE is varchar2(4000), since the name of the color is less than 4000 characters, we can request the JSON_VALUE to return something less than 4000 characters. 

demo@ORA12C> select json_value(doc_name,'$.ColorName' RETURNING VARCHAR2(10)) as colorname

  2  from t;

COLORNAME

--------------------

black

light blue

3 rows selected.

demo@ORA12C>

What happens now? Instead of three values we see only two, what happens to “dark yellow” we see NULL instead.

The color name “dark yellow” don’t fit with in Varchar2(10), so we encountered an error, but the default error handler (error on null) for SQL JSON function suppressed it and gave us a NULL instead.

At this point we have three options

Increase the output value a larger value

demo@ORA12C> select json_value(doc_name,'$.ColorName' RETURNING VARCHAR2(20)) as colorname

  2  from t;

COLORNAME

--------------------

black

light blue

dark yellow

3 rows selected.

demo@ORA12C>

Tell the error handler to return a default value instead.

demo@ORA12C> select json_value(doc_name,'$.ColorName' RETURNING VARCHAR2(10)

  2              DEFAULT '#BAD_VALUE' on error) as colorname

  3  from t;

COLORNAME

--------------------

black

light blue

#BAD_VALUE

3 rows selected.

Tell the error handler to not suppress the error and raise it instead.

demo@ORA12C> select json_value(doc_name,'$.ColorName' returning varchar2(10)

  2              ERROR ON ERROR ) as colorname

  3  from t;

ERROR:

ORA-40478: output value too large (maximum: )

no rows selected

The returning clause also allows us to return non-textual values.

demo@ORA12C> select json_value(doc_name,'$.rgb[0]' returning number) red,

  2         json_value(doc_name,'$.rgb[1]' returning number) blue,

  3         json_value(doc_name,'$.rgb[2]' returning number) green

  4  from t        ;

       RED       BLUE      GREEN

---------- ---------- ----------

         0          0          0

       255         69         78

        72         53         68

3 rows selected.

demo@ORA12C>

The other possible return data types are Boolean values, JSON support those but Oracle (at least PL/SQL does, but not SQL) not, but we have two choices to return either as string (true|false) or number (1|0).

demo@ORA12C> select json_value( '{a:true}','$.a' ) from dual;

JSON_VALUE('{A:TRUE}','$.A')

------------------------------------------------------------------------------

true

1 row selected.

demo@ORA12C> select json_value( '{a:true}','$.a' returning number) from dual;

JSON_VALUE('{A:TRUE}','$.A'RETURNINGNUMBER)

-------------------------------------------

                                          1

1 row selected.

demo@ORA12C>