☰

从嵌入另一个 JSON 文档的 JSON 文档中提取数据

tag-icon • tag-icon json jq
从嵌入另一个 JSON 文档的 JSON 文档中提取数据

我有一个嵌入另一个 JSON 文档的 JSON 文档。我需要从嵌入文档中提取数据，但我不太熟悉 JSON 或jq.

以下是我的输入（guard.json）：

{
  "_index": "test-2021.06.02",
  "_type": "servrd",
  "_id": "ZWUxMDU5MjItOGY2MC00MGI5LWJhZTEtODRhYWQ1YTZhOGIy",
  "_version": 1,
  "_score": null,
  "_source": {
    "stream": "stdout",
    "time": "2021-10-02T03:13:52.496705721Z",
    "docker": {
      "container_id": "392923402349320329432h3432k4kj32kfks9s9sdfksdfjkdsjfsh3939322342"
    },
    "kubernetes": {
      "container_name": "test",
      "namespace_name": "dev",
      "pod_name": "test-dev-v004-9s885",
      "container_image": "localhost:80/pg/test:1.19.0",
      "container_image_id": "docker-pullable://localhost:80/pg/test@sha256:2sfdsfsfsfsfdsr3wrewrewc235e728ad1b29baf5af3dfad30c7706e5eda38b6109",
      "pod_id": "ssfsfds-dsfdsfs-fs-sfsfs-sfdsfsdfsewrw",
      "host": "test-jup-node008",
      "labels": {
        "app": "test",
        "cluster": "test-dev",
        "load-balancer-test-dev": "true",
        "stack": "dev",
        "app_kubernetes_io/managed-by": "spinnaker",
        "app_kubernetes_io/name": "test",
        "moniker_spinnaker_io/sequence": "4"
      },
      "min_url": "https://100.400.0.22:443/api",
      "namespace_id": "jajdjsdf-dfse-dsf-koksls-sksjf030292",
      "namespace_labels": {
        "name": "dev"
      }
    },
    "elapsedTime": 39013,
    "message": "TransactionLog",
    "membersId": "TEST_0233203203_030202020303",
    "payload": "{\"serviceId\":\"00343\",\"AccessKey\":\"testdfsolpGS\",\"trackID\":\"KOLPSLSLSLL99029283\",\"membersId\":\"TEST_0233203203_030202020303\",\"shopperInfo\":{\"emailAddress\":\"[email protected]\",\"ipAddress\":\"localhost\"},\"parkid\":{\"parkssID\":\"carrier-park\"},\"cartinfo\":{\"checkType\":\"preorder\",\"detailsmetis\":\"card\",\"currency\":\"US\",\"grosscount\":\"10\",\"reedeem\":\".00\",\"Discount\":\".00\",\"tokenvalue\":{\"token\":\"11102020392023920920393993\",\"Bin\":\"00000\",\"digit\":\"0000\",\"expirationDate\":\"202509\",\"price\":\"10\"}},\"cartdetails\":[{\"dones\":[{\"donesnames\":\"test\",\"price\":\"003\",\"quan\":\"1\"}]}]}",
    "requestDate": "2021-10-02T03:13:12.541207804Z",
    "requestId": "12321321wes-sfsfdsf-snnm79887-029299",
    "finalToClient": "{\"finalType\":\"ok\",\"evaluationMessage\":\"Accept\",\"subMessage\":\"testcallled\",\"score\":0}",
    "serviceId": 00343,
    "timestamp": "2021-10-02T03:13:51.951+00:00",
    "@timestamp": "2021-10-02T03:13:52.621643399+00:00"
  },
  "fields": {
    "@timestamp": [
      "2021-10-02T03:13:52.621Z"
    ],
    "requestDate": [
      "2021-10-02T03:13:12.541Z"
    ],
    "timestamp": [
      "2021-10-02T03:13:51.951Z"
    ]
  },
  "highlight": {
    "kubernetes.labels.app": [
      "@kibana-highlighted-field@test@/kibana-highlighted-field@"
    ]
  },
  "sort": [
    1654139632621
  ]
}

我需要 CSV 格式的输出，类似于：

serviceId, trackID, currency, grosscount
00343,KOLPSLSLSLL99029283,US,10

答案1

您的问题是关于提取存储在作为 JSON 字符串 ( ._source.payload) 嵌入到另一个 JSON 文档中的 JSON 文档中的某些信息。

要获取包含数据的 JSON 文档，我们首先需要从其宿主文档中解开该文档：

jq -r '._source.payload' guard.json

鉴于你问题中的例子，这会给我们一些东西相当于

{
  "serviceId": "00343",
  "AccessKey": "testdfsolpGS",
  "trackID": "KOLPSLSLSLL99029283",
  "membersId": "TEST_0233203203_030202020303",
  "shopperInfo": {
    "emailAddress": "[email protected]",
    "ipAddress": "localhost"
  },
  "parkid": {
    "parkssID": "carrier-park"
  },
  "cartinfo": {
    "checkType": "preorder",
    "detailsmetis": "card",
    "currency": "US",
    "grosscount": "10",
    "reedeem": ".00",
    "Discount": ".00",
    "tokenvalue": {
      "token": "11102020392023920920393993",
      "Bin": "00000",
      "digit": "0000",
      "expirationDate": "202509",
      "price": "10"
    }
  },
  "cartdetails": [
    {
      "dones": [
        {
          "donesnames": "test",
          "price": "003",
          "quan": "1"
        }
      ]
    }
  ]
}

从中，您需要选择.serviceId、.trackID、.cartinfo.currency和.cartinfo.grosscount作为带标题的 CSV。

我们可以这样做：

jq -r '._source.payload' guard.json |
jq -r '
    [ "serviceId", "trackID", "currency", "grosscount" ],
    [ .serviceId, .trackID, .cartinfo.currency, .cartinfo.grosscount ] |
    @csv'

第二个jq命令是从嵌入的 JSON 文档中提取信息。第一个数组只是标题字符串的数组，而第二个数组则是提取每列数据的数组。然后使用运算符将​​这两个数组格式化并输出为 CSV @csv。

您还可以通过在有效负载（嵌入的 JSON 文档）上jq使用单次调用来完成此操作：fromjson

jq -r '._source.payload | fromjson |
    [ "serviceId", "trackID", "currency", "grosscount" ],
    [ .serviceId, .trackID, .cartinfo.currency, .cartinfo.grosscount ] |
    @csv' guard.json

根据问题中的数据，这将产生

"serviceId","trackID","currency","grosscount"
"00343","KOLPSLSLSLL99029283","US","10"

添加位（2022 年 10 月）：

用于jq提取嵌入的有效负载，然后由 Miller 创建生成的 CSV：

$ jq -r ._source.payload file | mlr --j2c cut -f serviceId,trackID,cartinfo:currency,cartinfo:grosscount then label serviceId,trackID,currency,grosscount
serviceId,trackID,currency,grosscount
00343,KOLPSLSLSLL99029283,US,10

相关内容