JSON_ARRAY_CONTAINS
json_array_contains(json,value)
Command description: determines whether
value
exists in the JSON array.Return value type: BOOLEAN
Examples:
select json_array_contains('[1, 2, 3]', 2);
+-----------------------------------------+
| 1 |
JSON_ARRAY_GET
json_array_get(json_array,index)
Command description: returns the element at the specified
index
intojson_array
.index >= 0
: returns the specified elements from the start ofjson_array
.index < 0
: returns the specified elements from the end ofjson_array
.No specified element at index
: returnsNULL
.
Return value type: VARCHAR
Examples:
select json_array_get('["a", "b", "c"]', 1) as result;
+-----------------------------------------------+
| b |
select json_array_get('["a", "b", "c"]', -1) as result;
+-----------------------------------------------+
| c |
JSON_ARRAY_LENGTH
json_array_length(json)
Command description: returns the length of the
json
array.Return value type: BIGINT
Examples:
select json_array_length('[1, 2, 3]')
+-----------------------------------+
| 3 |
JSON _EXTRACT
json_extract(json,json_path)
Command description: returns the
json_path
fromjson
, in$[n]
format.If
n is less than 0
, the system reports an error.If
n
is an invalid parameter whose value is greater than 0, the returned result isNULL
.
Return value type: JSON
Examples:
select json_extract('[10, 20, [30, 40]]', '$[1]');
+------------------------------------------------+
| 20 |
select json_extract('[10, 20, [30, 40]]', '$[0]');
+------------------------------------------------+
| 10 |
JSON_EXTRACT_SCALAR
json_extract_scalar(json,json_path)
Command description: returns the
json_path
fromjson
, in$[n]
format.If
n is less than 0
, the system reports an error.If
n
is an invalid parameter whose value is greater than 0, the returned result isNULL
.
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
json_array_extract(json,jsonPath)
Command description: returns the
json_path
fromjson
.Return value type: VARCHAR (array string)
Examples:
select json_array_extract(arr1, '$.book.id') as a
from (values ('[{"book":{"id":"12"}}, {"book":{"id":"14"}}]')) t(arr1);
Returned result:
["12", "14"]
select json_array_extract_scalar(arr1, '$.book.id') as a
from (values ('[{"book":{"id":"12"}}, {"book":{"id":"14"}}]')) t(arr1);
Returned result:
[12, 14]
JSON_FORMAT
json_format(json)
Command description: returns the JSON text serialized from the input
json
.Return value type: VARCHAR
Examples:
select json_format(JSON '[1, 2, 3]');
+-----------------------------------+
| [1,2,3] |
JSON_PARSE
json_parse(string)
Command description: returns the JSON value deserialized from the input
string
.Return value type: JSON
Examples:
select json_parse('[1, 2, 3]');
+----------------------------+
| [1,2,3] |
select json_parse('"a"');
+-----------------------+
| "a" |
JSON_SIZE
json_size(json,json_path)
Command description: returns the size of
json
.Return value type: BIGINT
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
json_remove(json_string,json_path_string)
json_remove(json_string,array[json_path_string])
Command description: returns data from
json_string
, excludingjson_path_string
.You can use
array[json_path_string]
to specify multiple values ofjson_path_string
at a time.Return value type: VARCHAR (JSON-formatted 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;
Returned result:
{"glossary":{"title":"example glossary"}}
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;
Returned result:
```sql
{“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
```sql
json_reserve(json_string,json_path_string)
json_reserve(json_string,array[json_path_string])
Command description: returns
json_path_string
fromjson_string
.You can use
array[json_path_string]
to specify multiple values ofjson_path_string
.Return value type: VARCHAR (JSON-formatted 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;
Returned result:
{"glossary":{"title":"example glossary"}}
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;
Returned result:
```sql
{“glossary”:{“title”:”example glossary”,”GlossDiv”:{“GlossList”:{“GlossEntry”:{“ID”:”SGML”}},”title”:”S”}}}```