Examining JSON functions in

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


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


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


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.



Example 1

select *
from JSON_TABLE('{"data":{"a":[1,2,3],"b":[0,1,2],"c":"nur ein test"}}'
       columns ("A_json" varchar2(100) format json path '$.a'
               ,"B_check" varchar2(10) EXISTS path '$.b'
               ,"C_text" varchar2(100) path '$.c'
               )) tab
[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');


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


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 '$'))




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 '$')));
[[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.


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



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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.