get_json_object¶
This function is used to parse the JSON object in a specified JSON path. The function will return NULL if the JSON object is invalid.
Syntax¶
get_json_object(string <json>, string <path>)
Parameters¶
Parameter | Mandatory | Type | Description |
|---|---|---|---|
json | Yes | STRING | Standard JSON object, in the {Key:Value, Key:Value,...} format. |
path | Yes | STRING | Path of the object in JSON format, which starts with $. The meanings of characters are as follows:
|
Return Values¶
The return value is of the STRING type.
Note
If the value of json is empty or in invalid JSON format, NULL is returned.
If the value of json is valid and path is specified, the corresponding string is returned.
Example Code¶
Extracts information from the JSON object src_json.json. An example command is as follows:
jsonString = {"store": {"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "bicycle":{"price":19.95,"color":"red"} }, "email":"amy@only_for_json_udf_test.net", "owner":"Tony" }
Extracts the information of the owner field and returns Tony.
select get_json_object(jsonString, '$.owner');
Extracts the first array information of the store.fruit field and returns {"weight":8,"type":"apple"}.
select get_json_object(jsonString, '$.store.fruit[0]');
Extracts information about a field that does not exist and returns NULL.
select get_json_object(jsonString, '$.non_exist_key');
Extracts information about an array JSON object. An example command is as follows:
The value 22 is returned.
select get_json_object('{"array":[["a",11],["b",22],["c",33]]}','$.array[1][1]');
The value ["h00","h11","h22"] is returned.
select get_json_object('{"a":"b","c":{"d":"e","f":"g","h":["h00","h11","h22"]},"i":"j"}','$.c.h[*]');
The value ["h00","h11","h22"] is returned.
select get_json_object('{"a":"b","c":{"d":"e","f":"g","h":["h00","h11","h22"]},"i":"j"}','$.c.h');
The value h11 is returned.
select get_json_object('{"a":"b","c":{"d":"e","f":"g","h":["h00","h11","h22"]},"i":"j"}','$.c.h[1]');
Extracts information from a JSON object with a period (.). An example command is as follows:
Create a table.
create table json_table (id string, json string);
Insert data into the table. The key contains a period (.).
insert into table json_table (id, json) values ("1", "{\"China.hangzhou\":{\"region\":{\"rid\":6}}}");
Insert data into the table. The key does not contain a period (.).
insert into table json_table (id, json) values ("2", "{\"China_hangzhou\":{\"region\":{\"rid\":7}}}");
Obtain the value of rid. If the key is China.hangzhou, 6 is returned. Only [''] can be used for parsing because a period (.) is included.
select get_json_object(json, "$['China.hangzhou'].region['id']") from json_table where id =1;
Obtain the value of rid. If the key is China_hangzhou, 7 is returned. You can use either of the following methods:
select get_json_object(json, "$['China_hangzhou'].region['id']") from json_table where id =2; select get_json_object(json, "$.China_hangzhou.region['id']") from json_table where id =2;
The json parameter is either empty or has an invalid format. An example command is as follows:
The value NULL is returned.
select get_json_object('','$.array[2]');
The value NULL is returned.
select get_json_object('"array":["a",1],"b":["c",3]','$.array[1][1]');
A JSON string involves escape. An example command is as follows:
The value 3 is returned.
select get_json_object('{"a":"\\"3\\"","b":"6"}', '$.a');
The value 3 is returned.
select get_json_object('{"a":"\'3\'","b":"6"}', '$.a');
A JSON object can contain the same key and can be parsed successfully.
The value 1 is returned.
select get_json_object('{"b":"1","b":"2"}', '$.b');
The result is output in the original sorting mode of the JSON string.
The value {"b":"3","a":"4"} is returned.
select get_json_object('{"b":"3","a":"4"}', '$');