SQL JSON String Functions

A SQL JSON function allows creation, searching, and modification of JSON data represented as SQL strings. They can be used with any string values that contain valid JSON data types.

In the JSON functions below, all arguments refer to a string, which can be a string literal, table column, or the result of an expression. The arguments target, candidate, and document must be a valid JSON document consisting of one or more JSON types. The argument path must be a valid path expression which consists of an appropriately formatted set of indexes and/or keys.

JSON Types

NuoDB does not implement a dedcated JSON data type. Instead NuoDB can parse and manipulate JSON documents encoded into SQL strings. Functions and operators manipulating JSON data will decode SQL string literals using the following conventions:

JSON Type Example

string

"abc"

number

1234
56.78

boolean

true
false

null

null

array

[1,2,3]
[[4,5],["a","b","c"]]

object

{"key":"value"}
{"a":1,"b":{"c":3,"d":4}}

SQL NULL and JSON null are not the same type. The JSON null type is case-sensitive. The use of lowercase letters are valid. Mixed case or upper case letters will result in INVALID_JSON_TEXT error.

Path Expression

A function argument requiring a path must have a correctly formatted path expression depending on the type of JSON structure searched. If the document is an array, one or more indexes must be provided. For an object, one or more keys must be provided. Since arrays and objects allow nesting, you may provide a path containing both keys and indexes. Where functions require a valid index or key, an index is considered invalid if it exceeds the bounds of the array, and a key is considered invalid if it does not exist within the JSON object. Wildcards may be used within a path expression if the JSON function allows it.

If an object key contains white space, it must be enclosed in double quotes.
Expression Type Format Example

Index

$[index1][index2]…​

$[2]
$[2][0][3]

Key

$.key1.key2…​

$.year
$.year.month.day.minute

Mixed (index and key)

$.key[index]…​
$[index].key…​

$.day[4]
$[5].city

The JSON standard functions JSON_EXISTS, JSON_QUERY, and JSON_VALUE have an optional path mode specifier. lax mode is the default. In this mode a structural error is converted to an empty SQL JSON sequence. strict mode converts a structural error to a hard error.

Expression Mode Format Example

lax

lax $.key1[index1]…​

lax $.year
lax $[2][0][3]

strict

strict $.key1[index1]…​

strict $.year
strict $[2][0][3]

The lax and strict keywords must be lowercase.

Get Attribute Function

The function described in the following table returns the attributes of the JSON document.

Function Description

IS_VALID_JSON(document)

Return TRUE if the document is a valid JSON document. It will return FALSE if the document is not a valid JSON type or is incorrectly formed. It will return NULL if document is NULL.


Return type: BOOLEAN

SELECT IS_VALID_JSON('{"key1":100,"key2":200}') FROM dual;
 [IS_VALID_JSON]
 ---------------
      TRUE

SELECT IS_VALID_JSON('Invalid JSON string') FROM dual;
 [IS_VALID_JSON]
 ---------------
     FALSE

Create Function

The function described in the following table creates a JSON type value.

Function Description

JSON_OBJECT([key, value [FORMAT JSON] [, key, value] …​])

Returns a correctly formatted JSON object as a SQL string. The object is created from a list of key/value pairs. The list can be empty.

An optional FORMAT JSON clause can be used with any value. If the value is a SQL string and the clause is not used then the value is treated as a JSON string. With the clause the value is treated as a JSON type value.

An error occurs if there are an odd number of arguments, any key is null, or the FORMAT JSON clause is used with the key argument.


Return type: STRING

SELECT JSON_OBJECT('key1', 'value1') FROM dual;
   [JSON_OBJECT]
 -----------------
 {"key1":"value1"}

SELECT JSON_OBJECT('a', 2, 'b', '{"c" : 3}' FORMAT JSON) FROM dual;
    [JSON_OBJECT]
 -------------------
 {"a":2,"b":{"c":3}}

Search Functions

The functions in the following table performs search operations on the JSON document.

Function Description

JSON_CONTAINS(target, candidate[, path])

Returns TRUE or FALSE to indicate the presence of the candidate JSON value within the target JSON document. If a path argument is provided the function will indicate if the candidate is present at that path only, within the target JSON document. The search is recursive from the top level down. So, if no path is provided, then the whole document will be searched. However, if a path is provided then, the entire section of the document contained within that path will be searched.

