JSON Functions¶
JSON Format¶
JSON is a language-independent data format that represents data as
human-readable text. A JSON text can represent a number, a boolean, a
string, an array, an object, or a null. A JSON text representing a string
must escape all characters and enclose the string in double quotes, e.g.,
"123\n"
, whereas a JSON text representing a number does not need to,
e.g., 123
. A JSON text representing an array must enclose the array
elements in square brackets, e.g., [1,2,3]
. More detailed grammar can
be found in this JSON introduction.
JSON Functions¶
- get_json_object(jsonString, path) varchar ¶
Returns a json object, represented by VARCHAR, from
jsonString
by searchingpath
. Validpath
should start with ‘$’ and then contain “[index]”, “[‘field’]” or “.field” to define a JSON path. Here are some examples: “$.a” “$.a.b”, “$[0][‘a’].b”. ReturnsjsonString
ifpath
is “$”. Returns NULL ifjsonString
orpath
is malformed. Returns NULL ifpath
does not exist.SELECT get_json_object('{"a":"b"}', '$.a'); -- 'b' SELECT get_json_object('{"a":{"b":"c"}}', '$.a'); -- '{"b":"c"}' SELECT get_json_object('{"a":3}', '$.b'); -- NULL (unexisting field) SELECT get_json_object('{"a"-3}'', '$.a'); -- NULL (malformed JSON string) SELECT get_json_object('{"a":3}'', '.a'); -- NULL (malformed JSON path)
- json_object_keys(jsonString)¶
Returns all the keys of the outermost JSON object as an array if a valid JSON object is given. If it is any other valid JSON string, an invalid JSON string or an empty string, the function returns null.
SELECT json_object_keys('{}'); -- [] SELECT json_object_keys('{"name": "Alice", "age": 5, "id": "001"}'); -- ['name', 'age', 'id'] SELECT json_object_keys(''); -- NULL SELECT json_object_keys(1); -- NULL SELECT json_object_keys('"hello"'); -- NULL