Filter nested array using jmes query

Question

I have to get the name of companies in which 'John' worked in the 'sales' department. My JSON looks like this:

[
            {
            "name" : "John",
            "company" : [{
                        "name" : "company1",
                        "department" : "sales"
                        },
                        {
                        "name" : "company2",
                        "department" : "backend"
                        },
                        {
                        "name" : "company3",
                        "department" : "sales"
                        }
                      ],
            "phone" : "1234"
            }
]

And my jmesquery is like this:

jmesquery: "[? name=='John'].company[? department=='sales'].{Company: name}"

But with this query, I'm getting a null array.

Answer 1

This is because your first filter [?name=='John'] is creating a projection, and more specifically a filter projection, that you will have to reset in order to further filter it.
Resetting a projection can be achieved using pipes.

Projections are an important concept in JMESPath. However, there are times when projection semantics are not what you want. A common scenario is when you want to operate of the result of a projection rather than projecting an expression onto each element in the array. For example, the expression people[*].first will give you an array containing the first names of everyone in the people array. What if you wanted the first element in that list? If you tried people[*].first[0] that you just evaluate first[0] for each element in the people array, and because indexing is not defined for strings, the final result would be an empty array, []. To accomplish the desired result, you can use a pipe expression, <expression> | <expression>, to indicate that a projection must stop.

^{Source: https://jmespath.org/tutorial.html#pipe-expressions}

So, here would be a first step in your query:

[?name=='John'] | [].company[?department=='sales'].{Company: name}

---

This said, this still ends in an array of array:

[
  [
    {
      "Company": "company1"
    },
    {
      "Company": "company3"
    }
  ]
]

Because you can end up with multiple people named John in a sales department.
So, one array for the users and another for the companies/departments.

In order to fix this, you can use the flatten operator: [].

So we end with:

[?name=='John'] | [].company[?department=='sales'].{Company: name} []

Which gives:

[
  {
    "Company": "company1"
  },
  {
    "Company": "company3"
  }
]