查詢和分析JSON日誌的常見問題有哪些 - Simple Log Service

本文介紹查詢和分析JSON日誌的常見問題。

日誌範例

本文中介紹的各個案例是基於如下JSON格式的訂單處理系統日誌。 JSON日誌範例

request欄位為訂單請求資訊，JSON格式。一個請求中包含一個使用者的多個訂單，訂單中包含購買的商品和支付總價。
response欄位為訂單處理結果。
- 請求成功時，response欄位值為SUCCESS。
- 請求失敗時，response欄位值為JSON格式，包含errcode和msg資訊。

您可以通過Logtail將該日誌採集到Log Service中，進行查詢與分析。具體操作，請參見使用JSON模式採集日誌。

如何設定索引？

索引是一種儲存結構，用於對日誌中的一列或多列進行排序。您只有設定索引後，才能進行查詢和分析操作。在為JSON日誌設定索引時，可能涉及如下方面的問題。

如何選擇索引類型？

Log Service索引分為全文索引和欄位索引，您可以參考如下說明，選擇索引類型。更多資訊，請參見建立索引。

如果您需要查詢日誌中的所有欄位，建議建立全文索引；如果您明確僅查詢部分欄位，可針對目標欄位建立欄位索引，減少索引費用。
如果對欄位有SQL分析需求，則必須對目標欄位建立索引，並開啟統計功能。

說明

如果您同時配置了全文索引和欄位索引，則配置了欄位索引的欄位，以欄位索引的配置為準。

例如您要統計分析request欄位和response欄位，則需要建立這兩個欄位的欄位索引，並開啟統計功能。

在索引配置中，如何選擇欄位的資料類型？

在設定索引時，欄位的資料類型分為text、long、double和JSON。更多資訊，請參見資料類型。

當您設定JSON欄位的資料類型時，可參考如下思路。

如果欄位值不是標準JSON格式，可能只是包含了JSON格式的內容，則設定為text類型；如果欄位值是標準JSON格式，則設定為JSON類型。
說明
針對非完全合法的JSON日誌，Log Service支援解析合法部分。
將某個欄位設定為JSON類型後，如果對JSON對象中的某個葉子節點有進一步的分析需求，可以為葉子節點建立索引，這樣可以加快葉子節點的查詢和分析速度，但同時也會產生額外的索引費用。
Log Service支援JSON對象中的葉子節點建立索引，但不支援包含葉子節點的子節點建立索引。
Log Service不支援值為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日誌

建立索引後，新採集的日誌將顯示為如下格式。 JSON日誌

如何設定別名？

JSON葉子節點的路徑較長，您可以為其設定別名。更多資訊，請參見列的別名。 JSON日誌

說明

在設定索引時，不同欄位的欄位名或別名不能重複。
對於JSON類型的欄位，JSON葉子節點的名稱是按照全路徑進行重名判定的。例如為response欄位設定別名為clientIp，系統不會判定該別名與request.clientIp欄位名重複。

如何查詢和分析有索引的JSON欄位？

查詢和分析語句格式為查詢語句|分析語句。在分析語句中，您必須使用雙引號（""）包裹欄位名稱，使用單引號（''）包裹字串。您還需為目標欄位加上所有的父路徑，格式為Key1.Key2.Key3。例如request.clientIp、request.param.userId。更多資訊，請參見查詢和分析JSON日誌。

例如統計186499使用者的用戶端IP地址，可執行如下語句。

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

查詢和分析結果如下所示。 JSON日誌

何時使用JSON函數？

首先，在查詢和分析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_extract函數和json_extract_scalar函數都是用於從JSON對象或JSON數組中提取內容，用法類似，主要區別如下：

