Trino & Presto函数进阶之JSON

qianmoq
4,795 阅读3分钟

当我们进行SQL查询时，有些时候需要对一些复杂JSON进行解析，当前文章主要用于对JSON函数的使用进阶。

我们使用以下示例用来标记解析的原始数据：

{
    "type": "btn",
    "btnId": "report_publish",
    "btnName": "报表创建",
    "version": 1,
    "operater": "",
    "pageName": "自助查询",
    "tracking_id": "1",
    "request_params": {
        "appKey": "datateam",
        "engine": "OLAP",
        "isEdit": false,
        "endTime": 1672299701120,
        "oprList": [
            {
                "msg": "列名有关联数据集数重复，请加入别名解决",
                "desc": "解析olap接口数据报错",
                "time": 1672299543001,
                "type": "parseOlapQueryResultErr",
                "moreMsgs": [
                    {
                        "msg": "列名有关联数据集数重复，请加入别名解决",
                        "type": "列名重复"
                    }
                ],
                "reportType": "Table"
            },
            {
                "msg": "Code: 179, e.displayText() = DB::Exception: Different expressions with the same alias `有关联数据集数`:SUM(data_j_report_pv) / sum(data_j_pv) AS `有关联数据集数`andSUM(data_j_report_pv) AS `有关联数据集数`: While processing SUM(data_j_report_pv) / sum(data_j_pv) AS `有关联数据集数` (version 20.11.3.3)",
                "code": 10015,
                "desc": "执行olap查询报错",
                "time": 1672299557315,
                "type": "apiRunOlapSqlErr",
                "moreMsgs": {
                    "code": 0,
                    "data": {
                        "sql": "SHOW tables",
                        "data": null,
                        "time": {
                            "end": 1672299557376,
                            "start": 1672299557295,
                            "elapsed": 81
                        },
                        "proxy": "localhost",
                        "fields": null,
                        "remote": "clickhouse",
                        "sqlTime": {
                            "end": 1672299557362,
                            "start": 1672299557336,
                            "elapsed": 26
                        },
                        "proxyTime": {
                            "end": 1672299557376,
                            "start": 1672299557363,
                            "elapsed": 13
                        }
                    },
                    "size": "673B",
                    "message": "Code: 179, e.displayText() = DB::Exception: Different expressions with the same alias",
                    "success": false,
                    "timestamp": 1672299557265
                },
                "reportType": "Table"
            },
            {
                "time": 1672299576168,
                "type": "data",
                "dataLen": 1,
                "duration": 338,
                "reportType": "Table",
                "apiDuration": 320
            },
            {
                "time": 1672299677236,
                "type": "data",
                "dataLen": 1,
                "duration": 311,
                "reportType": "Table",
                "apiDuration": 287
            }
        ]
    }
}

解析某key的值

比如需要解析json中key = type 的数据，可以使用以下SQL进行解析操作

select json_extract(json, '$.request_params')

结果解析出来将是一个json路径结果，它包含了左右 " 双引号

"btn"

如果我们想要去除 " 双引号

一般的情况下都使用强制类型转换，强制将数据转换为字符串

select cast(json_extract(json, '$.request_params') as varchar)

这种操作是不建议的，因为这样会给引擎带来不必要的资源以及性能的消耗。正确的方式应该是

select json_extract_scalar(json, '$.request_params')

json_extract_scalar 函数会将解析出来的结果转换成字符串

需要注意的是该函数不能适用于解析object，如果我们使用它解析 request_params 字段时，它将返回 null 结果

解析嵌套json某key的值

在示例中 request_params 节点下拥有一个 appKey 的值，如果需要解析它的话，通常是嵌套多次 json_extract 来解析

select json_extract_scalar(json_extract(json, '$.request_params'), '$.appKey')

这种方式是可以进行数据的解析，但是引擎会将其优化并转换为json路径方式进行解析，转换后方式为

select json_extract_scalar(json, '$.request_params.appKey')

虽然说引擎会帮我们进行优化转换，但是为了减少引擎的校验已经优化过程，建议使用该方式进行解析

根据索引解析JSON数组

在示例中包含了一个json数组，比如想获取 request_params --> oprList --> 第一个元素 中 msg 的结果信息，可以使用以下函数进行获取

select
  json_array_get(
    json_array(
        json,
        '$.request_params.oprList'
    ), 
  0)

json_array 函数将解析出来的数据转换为json数据，它也可以使用 cast(xx AS ARRAY<JSON>) 方式来表示

json_array_get 函数通过指定一个索引值，根据该索引值来获取数组中的数据结果，索引是从 0 开始

解析JSON数组某key的值

在示例中想获取 request_params --> oprList 中所有 msg 的结果信息，可以使用以下函数进行获取

select
  TRANSFORM(
    CAST(
      json_extract(json, '$.request_params.oprList') AS ARRAY<JSON>
    ),
    x -> json_extract(x, '$.msg')
  )

这里使用到的 TRANSFORM 可以通过它运行一些 lambda 表达式，进行一些数据的特殊操作，比如行转列，数组抽取等。

TRANSFORM 函数需要两个值。第一个是一个 ARRAY 或者 FUNCTION 类型的数据，也可以通过它实现一些自定义函数，支持部分函数下推操作。 第二个值为自定义的一些函数操作，在该示例中使用了循环解析key为 msg 的json解析函数。

该函数返回一个结果为数组类型的数据。

它也可以使用另一种方式来优化

select 
  json_query(
    json,
    'lax $.request_params.oprList[*]?(@.msg != null)'
    WITH ARRAY WRAPPER
    EMPTY ARRAY ON EMPTY
  )

json_query 函数目前支持两种方式：

  • lax 松散模式
  • strict 严格模式

第一个参数为需要解析的JSON数据

第二个参数格式为 <模式> <JSON路径(可以是多路径)>?<过滤条件>, 其中过滤条件支持部分函数下推

WITH ARRAY WRAPPER
EMPTY ARRAY ON EMPTY

对解析出的结果配置。

建议多使用 try() 函数用于处理一些错误信息

try( json_extract(json, '$.request_params.oprList') )

通过该函数可以避免一些异常现象。

avatar
文章
阅读
粉丝