背景

最近在处理的数据好几个字段都是大JSON，结构复杂嵌套多。而业务逻辑需要对JSON中的部分内容进行过滤和抽取。
过去常用的内置函数get_json_object和一些自定义的UDF都不能很好地实现需求。因此探索了一下更好的JSON处理方法。

MaxCompute最新的脚本模式已经支持对java gson的引入，可以通过gson API直接把字符串转换为java类型进行处理，但使用Java代码处理数据字段的代码复杂度更高（包含了打包自己的java bean类，引入自定义的jar包资源，定义自己的java代码），且由于大部分数据工程脚本还是SQL类型的节点，改造代价很大。所以，虽然这样执行JSON字段处理的效率更高，但目前主流的JSON字段处理依然使用了自定义UDF来进行。

常见用来处理JSON的自定义UDF，主要分为两类：

1. 拼接JSON数据使用的
2. 解析、提取JSON数据使用的

本文只对司内常用的JSON解析和拼接的UDF进行对比。司外同学可以通过开发UDF的方式实现类似功能。

JSON的解析

JSON的检索表达式有JsonSQL、JSONPath、jfunk、JMESPath、JSONSelect。当然还有手撸的JSON解析语法。

MaxCompute内置了get_json_object方法，可以实现基于JSONPath的取数逻辑，但：
a> 对JSONPath的语法支持并不完整（不支持?@><等条件过滤，对?=条件过滤只返回第一个有效结果）；

b> 无法返回数组结果（取数组直接返回NULL值）。

因此，对于这样一个JSON（例子1）

{
    "address": {
        "city": "Nara",
        "postalCode": "630-0192",
        "streetAddress": "naist street"
    },
    "age": 26,
    "firstName": "John",
    "lastName": "doe",
    "phoneNumbers": [
        {
            "number": "0123-4567-8888",
            "type": "iPhone",
            "weight": 0.1
        },
        {
            "number": "0123-4567-8910",
            "type": "home",
            "weight": 0.2
        }
    ]
}

想获取phoneNumbers中权重大于0.1的number，就无能为力了。

方法对比

综合来讲MaxCompute上目前只有三种主流的json提取方法：

1. FROM_JSON，转换成STRUCT、ARRAY、MAP等复杂类型后用复杂类型直接操作；
2. get_json_object，快速提取简单JSON的某个字段；
3. 自定义UDF，jmespath_get_object(or json)，通过jmespath语法提取复杂逻辑处理后的结果。

司内数据市场对json检索语句的UDF支持主要包含jsonpath（语法）和jmespath（语法）两类，常见的对比如下：

大表测试：

大表包含一个大json字段，记录数为3484503条。我们分别上述UDF进行单独调用，测试抽取json字段头部的某个字段内容，和尾部的一个数组内的某个字段内容。

使用语法：

头部

尾部

在同样的DAG下，每个SQL语句执行两次（确保预热），提取Summary中UDF执行过程的avg处理条数和avg处理时间：
metrics_output_count: Project1和metrics_inner_time_ms: Project1用于计算处理速度。

最终得到下表：

在UDF选择时需要明确

1. 字符串格式是否标准JSON（双引号格式）？无法保证：使用JMESPATH；
2. 是否需要对取出的结果进行复杂运算？需要：使用FROM_JSON。
3. 是否需要复杂的逻辑查询？当只需要简单数据抽取（只包含字段的直接.查询和数组的[]索引查询）时使用get_json_object；否则使用FROM_JSON或jmespath_get_json都行。
4. 是否保留其他字段返回完整JSON？当 JSON 的 SCHEMA 不确定，且有需要保留冗余字段时，建议使用JMESPATH，通过merge操作处理指定字段；若只需要提取其中特定字段，并进行计算，推荐使用FROM_JSON或get_json_object。
5. 增加一个CASE：当json内的key为非标准命名的时候：使用jmespath或from_json：

对于JMESPATH：

SELECT jmespath_get_json(
  '{">=60":180,"[45,49]":211,"[1,17]":59,"[50,54]":112,"[35,39]":965,"NULL":26395,"[40,44]":418,"[25,29]":1365,"[30,34]":1707,"[18,24]":1184,"[55,59]":62}'
  , '"[45,49]"') AS sco

SELECT jmespath_get_json(

  '{">=60":180,"[45,49]":211,"[1,17]":59,"[50,54]":112,"[35,39]":965,"NULL":26395,"[40,44]":418,"[25,29]":1365,"[30,34]":1707,"[18,24]":1184,"[55,59]":62}'

  , '"[45,49]"') AS score;


对于FROM_JSON：

SELECT FROM_JSON(
  '{">=60":180,"[45,49]":211,"[1,17]":59,"[50,54]":112,"[35,39]":965,"NULL":26395,"[40,44]":418,"[25,29]":1365,"[30,34]":1707,"[18,24]":1184,"[55,59]":62}', 'map<string,bigint>'
)['[45,49]'] AS score;

