Examining JSON functions in 12.1.0.2

In preparation of a new version of my Quickreference SQL Advanced Functions i had a closer look at the new SQL JSON possibilities which are available in Oracle DB 12.1.0.2.

Overview

There are three functions and three conditions that all deal with JSON.

  • json_value – a function that returns a single (skalar) value
  • json_query – a function that returns a json string (can be an array)
  • json_table – a function that provides table logic based upon a json document.
  • is [not] json – checks if the document is json or not
  • json_exists – checks if a certain element or structure exists in the json document
  • json_textcontains – efficient search through a json document using an oracle text index

JSON_TABLE is the most versatile of all json functions. It can do everything that json_value, json_query and json_exists can do and more than that. So if you want to learn more about the json functions in 12c then concentrate on JSON_TABLE. See also: JSON_TABLE Generalizes Other Oracle SQL Functions

Call Parameters

data is [not] json
json_exists(data, $path on_error)
json_textcontains(column, $path, searchstring)
json_query(data, $path returning query_wrapper on_error)
json_table(data, $path returning on_error COLUMNS columnlist)
json_value(data, $path returning on_error)

data:  json varchar2, clob or a blob; blobs need to add “FORMAT JSON”

$path: json search path

on_error: action when return error happens;
ERROR|NULL|EMPTY|DEFAULT literal|TRUE|FALSE ON ERROR

ERROR, NULL for all functions
TRUE|FALSE   for json_exists
EMPTY             for json_query
DEFAULT          for json_table, json_value

returning :return data type and formatting; PRETTY ASCII

query_wrapper  : WITH|WITHOUT ARRAY WRAPPER

columnlist  : json column list

EXISTS PATH             like json_exists
PATH                            like json_query or json_value
NESTED PATH           single row unnesting
FOR ORDINALITY    row numbers

Understanding JSON path expressions

All functions and conditions can use a json path expression.

A JSON_PATH expression uses dot-notation to separate steps. Each step an be an object or an array step.

Syntax $path (object or array)

$
[ { . { * | simplename | ”complexname” } }
   | 
   { [ { integer [TO integer]     
         [, integer [TO integer]
         ]…
         } 
         | *
      ]
   }
] …

Some examples for json path expressions

$.ShippingInstructions.Address.zipCode
$.a.b.c
$.a[3].b[*].c[0]

The json_table function will map columns based upon a json_path expression.
The columns can be defined using an expression resembling the JSON_VALUE, JSON_QUERY or JSON_EXISTS syntax.

 

Examples

Example 1

select *
from JSON_TABLE('{"data":{"a":[1,2,3],"b":[0,1,2],"c":"nur ein test"}}'
               ,'$.data'
       columns ("A_json" varchar2(100) format json path '$.a'
               ,"B_check" varchar2(10) EXISTS path '$.b'
               ,"C_text" varchar2(100) path '$.c'
               )) tab
;
A_JSON B_CHECK C_TEXT
[1,2,3] true nur ein test

Column A uses a json_query syntax. The “format json” directive allows to return a more complex json object.

Column B uses a json_exists syntax. If the column is declared as varchar2 then true/false is return. If it is declared as number then 1/0 is returned.

Column C uses the default json_value syntax.

Example 2

select *
from json_table('{a:[5, 10, 15]}', '$'
columns (val1 varchar2(20) format json path '$.a');
VAL1
[5,10,15]

 

select *
from json_table('{a:[5, 10, 15]}', '$'
      columns (nested path '$.a[*]' columns (val2 number path '$'))
);
VAL2
5
10
15

 

And now the combination of both columns in one query.

select *
from json_table('{a:[5, 10, 15]}', '$'
      columns (val1 varchar2(20) format json path '$.a'
              ,nested path '$.a[*]' columns (val2 number path '$'))
);

 

VAL1 VAL2
5
10
15
15

 

This result is a bug!

There is a patch available from oracle metalink, that corrects this.

patch 20080249: “JSON Patch Bundle 1”. MOS Doc 1992767.1

It is strongly recommended to install this patch when planning to work with JSON data in the database.

An error happens, but the json function does return an empty string and do not raise the error.

We can get some more info by adding the “ERROR ON ERROR” clause.

select *
from json_table('{a:[5, 10, 15]}', '$'
error on error
columns (val1 varchar2(20) format json path '$.a' ,
         nested path '$.a[*]' columns (val2 number path '$')));

ORA-40480: result cannot be returned without array wrapper

Now that we see the error message, we can try to react on it by adding “WITH ARRAY WRAPPER”. for column val1

select *
from json_table('{a:[5, 10, 15]}', '$'
error on error
columns (val1 varchar2(20) format json WITH ARRAY WRAPPER path '$.a' ,
         nested path '$.a[*]' columns (val2 number path '$')));
VAL1 VAL2
[[5] 5
,[10] 10
,[15] 15
,[]] 15

 
This result is still wrong. But at least we got some indication about what happend to our data. To solve the issue we need to install the patch or do a different kind of query.

Fazit

Using json functions in the database is easy. But just as with any other new technology we need a little time to get used to it. Also expect a few minor bugs at the beginning.

 

Other reads

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s