A NULL will be returned if any argument is NULL, or if the path is not a valid path within the target document.

An error occurs if target or candidate is not a valid JSON document, the path argument is not a valid path expression, or the path expression contains a star wildcard (*).


Return type: BOOLEAN

SELECT JSON_CONTAINS('{"a":1, "b":2}', '2') FROM dual;
 [JSON_CONTAINS]
 ----------------
       TRUE

SELECT JSON_CONTAINS('[10,[20,30]]', '30', '$[1][0]') FROM dual;
 [JSON_CONTAINS]
 --------------
      FALSE

JSON_CONTAINS_PATH(target, one_or_all, path[, path] …​)

Returns TRUE if the target document contains data at a given path or paths for at lease one path or all paths depending on one_or_all condition. Return FALSE otherwise.

For ONE it will return true if at least one path is contained within the target document. For ALL it will return true if all paths are contained within the target document.

Returns NULL if any argument is NULL.

An error occurs if the target is not a valid JSON document, or if any path argument is not a valid expression, or if one_or_all is not 'ONE' or 'ALL' ('ONE' and 'ALL' are not case sensitive).


Return type: BOOLEAN

SELECT JSON_CONTAINS_PATH('[10,[20,30]]', 'ONE', '$[1][0]') FROM dual;
 [JSON_CONTAINS_PATH]
 ---------------------
         TRUE

SELECT JSON_CONTAINS_PATH('[10,[20,30]]', 'ALL', '$[1][99]') FROM dual;
 [JSON_CONTAINS_PATH]
 ---------------------
         FALSE

JSON_EXISTS(target, [<path mode>] path [<error mode> ON ERROR] )

Returns TRUE if the target document contains data at a given path. Returns FALSE otherwise.

Returns NULL if any argument is NULL.

An error occurs if the target is not a valid JSON document, or if the path argument is not a valid path expression.

The <path mode> can be lax or strict. lax is the default.

An optional ON ERROR clause can be used to set a known return value in the event of an error.

ON ERROR modes:

An error condition only occurs in strict path mode. In strict mode invalid paths (structural errors) are treated as hard error.

  • TRUE ON ERROR - Returns TRUE on error

  • FALSE ON ERROR - Default option. Returns FALSE on error.

  • UNKNOWN ON ERROR - Returns NULL on error.

  • ERROR ON ERROR - Throws SQL error on error.


Return type: BOOLEAN

SELECT JSON_EXISTS('[10,[20,30]]', '$[1][0]') FROM dual;
 [JSON_EXISTS]
 --------------
      TRUE

SELECT JSON_EXISTS('[10,[20,30]]', '$[1][99]') FROM dual;
 [JSON_EXISTS]
 --------------
     FALSE

SELECT JSON_EXISTS('[10,[20,30]]', 'strict $[1][99]' TRUE ON ERROR) FROM dual;
 [JSON_EXISTS]
 --------------
      TRUE

JSON_EXTRACT(document, path[, path] …​)

Returns JSON data from the document found at each corresponding path. The star wildcard (*) path expression can be used to return data from multiple paths.

Returns NULL if argument is NULL, all paths do not contain a value, or the document is not a structured JSON type (array or object).


Return type: STRING

SELECT JSON_EXTRACT('{"person":{"age":45}}', '$.person.age') FROM dual;
 [JSON_EXTRACT]
 ---------------
       45

 SELECT JSON_EXTRACT('[[1,2],[3,4]]', '$[*][0]') FROM dual;
 [JSON_EXTRACT]
 ---------------
      [1,3]

JSON_QUERY(target, [<path mode>] path [<wrap mode> WRAPPER] [<empty mode> ON EMPTY] [<error mode> ON ERROR] )

Returns JSON data from target document if value at path is an array or object.

The <path mode> can be lax or strict. lax is the default.

There are three optional clauses that can be used WRAPPER, ON EMPTY, and ON ERROR.

WRAPPER modes:

The WRAPPER mode clause allows the JSON result to be conditionally or unconditionally enclosed in array or object structure.

  • WITHOUT [ARRAY] WRAPPER - Default option. Returns JSON value at path unchanged.

  • WITH CONDITIONAL [ARRAY] WRAPPER - Returns the JSON value enclosed in an array only if it is not an array or object already.

  • WITH UNCONDITIONAL [ARRAY] WRAPPER - Returns the JSON value enclosed within an array. If it is already an array or object it is nested within a new array.

ON EMPTY modes:

If a path uses lax mode and there is not a valid value at the path, the ON EMPTY clause determines the return value.

  • NULL ON EMPTY - Default option. Returns NULL value.

  • ERROR ON EMPTY - Throws SQL error.

  • EMPTY ARRAY ON EMPTY - Returns empty JSON array.

  • EMPTY OBJECT ON EMPTY - Returns empty JSON object.

ON ERROR modes:

The ON ERROR clause determines the return value in the event of an error.

  • NULL ON ERROR - Default option. Returns NULL value.

  • ERROR ON ERROR - Throws SQL error.

  • EMPTY ARRAY ON ERROR - Returns empty JSON array.

  • EMPTY OBJECT ON ERROR - Returns empty JSON object.

An error occurs in the following situations:

  1. The JSON document cannot be parsed

  2. The JSON path expression cannot be parsed

  3. Returned value is scalar

  4. Empty value returned in strict path mode

The ON EMPTY clause is prohibited if WITH [ARRAY] WRAPPER is specified.


Return type: STRING

SELECT JSON_QUERY('{"key":["value"]}', '$.key') FROM dual;
 [JSON_QUERY]
 -------------
   ["value"]

SELECT JSON_QUERY('{"key":"value"}', '$.key' WITH WRAPPER) FROM dual;
 [JSON_QUERY]
 -------------
   ["value"]

SELECT JSON_QUERY('"abc"', '$.something' EMPTY ARRAY ON EMPTY) FROM dual;
 [JSON_QUERY]
 -------------
      []

SELECT JSON_QUERY('"abc"', 'strict $.something' EMPTY OBJECT ON ERROR) FROM dual;
 [JSON_QUERY]
 -------------
      {}

JSON_VALUE(target, [<path mode>] path [RETURNING <data type>] [<empty mode> ON EMPTY] [<error mode> ON ERROR])

Returns JSON data from target document if value at path is scalar.

The <path mode> can be lax or strict. lax is the default.

There are three optional clauses that can be used ON EMPTY, ON ERROR, and RETURNING.

ON EMPTY modes:

If a path uses lax mode and there is not a valid value at the path, the ON EMPTY clause determines the return value.

  • NULL ON EMPTY - Default option. Returns NULL value.

  • ERROR ON EMPTY - Throws SQL error.

  • DEFAULT <value> ON EMPTY - Returns given default value <value>.

ON ERROR modes:

The ON ERROR clause determines the return value in the event of an error.

  • NULL ON ERROR - Default option. Returns NULL value.

  • ERROR ON ERROR - Throws SQL error.

  • DEFAULT <value> ON ERROR - Returns given default value <value>.

RETURNING modes:

The RETURNING clause returns data of a specific type. A STRING is returned by default. The following types are supported:

  • RETURNING STRING - Default option. Returns string value.

  • RETURNING INTEGER - Returns integer value.

  • RETURNING DOUBLE - Returns double value.

  • RETURNING BOOLEAN - Returns boolean value.

An error occurs in the following situations:

  1. The JSON document cannot be parsed

  2. The JSON path expression cannot be parsed

  3. Output conversion failure

  4. Returned value is not a scalar

  5. Empty value returned in strict path mode

If the JSON value cannot convert to the chosen return type an error occurs. The ON ERROR clause handles this.


Return type: STRING | INTEGER | DOUBLE | BOOLEAN

SELECT JSON_VALUE('[11,22,33]', '$[0]') FROM dual;
 [JSON_VALUE]
 -------------
      11

SELECT JSON_VALUE('{"value":12.34}', 'lax $.value' RETURNING DOUBLE) FROM dual;
 [JSON_VALUE]
 -------------
     12.34

select JSON_VALUE('[1,2]', 'lax $[99]' NULL ON EMPTY) FROM dual;
 [JSON_VALUE]
 -------------
    <null>

