This topic describes JSON functions.
JSON_ARRAY_CONTAINS
Syntax
json_array_contains(json,value)
Description
Determines whether a value specified by value
is included in a JSON array.
Return value type
BOOLEAN
Example
select json_array_contains('[1, 2, 3]', 2);
+-----------------------------------------+
| 1 |
JSON_ARRAY_GET
Syntax
json_array_get(json_array,index)
Description
Returns a specified element in a JSON array
based on the value of index
.
If the value of
index
is greater than or equal to 0, elements are returned from the beginning of aJSON array
.If the value of
index
is less than 0, elements are returned from the end of aJSON array
.If the element specified by
index
does not exist,NULL
is returned.
Return value type
VARCHAR
Example
select json_array_get('["a", "b", "c"]', 1) as result;
+-----------------------------------------------+
| b |
JSON_ARRAY_LENGTH
Syntax
json_array_length(json)
Description
Returns the length of a JSON
array.
Return value type
BIGINT
Example
select json_array_length('[1, 2, 3]')
+-----------------------------------+
| 3 |
JSON_EXTRACT
Syntax
json_extract(json,json_path)
Description
Returns the value specified by json_path
from a JSON object. The value of $[n]
.
If the value of
n
is less than 0, an error is returned.If the value of
n
is an invalid value that is greater than 0,NULL
is returned.
Return value type
JSON
Example
select json_extract('[10, 20, [30, 40]]', '$[1]');
+------------------------------------------------+
| 20 |
JSON_EXTRACT_SCALAR
Syntax
json_extract_scalar(json,json_path)
Description
Returns the scalar value specified by json_path
from a JSON
object. The value of json_path
is in the format of $[n]
.
If the value of
n
is less than 0, an error is returned.If the value of
n
is an invalid value that is greater than 0,NULL
is returned.
Return value type
VARCHAR
Examples
select json_extract_scalar('[10, 20, [30, 40]]', '$[1]');
+------------------------------------------------------+
| 20 |
select json_extract_scalar('[10, 20, [30, 40]]', '$[0]');
+------------------------------------------------------+
| 10 |
JSON_ARRAY_EXTRACT/JSON_ARRAY_EXTRACT_SCALAR
Syntax
json_array_extract(json,jsonPath)
Description
Returns the value specified by json_path
from a JSON
array.
Return value type
VARCHAR (JSON array of strings)
Examples
select json_array_extract(arr1, '$.book.id') as a
from (values ('[{"book":{"id":"12"}}, {"book":{"id":"14"}}]')) t(arr1);
["12", "14"]
is returned.
select json_array_extract_scalar(arr1, '$.book.id') as a
from (values ('[{"book":{"id":"12"}}, {"book":{"id":"14"}}]')) t(arr1);
[12, 14]
is returned.
JSON_FORMAT
Syntax
json_format(json)
Return value type
VARCHAR
Example
select json_format(JSON '[1, 2, 3]');
+-----------------------------------+
| [1,2,3] |
JSON_PARSE
Syntax
json_parse(string)
Description
Parses a string
as JSON data and returns the data.
Return value type
JSON
Examples
select json_parse('[1, 2, 3]');
+----------------------------+
| [1,2,3] |
select json_parse('"a"');
+-----------------------+
| "a" |
JSON_SIZE
Syntax
json_size(json,json_path)
Description
Returns the size of a JSON
object.
Examples
select json_size('{"x":{"a":1, "b": 2}}', '$.x') as result;
+---------------------------------------------------------+
| 2 |
json_size('{"x": {"a": 1, "b": 2}}', '$.x.a') as result;
+------------------------------------------------------+
| 0 |
JSON_REMOVE
Syntax
json_remove(json_string,json_path_string)
json_remove(json_string,array[json_path_string])
Description
Removes a specified JSONPath expression string
from a JSON string
and returns the remaining data of the JSON string. You can use array[json_path_string]
to specify multiple JSONPath expression strings
at a time.
Return value type
VARCHAR (JSON string)
Examples
select json_remove(
'{
"glossary": {
"title": "example glossary",
"GlossDiv": {
"title": "S",
"GlossList": {
"GlossEntry": {
"ID": "SGML",
"SortAs": "SGML",
"GlossTerm": "Standard Generalized Markup Language",
"Acronym": "SGML",
"Abbrev": "ISO 8879:1986",
"GlossDef": {
"para": "A meta-markup language, used to create markup languages such as DocBook.",
"GlossSeeAlso": ["GML", "XML"]
},
"GlossSee": "markup"
}
}
}
}
}'
, '$.glossary.GlossDiv') a;
{"glossary":{"title":"example glossary"}}
is returned.
select json_remove(
'{
"glossary": {
"title": "example glossary",
"GlossDiv": {
"title": "S",
"GlossList": {
"GlossEntry": {
"ID": "SGML",
"SortAs": "SGML",
"GlossTerm": "Standard Generalized Markup Language",
"Acronym": "SGML",
"Abbrev": "ISO 8879:1986",
"GlossDef": {
"para": "A meta-markup language, used to create markup languages such as DocBook.",
"GlossSeeAlso": ["GML", "XML"]
},
"GlossSee": "markup"
}
}
}
}
}'
, array['$.glossary.title', '$.glossary.GlossDiv.title']) a;
The following result is returned:
{"glossary":{"GlossDiv":{"GlossList":{"GlossEntry":{"GlossTerm":"Standard Generalized Markup Language","GlossSee":"markup","SortAs":"SGML","GlossDef":{"para":"A meta-markup language, used to create markup languages such as DocBook.","GlossSeeAlso":["GML","XML"]},"ID":"SGML","Acronym":"SGML","Abbrev":"ISO 8879:1986"}}}}}
JSON_RESERVE
Syntax
json_reserve(json_string,json_path_string)
json_reserve(json_string,array[json_path_string])
Description
Returns a specified JSONPath expression string
from a JSON string
. You can use array[json_path_string]
to specify multiple JSONPath expression strings
at a time.
Return value type
VARCHAR (JSON string)
Examples
select json_reserve(
'{
"glossary": {
"title": "example glossary",
"GlossDiv": {
"title": "S",
"GlossList": {
"GlossEntry": {
"ID": "SGML",
"SortAs": "SGML",
"GlossTerm": "Standard Generalized Markup Language",
"Acronym": "SGML",
"Abbrev": "ISO 8879:1986",
"GlossDef": {
"para": "A meta-markup language, used to create markup languages such as DocBook.",
"GlossSeeAlso": ["GML", "XML"]
},
"GlossSee": "markup"
}
}
}
}
}'
, array['$.glossary.title']) a;
{"glossary":{"title":"example glossary"}}
is returned.
select json_reserve(
'{
"glossary": {
"title": "example glossary",
"GlossDiv": {
"title": "S",
"GlossList": {
"GlossEntry": {
"ID": "SGML",
"SortAs": "SGML",
"GlossTerm": "Standard Generalized Markup Language",
"Acronym": "SGML",
"Abbrev": "ISO 8879:1986",
"GlossDef": {
"para": "A meta-markup language, used to create markup languages such as DocBook.",
"GlossSeeAlso": ["GML", "XML"]
},
"GlossSee": "markup"
}
}
}
}
}'
, array['$.glossary.title', '$.glossary.GlossDiv.title', '$.glossary.GlossDiv.GlossList.GlossEntry.ID']) a;
The following result is returned:
{"glossary":{"title":"example glossary","GlossDiv":{"GlossList":{"GlossEntry":{"ID":"SGML"}},"title":"S"}}}
JSON_EXTRACT_VALUR_COMPARE
Syntax
json_extract_value_compare(json, json_path, value_to_compare, operator)
Description
Compares the value extracted from a JSON
string with the value specified by value_to_compare
. If data types of the two values are inconsistent, this function automatically converts the value specified by value_to_compare to the data type of the value extracted from the JSON string before the comparison.
json
: theJSON
string from which you want to extract a value.json_path
: theJSONPath
expression that you want to extract.value_to_compare
: the string that you want to compare with the extracted value. Only strings are supported. If the value that is extracted is a numeric value, this function automatically converts the string specified byvalue_to_compare
to a numeric value and then compares the two values.operator: the comparison operator. The following comparison operators are supported: <, >, ≤, ≥, =, !=, <>.
Return value type
BOOLEAN
Examples
select json_extract_value_compare('{"key":223}', '$.key', '9','gt');
+------------------------------------------------------------+
// 223 is a numeric value. You need to convert '9' to 9 and compare 9 with 223. 223 is greater than 9, and 1 is returned.
| 1 |
select json_extract_value_compare('{"key":"223"}', '$.key', '9', '>');
+------------------------------------------------------------+
// "223" is a string. You can directly compare "223" with "9". "223" is greater than "9", and 0 is returned.
| 0 |