Extract data from JSON document embedded in another JSON document

Question

I have a JSON document that embeds another JSON document. I need to extract data from the embedded document, but I'm not well versed with JSON or with jq.

The following is my input (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
  ]
}

I need output in CSV format, similar to this:

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

Answer 1

Your question is about extracting certain information stored in a JSON document embedded as a JSON string (._source.payload) in another JSON document.

To get at the JSON document with your data, we first need to unwrap the document from its host document:

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

Given the example in your question, this would give us something equivalent of

{
  "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"
        }
      ]
    }
  ]
}

From this, you'd like to pick out .serviceId, .trackID, .cartinfo.currency, and .cartinfo.grosscount as CSV, with headers.

We can do this like so:

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

The second jq command is what extracts the information from the embedded JSON document. The first array is simply an array of header strings, while the second array is what extracts the data for each column. These two arrays are then formatted and outputted as CSV using the @csv operator.

You could also do this with a single invocation of jq by using fromjson on the payload (the embedded JSON document):

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

With the data in the question, this would produce

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

---

Added bits (October 2022):

Using jq to extract the embedded payload, by then Miller to create the resulting 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