get_json_object

Syntax

get_json_object(string <json>, string <path>)

Parameters

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"}', '$');