Select JSON_VALUE('[1,2]', 'strict $[99]' DEFAULT 'N/A' ON ERROR) FROM dual;
 [JSON_VALUE]
 -------------
      N/A

Modify Functions

The functions in the following table return a modified result of the input JSON document(s).

Function Description

JSON_MERGE_PATCH(document, document[, document] …​)

Returns the result of merging two or more JSON documents. The merge will happen two documents at a time moving left to right, obeying the following rules:

  1. If either document is not a JSON object type, then the second document will be returned.

  2. If the key in the second document is not contained within the first document then add it to the first document and return the first document.

  3. If the second document contains the same key as the first document, that value in the first document will be replaced and the first document will be returned. If the key contains a nested object then the values will be recursively merged following the same rules.

  4. If the value in the second document is null, then that key will be removed from the first document and the first document will be returned.


Return type: STRING

SELECT JSON_MERGE_PATCH('{"a":1}', '{"b":2}') FROM dual;
 [JSON_MERGE_PATCH]
 -------------------
    {"a":1,"b":2}

SELECT JSON_MERGE_PATCH('{"a":1}', '{"a":5,"b":2}') FROM dual;
 [JSON_MERGE_PATCH]
 -------------------
    {"a":5,"b":2}

JSON_UNQUOTE(json_value)

Returns the input json_value with its surrounding quotes removed. If json_value is not a JSON string literal, it will return the input value unchanged.


Return type: STRING

SELECT JSON_UNQUOTE('"abc"') FROM dual;
 [JSON_UNQUOTE]
 ---------------
       abc

More Examples

A few more examples using these JSON functions are given below.

Example 1

IS_VALID_JSON

CREATE TABLE documents(doc STRING CHECK(IS_VALID_JSON(doc)));
INSERT INTO documents VALUES('not valid json');

 Error 23001: violation of constraint "DOC"

INSERT INTO documents VALUES('"valid json"');
SELECT * FROM documents;

     DOC
 ------------
 "valid json"

Example 2

JSON_MERGE_PATCH

CREATE TABLE records (old STRING, new STRING);
INSERT INTO records VALUES ('{"ID":502,"Owner":"Alice","Status":"Active"}', '{"Owner":"Alice","Status":"Inactive"}');
SELECT JSON_MERGE_PATCH(old, new) AS "Latest Record" FROM records;
                Latest records
 ----------------------------------------------
 {"ID":502,"Owner":"Alice","Status":"Inactive"}

Example 3

JSON_CONTAINS

CREATE TABLE exam_results (id INT, name STRING, answer_sheet STRING);
INSERT INTO exam_results VALUES
(12, 'John', '{"Section A":{"Question 2":{"Question":"What is the value of PI?","Answer":3.1415}}, "Section B":{"Question 7":{"Question":"What is the Capital of France?","Answer":"Paris"}}}'),
(34, 'Paul', '{"Section A":{"Question 2":{"Question":"What is the value of PI?","Answer":3.1415}}, "Section B":{"Question 7":{"Question":"What is the Capital of France?","Answer":"Paris"}}}'),
(56, 'George', '{"Section A":{"Question 2":{"Question":"What is the value of PI?","Answer":3.5}}, "Section B":{"Question 7":{"Question":"What is the Capital of France?","Answer":"Paris"}}}'),
(72, 'Ringo', '{"Section A":{"Question 2":{"Question":"What is the value of PI?","Answer":42}}, "Section B":{"Question 7":{"Question":"What is the Capital of France?","Answer":"Rome"}}}');

SELECT id, name FROM exam_results WHERE JSON_CONTAINS(answer_sheet, '3.1415', '$."Section A"."Question 2"."Answer"') = true;

 ID  NAME
 --- -----
 12  John
 34  Paul

Example 4

JSON_CONTAINS_PATH

SELECT COUNT(*) FROM exam_results WHERE JSON_CONTAINS_PATH(answer_sheet, 'ALL', '$."Section A"."Question 2"."Answer"') = true;
 [COUNT]
 --------
    4

Example 5

JSON_EXTRACT

