MaxCompute:GET_JSON_OBJECT

Example 1: Get values for specific keys from JSON data

-- Returns 1.
SELECT GET_JSON_OBJECT(JSON '{"a":1, "b":2}', '$.a');

-- Returns NULL.
SELECT GET_JSON_OBJECT(JSON '{"a":1, "b":2}', '$.c');

Example 2: An invalid json_path returns NULL.

-- Returns NULL.
SELECT GET_JSON_OBJECT(JSON '{"a":1, "b":2}', '$invalid_json_path');

Example 1: Extract information from thesrc_json.json JSON object

-- Prepare the test data.
CREATE TABLE IF NOT EXISTS src_json (
    json STRING
);

INSERT OVERWRITE TABLE src_json
VALUES
('{"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":"amy"}');

-- Extract the information of the owner field. The return value is amy.
SELECT GET_JSON_OBJECT(src_json.json, '$.owner') FROM src_json;

-- Optional. Output by preserving the original string.
SET odps.sql.udf.getjsonobj.new=true;
-- Extract the information of the first array in the store.fruit field. The return value is {"weight":8,"type":"apple"}.
SELECT GET_JSON_OBJECT(src_json.json, '$.store.fruit[0]') FROM src_json;

-- Extract the information of a non-existent field. The return value is NULL.
SELECT GET_JSON_OBJECT(src_json.json, '$.non_exist_key') FROM src_json;

Example 2: Extract information from JSON array data

-- Returns 2222.
SELECT GET_JSON_OBJECT('{"array":[["aaaa",1111],["bbbb",2222],["cccc",3333]]}','$.array[1][1]');

-- Output by preserving the original string.
SET odps.sql.udf.getjsonobj.new=true;
-- Returns ["h0","h1","h2"].
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]');

-- Output by escaping JSON reserved characters.
SET odps.sql.udf.getjsonobj.new=false;
-- Returns ["h0","h1","h2"].
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]');

-- Returns h1.
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[1]');

Example 3: Extract information from JSON data with a period (.) in the key

-- Prepare the test data.
CREATE TABLE json_test (id STRING, json STRING);

-- Insert data where the key contains a period (.).
INSERT INTO TABLE json_test (id, json) VALUES 
("1", 
  "{
    \"China.beijing\":
      {\"school\":
        {\"id\":0,\"book\":
          [{\"title\": \"A\",\"price\": 8.95},
           {\"title\": \"B\",\"price\": 10.2}]
        }
      }
  }"
);

-- Insert data where the key does not contain a period (.).
INSERT INTO TABLE json_test (id, json) VALUES 
("2", 
  "{
    \"China_beijing\":
      {\"school\":
        {\"id\":0,\"book\":
          [{\"title\": \"A\",\"price\": 8.95},
           {\"title\": \"B\",\"price\": 10.2}]
        }
      }
  }"
);

-- Use square brackets [''] to parse data that contains a period (.).
-- This extracts the 'id' value under 'China.beijing'. The return value is 0.
SELECT GET_JSON_OBJECT(json, "$['China.beijing'].school['id']") FROM json_test WHERE id =1;

-- For data without special characters, both '.' and [''] are valid and equivalent.
-- This extracts the 'id' value under 'China_beijing'. The return value is 0.
SELECT GET_JSON_OBJECT(json, "$['China_beijing'].school['id']") FROM json_test WHERE id =2;
SELECT GET_JSON_OBJECT(json, "$.China_beijing.school['id']") FROM json_test WHERE id =2;

Example 4: Use [''] for keys that contain a period (.)

SET odps.sql.udf.getjsonobj.new=true;

-- Returns 1.
SELECT GET_JSON_OBJECT('{"a.1":"1","a":"2"}', '$[\'a.1\']');

Example 5: Empty or invalid JSON input

-- Returns NULL.
SELECT GET_JSON_OBJECT('','$.array[1][1]');

-- Returns NULL.
SELECT GET_JSON_OBJECT('"array":["aaaa",1111],"bbbb":["cccc",3333]','$.array[1][1]');

Example 6: Escaped JSON strings

SET odps.sql.udf.getjsonobj.new=true;

--Returns "1".
SELECT GET_JSON_OBJECT('{"a":"\\"1\\"","b":"2"}', '$.a'); 

--Returns '1'.
SELECT GET_JSON_OBJECT('{"a":"\'1\'","b":"2"}', '$.a');

Example 7: Emoji support

-- Returns the emoji symbol.
SELECT GET_JSON_OBJECT('{"a":"<Emoji symbol>"}', '$.a');

Note: DataWorks does not support entering emoji characters directly. You can use a tool, such as Data Integration, to write the encoded strings that correspond to emoji characters to MaxCompute. Then, you can use the GET_JSON_OBJECT function to process them.