MaxCompute:GET_JSON_OBJECT

Exemplo 1: Obter valores de chaves específicas em dados JSON

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

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

Exemplo 2: Um json_path inválido retorna NULL.

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

Exemplo 1: Extrair informações do objeto JSON src_json.json

-- 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;

Exemplo 2: Extrair informações de dados de array JSON

-- 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]');

Exemplo 3: Extrair informações de dados JSON com ponto (.) na chave

-- 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;

Exemplo 4: Usar [''] para chaves que contêm ponto (.)

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

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

Exemplo 5: Entrada JSON vazia ou inválida

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

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

Exemplo 6: Strings JSON com escape

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');

Exemplo 7: Suporte a emojis

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

Observação: o DataWorks não suporta a inserção direta de caracteres emoji. Utilize uma ferramenta como o Data Integration para gravar as strings codificadas correspondentes aos caracteres emoji no MaxCompute. Em seguida, use a função GET_JSON_OBJECT para processá-las.