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

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

Search Functions

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

Function Description

JSON_CONTAINS(target, candidate[, path])

Returns 0 or 1 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: INTEGER

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

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

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

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"') = 1;

 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