使用GET_JSON_OBJECT按json_path提取JSON字符串-云原生大数据计算服务 MaxCompute-阿里云

GET_JSON_OBJECT函数用于根据指定的JSON路径json_path，从JSON字符串或JSON类型数据中提取字符串。

命令格式

STRING GET_JSON_OBJECT(JSON|STRING <json>, STRING <json_path>)

-- 典型示例，返回Alice。
SELECT GET_JSON_OBJECT(JSON '{"name": "Alice", "age": 30}', '$.name');

注意事项

GET_JSON_OBJECT不支持JSON PATH的正则语法。
新JSON数据类型的JSON PATH与原有规范不同，可能存在兼容性问题。
当查询语句存在多个GET_JSON_OBJECT函数且处理的都是同一个JSON数据时，函数会多次对同一个JSON字符串做重复的解析遍历，反复调用可能影响性能并产生费用。可以通过结合UDTF转换JSON格式日志数据，避免多次调用函数，详情请参见利用MaxCompute内建函数及UDTF转换JSON格式日志数据。

参数说明

json：必填。待处理的JSON数据。支持JSON或STRING两种输入类型：
- JSON类型：JSON数据类型。格式为{"Key":"Value", "Key":"Value",...}，例如JSON '{"name": "Alice", "age": 30}'。
- STRING类型：当输入为STRING类型时，满足格式要求如下：
  - STRING格式为{Key:Value, Key:Value,...}，例如'{"name": "Alice", "age": 30}'。
  - 英文双引号（"），需要两个反斜杠（\\）转义。
  - 英文单引号（'），需要一个反斜杠（\）转义。
json_path：必填。需要提取的数据的JSON路径表达式，以$开头，STRING类型，例如$.aliyun.test[0].demo。不同字符的含义如下：
- $：表示根节点。
- .或['']：表示子节点，用于解析JSON对象，例如$.store.book。当JSON的Key本身包含.时，可以用['']来替代。
  用['']取数，仅在设置Flag的语句SET odps.sql.udf.getjsonobj.new=true;时支持。
- []：[number]表示数组下标，从0开始。
- *：Wildcard for []，返回整个数组。*不支持转义。

返回值说明

返回STRING类型，即按照指定路径提取出来的数据。返回规则如下：

当json合法且json_path存在时，则返回对应字符串。
当json为空或为非法格式时，返回NULL。
当json_path存在[*]时，返回非ARRAY格式。若希望强制返回统一的ARRAY格式，需设置SET odps.sql.force.getjsonobj.array.format=true;。
当json_path非法时，返回NULL。

返回行为说明：

支持通过设置Project/Session级别FlagSET odps.sql.udf.getjsonobj.new=true/false;控制函数的返回方式。

不同的Flag设置对应函数的两种返回行为如下:

重要

推荐使用SET odps.sql.udf.getjsonobj.new=true;配置，函数返回行为更标准，处理数据更方便，性能更好。如果MaxCompute项目有使用JSON保留字符转义行为的存量作业，建议保留原有行为方式，避免因未验证而直接使用该行为产生错误或正确性问题。

参数设置	`SET odps.sql.udf.getjsonobj.new=true;`	`SET odps.sql.udf.getjsonobj.new=false;`
函数返回行为	采用保留原始字符串的方式输出。	采用JSON保留字符转义的方式输出。
函数返回行为	返回值仍是一个JSON字符串，可以继续解析，不需要额外使用`REPLACE`或`REGEXP_REPLACE`等函数替换反斜线。	换行符（\n）、引号（"）等JSON保留字符通过字符串`'\n'`、`'\"'`显示。
相同Key解析	一个JSON对象中可以出现相同的Key，并成功解析。 `-- 返回1。 SELECT GET_JSON_OBJECT('{"a":"1","a":"2"}', '$.a');`	一个JSON对象中不可以出现相同的Key，可能导致无法解析。 `-- 返回NULL。 SELECT GET_JSON_OBJECT('{"a":"1","a":"2"}', '$.a');`
输出结果排序方式	输出结果按照JSON字符串的原始排序方式输出。 `-- 返回{"b":"1","a":"2"}。 SELECT GET_JSON_OBJECT('{"b":{"b":"1","a":"2"},"a":"2"}', '$.b');`	输出结果按照字典排序方式输出。 `-- 返回{"a":"2","b":"1"}。 SELECT GET_JSON_OBJECT('{"b":{"b":"1","a":"2"},"a":"2"}', '$.b');`

