Select first element of a nested list of lists

Question

I am trying to get the first element in each nested list in a list of lists in JMESpath. Specifically, I am trying variations on:

aws redshift-data get-statement-result \
  --id 0de36215-f1db-421f-8704-3242e1a03f21 \
  --query 'Records[][0]'

Unfortunately this results in the parent list being firstly flattened, and thus I get just the first element of the first list.

The input data (simplified) is:

{   
    "Records": [
        [{"stringValue": "rdsdb"},{"etc": 1}],
        [{"stringValue": "testuser"},{"etc": 100}]
    ],
    "ColumnMetadata": [{"etc": true}],
    "TotalNumRows": 2
}

Given that input data, the output I would expect from that:

[
    {"stringValue": "rdsdb"},
    {"stringValue": "testuser"}
]   

How can I get the first element in each nested list in a list of lists?

Answer 1

From what I gather from your comment, you over-simplified your example and you actually after a whole object, and not a single property.

With that requirement, you can achieve it using [*] instead of [], as the first one does not flatten projections, compared to the second one.

So, given the JSON input:

{   
    "Records": [
        [{"stringValue": "rdsdb", "some-property": "foo"},{"etc": 1}],
        [{"stringValue": "testuser"},{"etc": 100}]
    ],
    "ColumnMetadata": [{"etc": true}],
    "TotalNumRows": 2
}

The query:

Records[*][0]

Yields the expected:

[
  {
    "stringValue": "rdsdb",
    "some-property": "foo"
  },
  {
    "stringValue": "testuser"
  }
]

---

Otherwise, you can filter out all the object in the array of array Records using a filter projection that would look if the key stringValue is not null with [?stringValue]

But because [] is creating a projection, as you raised, you have to stop that projection first, with the pipe operator |, as explained in the linked documentation chapter.

So given:

Records[]|[?stringValue]

This yields the expected:

[
  {
    "stringValue": "rdsdb"
  },
  {
    "stringValue": "testuser"
  }
]