json_extract函數的傳回值是JSON類型，json_extract_scalar函數的傳回值是varchar類型。
說明
此類型是指SQL文法中的資料類型，例如varchar、bigint、boolean、JSON、array、date等，與Log Service索引中的資料類型不同。您可以通過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對象中提取欄位進行分析，直接使用json_extract_scalar函數即可。因為json_extract_scalar函數的傳回值為varchar類型，便於與其他函數結合使用。但是當您需要對JSON結構本身進行分析時，需要使用json_extract函數。例如您要統計一次請求中的訂單數，即統計request.param.orders欄位中JSON數組的長度，可使用如下查詢分析語句。

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

查詢和分析結果如下所示。 JSON日誌

重要

json_extract_scalar函數的傳回值是varchar類型。例如您上述樣本中的數值2，其資料類型也是varchar類型，如果您要對該值進行求和等計算，需要先使用cast函數，將其轉換為bigint類型。更多資訊，請參見類型轉換函式。

如何設定json_path？

使用json_extract等函數從JSON日誌中提取欄位時，您需指定json_path，用於標明需要提取JSON對象中的哪一部分。json_path格式為$.a.b，貨幣符號（$）代表當前JSON對象的根節點，然後通過半形句號（.）引用到待提取的節點。

如果JSON對象的欄位中存在特殊字元（例如http.path欄位、http path欄位、http-path欄位等），則需要使用中括弧[]代替半形句號（.），然後使用雙引號（""）包裹欄位名，例如* |SELECT json_extract_scalar(request, '$["http.path"]')。

說明

如果是通過SDK進行查詢和分析，則需要對雙引號（""）進行轉義，例如* | 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]')
```
查詢和分析結果如下所示。

如何分析JSON數組？

當日誌中有JSON數組時，您可以結合cast函數和UNNEST子句，展開JSON數組，再進行彙總統計。

樣本1

例如您要統計所有請求成功的訂單的金額，可參見如下思路。

使用查詢語句過濾出請求成功的日誌，然後在分析語句中使用json_extract函數提取出orders欄位的值。
```
*
and response: SUCCESS |
SELECT
  json_extract(request, '$.param.orders')
```
查詢和分析結果如下所示。

將上述提取的JSON數組（JSON類型）轉換為array類型。

*
and response: SUCCESS |
SELECT
  cast(
    json_extract(request, '$.param.orders') AS array(json)
  )

查詢和分析結果如下所示。

JSON日誌

使用UNNEST子句將數組展開。

*
and response: SUCCESS |
SELECT
  orderinfo
FROM  log,
  unnest(
    cast(
      json_extract(request, '$.param.orders') AS array(json)
    )
  ) AS t(orderinfo)

查詢和分析結果如下所示。

JSON日誌

使用json_extract_scalar提取payment欄位值，再使用cast函數將其轉換為bigint類型，最後進行求和計算。

*
and response: SUCCESS |
SELECT
  sum(
    cast(
      json_extract_scalar(orderinfo, '$.payment') AS bigint
    )
  )
FROM  log,
  unnest(
    cast(
      json_extract(request, '$.param.orders') AS array(json)
    )
  ) AS t(orderinfo)

查詢和分析結果如下所示。

JSON日誌

樣本2

統計所有成功的請求中，每一種商品被購買的數量。您可以先提取order欄位，將其轉換為array(json)類型，再使用UNNEST語句將其展開，展開結果中的每一行代表一個訂單。然後使用json_extract函數提取commodity欄位，將其轉換為array(json)類型，再使用UNNEST語句將其展開，展開結果中的每一行代表一個商品。最後再進行分組求和。具體思路請參見樣本1。

*
and response: SUCCESS |
SELECT
  item,
  count(1) AS cnt
FROM  (
    SELECT
      orderinfo
    FROM      log,
      unnest(
        cast(
          json_extract(request, '$.param.orders') AS array(json)
        )
      ) AS t(orderinfo)
  ),
  unnest(
    cast(
      json_extract(orderinfo, '$.commodity') AS array(json)
    )
  ) AS t(item)
GROUP  BY
  item
ORDER BY
  cnt DESC

查詢和分析結果如下所示。

JSON日誌