JSON的写入

JSON的写入在MaxCompute中推荐3种方法：

1. 通过TO_JSON将复杂类型直接转换为JSON字符串；（首推）
2. 通过不同的UDF转换不同的格式，通过merge相关的UDF完成拼接；
3. 通过JMESPATH实现拼接。
4. （这种不推荐了）直接通过字符串拼接，CONCAT('{"a":', 2, '}')；

这三种方法分别有优缺点。

通过TO_JSON转换

TO_JSON的操作需要配合ODPS复杂类型实现

--返回{"a":1,"b":2}。
select to_json(named_struct('a', 1, 'b', 2));
--返回{"time":"26/08/2015"}。
select to_json(named_struct('time', "26/08/2015"));
--返回[{"a":1,"b":2}]。
select to_json(array(named_struct('a', 1, 'b', 2)));
--返回{"a":{"b":1}}。
select to_json(map('a', named_struct('b', 1)));
--返回{"a":1}。
select to_json(map('a', 1));
--返回[{"a":1}]。
select to_json(array((map('a', 1))));

通过不同UDF进行拼接

在司内很多团队都沉淀了丰富的JSON拼接函数。在这里我只列举本人开发过程中使用过的几个直观好用的函数进行对比。

通过上述的几个方法可以实现基本的JSON插入/编辑需求。

通过JMESPATH实现拼接

由于JMESPATH对复杂函数的支持，可以实现对JSON的快速拼接。

比如，对于如下JSON：

[
  {
    "a": 1,
    "b": "b0"
  },
  {
    "a": 1,
    "b": "b1"
  },
  {
    "a": 2,
    "c": "b3"
  }
]

我们需要对数组内的所有对象增加一个字段c，并且赋值为1。

可以调用：

SELECT jmespath_get_json('[{"a":1,"b":"b0"},{"a":1,"b":"b1"},{"a":2,"c":"b3"}]', 'map(&merge(@,{"c":`1`}),@)');

来进行实现。即使用map方法对数组中每个元素进行merge操作，增加一个字段。结果为：

[
    {
        "a": 1,
        "b": "b0",
        "c": 1
    },
    {
        "a": 1,
        "b": "b1",
        "c": 1
    },
    {
        "a": 2,
        "c": 1
    }
]

而使用TO_JSON就需要比较复杂的操作过程了，因为c在第三个元素中存在类型转换的过程，所以复杂类型处理时schema会变化。

但是，使用JMESPATH生成JSON对象时，需要我们用CONCAT之类的函数把字段数据拼接成JMESPATH语法，CONCAT本身会因为NULL值存在失效的问题，所以字段转JMESPATH语法的代价也需要在开发中谨慎使用。

JSON拼接小结

针对上述三种JSON拼接的方法，可以在实际使用中灵活选择，具体可以考虑如下场景：

1. 是否从0开始生成完整的JSON？
   是：TO_JSON即可；
   否：选择其他
2. 组合JSON深度较浅，则使用方法二；
3. 对现有复杂JSON字段进行条件筛选或复杂修改，则使用方法三。

具体的，比如：

需要对TABLE(user_id:BIGINT, group_id:BIGINT)表的数据进行分user_id打包group的时候，可以简单地使用聚合方法（方法一）：

SELECT user_id, TO_JSON(COLLECT_LIST(group_id)) AS group_ids_json FROM a WHERE group_id IS NOT NULL GROUP BY user_id;

需要对user进行性别、年龄、名字等数据打包，且拼入已有的部分json信息时可以使用（方法二）：

SELECT user_id,bi_put_json_values('{"fav":[12,3,4], "hate":[6,9,11]}', 'name',bi_to_json_string_ex(name), 'age', bi_to_json_string_ex(age), 'gender', bi_to_json_string_ex(gender)) AS user_profile_json FROM a;

需要获取例子1中权重最大的phoneNumber，并生成一个firstPhoneNumber字段拼入原始profile信息内的时候（方法三）：

SELECT bjmespath_get_json(profile, 'merge(@,{firstPhoneNumber:max_by(phoneNumbers, &weight)})') AS max_phone_json FROM a;

总结

本文简述了工作中在MaxCompute上对JSON字段的处理UDF，对使用场景进行了对比。必然有大量的UDF没有尝试到。还希望同学们多多提供好用的方法和实践。

不过随着MaxCompute对复杂类型的支持越来越丰富（特别是匿名函数的引入，这个会新篇介绍），FROM_JSON和TO_JSON来处理JSON字段的效率会越来越高（无论是编程效率，还是执行效率），大家都可以尝试优先使用这两个方法来操作。