Remove square brackets from JMESPath expression

Question

I have following JSON:

{
    "694992": [
        {
            "domain": "example.com",
            "domain_id": 49392164,
            "data": [
                {
                    "category": "Main",
                    "category_id": 77133,
                    "data": [
                        {
                            "keyword": "sofort",
                            "key_id": 25963217,
                            "data": {
                                "9242": [
                                    27,
                                    "https://www.example.com/sofort",
                                    false,
                                    false,
                                    1
                                ]
                            }
                        },
                        {
                            "keyword": "das",
                            "key_id": 32325213,
                            "data": {
                                "9242": [
                                    23,
                                    "https://www.example.com/das",
                                    false,
                                    false,
                                    1
                                ]
                            }
                        },
                        {
                            "keyword": "wiki",
                            "key_id": 32325317,
                            "data": {
                                "9242": [
                                    44,
                                    "https://www.example.com/wiki",
                                    false,
                                    false,
                                    1
                                ]
                            }
                        }
                    ]
                }
            ]
        }
    ]
}

With JMESPath expression i want to extract some data from JSON and get it in the following form:

+---------+--------+--------------------------------+
| Keyword | Number | URL                            |
+---------+--------+--------------------------------+
| sofort  | 27     | https://www.example.com/sofort |
+---------+--------+--------------------------------+
| das     | 23     | https://www.example.com/das    |
+---------+--------+--------------------------------+
| wiki    | 44     | https://www.example.com/wiki   |
+---------+--------+--------------------------------+

I use the following JMESPath expression:

*[].data[].data[].{Keyword: keyword, Number: data.*[0], URL: data.*[1]}

But my output is like

+---------+--------+----------------------------------+
| Keyword | Number | URL                              |
+---------+--------+----------------------------------+
| sofort  | [27]   | [https://www.example.com/sofort] |
+---------+--------+----------------------------------+
| das     | [23]   | [https://www.example.com/das]    |
+---------+--------+----------------------------------+
| wiki    | [44]   | [https://www.example.com/wiki]   |
+---------+--------+----------------------------------+

How can i remove square brackets from Number and URL?

Answer 1

This would happen because the * in data.* could return multiple values, for example from:

{
    "keyword": "wiki",
    "key_id": 32325317,
    "data": {
        "9242": [
            44,
            "https://www.example.com/wiki",
            false,
            false,
            1
        ],
        "9243": [
            44,
            "https://www.example.com/wiki",
            false,
            false,
            1
        ]
    }
}

Which is a perfectly valid JSON.

If you are sure that your data will always contain only one element, you could use the pipe expression: | to stop a projection and help you extract the first element out of an array.

From the documentation:

Pipe expression are useful for stopping projections. They can also be used to group expressions.

^{Source: https://jmespath.org/examples.html#pipes
This is also illustrated in this example: https://jmespath.org/examples.html#working-with-nested-data}

So your expression end up being:

*[].data[].data[].{Keyword: keyword, Number: data.*[0] | [0], URL: data.*[1] | [0]}

Which gives the expected:

[
  {
    "Keyword": "sofort",
    "Number": 27,
    "URL": "https://www.example.com/sofort"
  },
  {
    "Keyword": "das",
    "Number": 23,
    "URL": "https://www.example.com/das"
  },
  {
    "Keyword": "wiki",
    "Number": 44,
    "URL": "https://www.example.com/wiki"
  }
]