Preserve hierarchy in JMESPath query

Question

When querying some document, I'd like to select items down in the hierarchy, but otherwise preserve the parent names in the structure. For example when querying aws database members, I can use:

DBClusters[].DBClusterMembers[].[DBInstanceIdentifier,IsClusterWriter]

To get from:

{
    "DBClusters": [
        {
            "DatabaseName": "bazbar",
            "DBClusterMembers": [
                {
                    "IsClusterWriter": false,
                    "DBClusterParameterGroupStatus": "in-sync",
                    "PromotionTier": 2,
                    "DBInstanceIdentifier": "foobar"
                },
    ...

To:

[
    [
        "foobar",
        false
    ],
    ...

But how can I get the following response instead:

{
    "bazbar": [
        [
             "foobar",
             false
        ],
        ...

I.e. for every cluster, extract the the DatabaseName as the key name for the list of members?

Answer 1

Context

• Jmespath query
• How to generate a query that preserves nesting of objects
• How to generate a dynamic object key that comes from the value of another object key-value pair

Solution

• http://jmespath.org/tutorial.html#object-projections
• http://jmespath.org/tutorial.html#list-and-slice-projections

Example

Assuming the following sample dataset ...

    {
        "dbclusters": [
            {
                "databasename": "alpha",
                "dbclustermembers": [
                    {
                        "isclusterwriter": false,
                        "dbinstanceidentifier": "foobar"
                    }
                    ,{
                        "isclusterwriter": true,
                        "dbinstanceidentifier": "doobar"
                    }
                ]
            }
            ,{
                "databasename": "bravo",
                "dbclustermembers": [
                    {
                        "isclusterwriter": false,
                        "dbinstanceidentifier": "foobar"
                    }
                ]
            }
            ,{
                "databasename": "charlie",
                "dbclustermembers": [
                    {
                        "isclusterwriter": false,
                        "dbinstanceidentifier": "foobar"
                    }
                ]
            }
        ]
    }

... the following jmespath query ...

    @.dbclusters[].{"databasename":databasename
        ,"dbclustermembers":[dbclustermembers[*].isclusterwriter
        ,dbclustermembers[*].dbinstanceidentifier][]
        }

... produces the following result ...

    [
      {
        "databasename": "alpha",
        "dbclustermembers": [
          false,
          true,
          "foobar",
          "doobar"
        ]
      },
      {
        "databasename": "bravo",
        "dbclustermembers": [
          false,
          "foobar"
        ]
      },
      {
        "databasename": "charlie",
        "dbclustermembers": [
          false,
          "foobar"
        ]
      }
    ]

Pitfalls

• this approach does not produce the database name as the object key as requested in the OP
  • As of this writing (2019-03-22), dynamic keys are not available in standard Jmespath
  • related stackoverflow post