JMESPath query for nested array structures

Question

I have the following data structure as a result of aws logs get-query-results:

    {
    "status": "Complete", 
    "statistics": {
        "recordsMatched": 2.0, 
        "recordsScanned": 13281.0, 
        "bytesScanned": 7526096.0
    }, 
    "results": [
        [
            {
                "field": "time", 
                "value": "2019-01-31T21:53:01.136Z"
            }, 
            {
                "field": "requestId", 
                "value": "a9c233f7-0b1b-3326-9b0f-eba428e4572c"
            }, 
            {
                "field": "logLevel", 
                "value": "INFO"
            }, 
            {
                "field": "callerId", 
                "value": "a9b0f9c2-eb42-3986-33f7-8e450b1b72cf"
            }
        ],
        [
            {
                "field": "time", 
                "value": "2019-01-25T13:13:01.062Z"
            }, 
            {
                "field": "requestId", 
                "value": "a4332628-1b9b-a9c2-0feb-0cd4a3f7cb63"
            }, 
            {
                "field": "logLevel", 
                "value": "INFO"
            }, 
            {
                "field": "callerId", 
                "value": "a9b0f9c2-eb42-3986-33f7-8e450b1b72cf"
            }
        ],
      ]
    }

The AWS CLI support JMESPath language for filtering output. I need to apply a query string, to filter among the returned "results" the objects that contain the "callerId" as a "field", retrieve the "value" property and obtain the following output:

    [
      {
       callerId: "a9b0f9c2-eb42-3986-33f7-8e450b1b72cf"
      },
      {
       callerId: "a9b0f9c2-eb42-3986-33f7-8e450b1b72cf"
      }
    ]

The first step I do is flatter the results array with the query string: results[]

This will get read of the other root properties (status, statistics) and return only one big array with all of the {field: ..., value: ...} alike objects. But after this I can't manage to properly filter for those objects that match field=="callerId". I tried, among others, the following expressions without success:

'results[][?field=="callerId"]'
'results[][*][?field=="callerId"]'
'results[].{ callerId: @[?field=="callerId"].value }'

I'm not an expert in JMESPath and I was doing the tutorials of the jmespath.org site but couldn't manage to make it work.

Thanks!

delete the last comma, if you place your json in the JMES Path tester the code would not work — bosskay972, Jul 17 '19 at 08:49

score 4 · Accepted Answer · answered Jul 17 '19 at 08:39

Using jq is a good thing because it's more complete language, but if you want to do it with JMES Path here the solution:

results[*][?field=='callerId'].{callerId: value}[]

to get:

[
  {
    "callerId": "a9b0f9c2-eb42-3986-33f7-8e450b1b72cf"
  },
  {
    "callerId": "a9b0f9c2-eb42-3986-33f7-8e450b1b72cf"
  }
]

score 1 · Answer 2 · answered Jun 29 '19 at 15:22

I'm not able to reproduce fully since I don't have the same logs in my log stream but I was able to do this using jq and putting the sample JSON object in a file

cat sample_output.json | jq '.results[][] | select(.field=="callerId") | .value'

OUTPUT:

"a9b0f9c2-eb42-3986-33f7-8e450b1b72cf"
"a9b0f9c2-eb42-3986-33f7-8e450b1b72cf"

you could pipe the output from the aws cli to jq.

I was able to get pretty close with the native JMESPath query and using the built in editor in this site http://jmespath.org/examples.html#filtering-and-selecting-nested-data

results[*][?field==`callerId`][]

OUTPUT:

[
  {
    "field": "callerId",
    "value": "a9b0f9c2-eb42-3986-33f7-8e450b1b72cf"
  },
  {
    "field": "callerId",
    "value": "a9b0f9c2-eb42-3986-33f7-8e450b1b72cf"
  }
]

but I'm not sure how to get callerId to be the key and the value to be the value from another key.

thanks @jmp, at some point I got something very similar but unfortunately the requirements asked me for JMESPath only. — fjf, Sep 11 '19 at 20:40

JMESPath query for nested array structures

2 Answers2