查询和分析JSON日志的常见问题

本文中介绍的各个案例是基于如下JSON格式的订单处理系统日志。

如何选择索引类型？

日志服务索引分为全文索引和字段索引，您可以参考如下说明，选择索引类型。更多信息，请参见创建索引。

• 如果您需要查询日志中的所有字段，建议创建全文索引；如果您明确仅查询部分字段，可针对目标字段建立字段索引，减少索引费用。

• 如果对字段有SQL分析需求，则必须对目标字段建立索引，并开启统计功能。

在索引配置中，如何选择字段的数据类型？

当您设置JSON字段的数据类型时，可参考如下思路。

• 如果字段值不是标准JSON格式，可能只是包含了JSON格式的内容，则设置为text类型；如果字段值是标准JSON格式，则设置为JSON类型。

  说明

  针对非完全合法的JSON日志，日志服务支持解析合法部分。

• 将某个字段设置为JSON类型后，如果对JSON对象中的某个叶子节点有进一步的分析需求，可以为叶子节点建立索引，这样可以加快叶子节点的查询和分析速度，但同时也会产生额外的索引费用。

• 日志服务支持JSON对象中的叶子节点建立索引，但不支持包含叶子节点的子节点建立索引。

• 日志服务不支持值为JSON数组的字段建立索引，也不支持为JSON数组中的字段建立索引。

例如基于本文的日志样例，您可以创建如下索引。

• request字段

  • request字段为JSON格式，设置为JSON类型，并开启统计功能。

  • request.clientIp字段需要经常分析，建议单独建立索引，设置为text类型，并开启统计功能。

  • request.http.path字段很少需要分析，可不用单独建立索引。在需要分析时，直接通过JSON函数进行解析。

  • request.param字段为包含叶子节点的子节点，不支持建立索引。

  • request.param.userId字段需要经常分析，建议单独建立索引，设置为text类型，并开启统计功能。

  • request.param.orders字段值为JSON数组，不支持建立索引。

• response字段

  response字段不一定是JSON格式，因此设置为text类型，并开启统计功能。

创建索引后，新采集的日志将显示为如下格式。

如何设置别名？

JSON叶子节点的路径较长，您可以为其设置别名。更多信息，请参见列的别名。

例如统计186499用户的客户端IP地址，可执行如下语句。

*
and request.param.userId: 186499 |
SELECT
  distinct("request.clientIp")

查询和分析结果如下所示。

首先，在查询和分析JSON日志时，如果数据量很大或结构复杂但相对固定，并且您对查询分析性能有要求时，建议对JSON叶子节点建立字段索引，然后再进行查询分析。如果数据量比较小，出于成本考虑，您可以不对JSON叶子节点建立字段索引，而是使用JSON函数进行查询和分析。使用JSON函数，可以灵活地对JSON日志进行动态处理和分析。另外，针对一些特殊情况，只能使用JSON函数进行查询与分析。

• 字段值不一定是JSON格式或者需要先进行一些预处理。

  例如response字段，只有在请求失败时是JSON格式，并且包含errcode字段。那么您要分析errcode的分布情况，则需先使用查询语句过滤出请求失败的日志，然后在分析语句中使用JSON函数动态提取errcode字段值。

  *  not response :SUCCESS |
  SELECT
    json_extract_scalar(response, '$.errcode') AS errcode

  查询和分析结果如下所示。

• 不支持建立索引的JSON节点，只能使用JSON函数实时分析。例如request.param字段和request.param.orders字段。

json_extract函数和json_extract_scalar函数都是用于从JSON对象或JSON数组中提取内容，用法类似，主要区别如下：

• json_extract函数的返回值是JSON类型，json_extract_scalar函数的返回值是varchar类型。

  说明

  此类型是指SQL语法中的数据类型，例如varchar、bigint、boolean、JSON、array、date等，与日志服务索引中的数据类型不同。您可以通过typeof函数查看SQL分析对象的数据类型。更多信息，请参见typeof函数。

• json_extract函数可以解析JSON对象中任意一块子结构，json_extract_scalar函数只解析标量类型（字符串、布尔值或者整型值）的叶子节点，返回对应的字符串。

例如提取request字段中的clientIp字段，两个函数都支持。

• 使用json_extract函数进行提取。

  * |
  SELECT
    json_extract(request, '$.clientIp')

  查询和分析结果如下所示。

  

• 使用json_extract_scalar函数进行提取。

  * | SELECT json_extract_scalar(request, '$.clientIp')

  查询和分析结果如下所示。

在上述基础上，如果要提取clientIp字段值中的第一部分，您需要先使用json_extract_scalar函数提取clientIp的值，然后使用split_part函数提取IP地址中的第一个数字。此处不支持使用json_extract函数，因为split_part函数的入参需为varchar类型。

* |
SELECT
  split_part(
    json_extract_scalar(request, '$.clientIp'),
    '.',
    1
  ) AS segment

查询和分析结果如下所示。

一般情况下，如果您需要从JSON对象中提取字段进行分析，直接使用json_extract_scalar函数即可。因为json_extract_scalar函数的返回值为varchar类型，便于与其他函数结合使用。但是当您需要对JSON结构本身进行分析时，需要使用json_extract函数。例如您要统计一次请求中的订单数，即统计request.param.orders字段中JSON数组的长度，可使用如下查询分析语句。

* |
SELECT
  json_array_length((json_extract(request, '$.param.orders')))

查询和分析结果如下所示。

如果JSON对象的字段中存在特殊字符（例如http.path字段、http path字段、http-path字段等），则需要使用中括号[]代替半角句号（.），然后使用双引号（""）包裹字段名，例如* |SELECT json_extract_scalar(request, '$["http.path"]')。

提取JSON数组中的某个元素时，可以用中括号[]。在中括号中，通过数字来表示下标，下标从0开始。例如：

• 查看用户第一个订单的金额。

  * |
  SELECT
    json_extract_scalar(request, '$.param.orders[0].payment')

  查询和分析结果如下所示。

  

• 查看用户第一个订单中购买的第二件商品。

  * |
  SELECT
    json_extract_scalar(request, '$.param.orders[0].commodity[1]')

  查询和分析结果如下所示。

  

示例1

示例2