当开启Hive兼容模式SET odps.sql.hive.compatible=true;时，GET_JSON_OBJECT函数的返回行为强制保留原始字符串。
2021年1月21日及之后创建的MaxCompute项目GET_JSON_OBJECT函数的返回行为默认为保留原始字符串。
2021年1月21日之前创建的MaxCompute项目GET_JSON_OBJECT函数的返回行为默认为JSON保留字符转义。
可以通过以下示例判断MaxCompute项目中GET_JSON_OBJECT函数采用了哪种行为，执行命令如下：
```
SELECT GET_JSON_OBJECT('{"a":"[\\"1\\"]"}', '$.a');
--JSON保留字符转义的行为返回：
[\"1\"]

--保留原始字符串的行为返回：
["1"]
```
可以通过提交工单联系产品技术支持团队，将项目中的GET_JSON_OBJECT函数返回行为切换为保留原始字符串，避免在Session级别频繁设置属性。

使用示例

入参为JSON类型

示例1：从JSON类型数据中，分别获取指定 key值为a、key值为c时，对应value值。

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

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

示例2：当json_path非法时，返回NULL。

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

入参为STRING类型

示例1：提取JSON对象`src_json.json`中的信息

-- 准备测试数据。
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"}');

-- 提取owner字段信息，返回amy。
SELECT GET_JSON_OBJECT(src_json.json, '$.owner') FROM src_json;

-- 可选，采用保留原始字符串的方式输出。
SET odps.sql.udf.getjsonobj.new=true;
-- 提取store.fruit字段第一个数组信息，返回{"weight":8,"type":"apple"}。
SELECT GET_JSON_OBJECT(src_json.json, '$.store.fruit[0]') FROM src_json;

-- 提取不存在的字段信息，返回NULL。
SELECT GET_JSON_OBJECT(src_json.json, '$.non_exist_key') FROM src_json;

示例2：提取数组型JSON数据信息

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

-- 采用保留原始字符串的方式输出。
SET odps.sql.udf.getjsonobj.new=true;
-- 返回["h0","h1","h2"]。
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]');

-- 采用JSON保留字符转义的方式输出。
SET odps.sql.udf.getjsonobj.new=false;
-- 返回["h0","h1","h2"]。
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]');

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

示例3：提取带有`.`的JSON数据中的信息

-- 准备测试数据。
CREATE TABLE json_test (id STRING, json STRING);

-- 插入Key带有.的数据。
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}]
        }
      }
  }"
);

-- 插入Key不带有.的数据。
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}]
        }
      }
  }"
);

-- 使用方括号['']解析包含'.'的数据。
-- 此处提取 'China.beijing' 下的 'id' 值，返回0。
SELECT GET_JSON_OBJECT(json, "$['China.beijing'].school['id']") FROM json_test WHERE id =1;

-- 不含特殊字符的数据，使用'.'和['']均有效且等效。
-- 此处提取 'China.beijing' 下的 'id' 值，返回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;

示例4：当JSON的Key本身包含`.`时，可以用`['']`来替代

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

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

示例5：JSON输入为空或非法格式

-- 返回NULL。
SELECT GET_JSON_OBJECT('','$.array[1][1]');

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

示例6：JSON字符串涉及转义

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

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

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

示例7：支持Emoji表情符号对应的编码字符串

-- 返回Emoji符号。
SELECT GET_JSON_OBJECT('{"a":"<Emoji符号>"}', '$.a');

注意：DataWorks 暂不支持输入Emoji表情符号，仅支持通过数据集成等工具直接将Emoji表情符号对应的编码字符串写入MaxCompute，再用GET_JSON_OBJECT函数处理。