SELECT name, JSON_EXTRACT(answer_sheet, '$."Section B"."Question 7"."Answer"') AS "Capital of France" FROM exam_results;
 NAME  Capital of France
 ------ -----------------
 John       "Paris"
 Paul       "Paris"
 George     "Paris"
 Ringo      "Rome"

Example 6

JSON_UNQUOTE

SELECT name, JSON_UNQUOTE(JSON_EXTRACT(answer_sheet, '$."Section B"."Question 7"."Answer"')) AS "Capital of France" FROM exam_results;
 NAME  Capital of France
 ------ -----------------
 John        Paris
 Paul        Paris
 George      Paris
 Ringo       Rome

Example 7

JSON_OBJECT

SELECT JSON_OBJECT() FROM dual;
 [JSON_OBJECT]
 --------------
       {}

 CREATE TABLE items (id INTEGER, object STRING);
 INSERT INTO items VALUES ('101', JSON_OBJECT(1, 55, 2, 7.8, 3, null));
 INSERT INTO items VALUES ('102', JSON_OBJECT(4, 'blue', 5, 'green'));
 INSERT INTO items VALUES ('103', JSON_OBJECT('North', 'Up', 'South', 'Down'));
 INSERT INTO items VALUES ('104', JSON_OBJECT(1, '{"number":1234}' FORMAT JSON));

 SELECT * FROM items;
  ID             OBJECT
  --- -----------------------------
  101 {"1":55,"2":7.8,"3":null}
  102 {"4":"blue","5":"green"}
  103 {"North":"Up","South":"Down"}
  104 {"1":{"number":1234}}

Example 8

JSON_EXISTS

 CREATE TABLE employees (id INTEGER, profile STRING);
 INSERT INTO employees VALUES
 (101,'{"name":"Alice","age":34,"Department":"HR","Location":"Helsinki","Training":{"First Aid":true}}'),
 (102,'{"name":"Bob","age":30,"Department":"HR","Location":"Brussels","Training":{}}'),
 (103,'{"name":"Charlie","age":42,"Department":"Finance","Location":"Lisbon","Training":{"First Aid":true}}'),
 (104,'{"name":"Diana","Department":"Engineering","Location":"Madrid","Training":{"First Aid":false}}'),
 (105,'{"name":"Evan","age":27,"Department":"Engineering","Location":"Paris","Training":{}}'),
 (106,'{"name":"Fiona","age":29,"Department":"Engineering","Location":"Paris","Training":{"First Aid":false}}');

 SELECT id FROM employees where json_exists(profile, '$.age');
  ID
  ---
  101
  102
  103
  105
  106

 SELECT id FROM employees WHERE JSON_EXISTS(profile, 'strict $.age' UNKNOWN ON ERROR) is NULL;
  ID
  ---
  104

 SELECT id FROM employees WHERE JSON_EXISTS(profile, 'strict $.*."First Aid"' FALSE ON ERROR);
  ID
  ---
  101
  103
  104
  106

Example 9

JSON_VALUE

SELECT JSON_VALUE(profile, '$.age' RETURNING INTEGER) AS Age FROM employees;
  AGE
 ------
     34
     30
     42
 <null>
     27
     29

 SELECT JSON_VALUE(profile, 'lax $.Training."First Aid"' RETURNING BOOLEAN DEFAULT 'false' ON EMPTY) AS Complete FROM employees;
 COMPLETE
 ---------
   TRUE
   FALSE
   TRUE
   FALSE
   FALSE
   FALSE

SELECT JSON_VALUE(profile, 'strict $.Training."First Aid"' RETURNING BOOLEAN DEFAULT 'false' ON EMPTY) AS Complete FROM employees;
 COMPLETE
 ---------
  TRUE
  <null>
  TRUE
  FALSE
  <null>
  FALSE

Example 10

JSON_QUERY

SELECT JSON_QUERY(profile, '$.Training') FROM employees;
    [JSON_QUERY]
 -------------------
 {"First Aid":true}
 {}
 {"First Aid":true}
 {"First Aid":false}
 {}
 {"First Aid":false}

SELECT JSON_QUERY(profile, '$.name' WITH WRAPPER) FROM employees;
 [JSON_QUERY]
 -------------
  ["Alice"]
  ["Bob"]
  ["Charlie"]
  ["Diana"]
  ["Evan"]
  ["Fiona"]