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.
![](https://support.huaweicloud.com/intl/en-us/sqlreference-dli/public_sys-resources/note_3.0-en-us.png)
- 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:
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 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:
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.
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"}', '$');
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot