Ansible — JSON_QUERY Nested KeyValuePair

Have you had troubles writing json_query with jmespath to help filter objects? Perhaps within a key/value pair within a nested object and need to leverage that for a new list?

Lets look at this example:

students: [{
    "Id": "125",
    "Name": "John Smith",
    "Grades": [
      {
        "Key": "Math",
        "Value": "65"
      },
      {
        "Key": "English",
        "Value": "85"
      }
    ]
  },
  {
    "Id": "234",
    "Name": "Jane Smith",
    "Grades": [
      {
        "Key": "Math",
        "Value": "85"
      },
      {
        "Key": "Science",
        "Value": "90"
      }
    ]
  },
  {
    "Id": "432",
    "Name": "Bob Smith",
    "Grades": [
      {
        "Key": "Art",
        "Value": "95"
      }
    ]
  }
]

Lets say we want to use ansible to grab Students that have received over 80 in Math, but return the student object for each that qualified.

Here would be sample code to first extract Math to top level to then query:

- name: Students with Math
  debug:
    msg: "{{ students | json_query(jmesquery) }}"
  vars:
  jmesquery: "[].{Id: Id, Name: Name, Grades: Grades, Math: (Grades[?Key=='Math'].Value)[0]}"

This results in the following:

From there its a simple query; so we expand on code above to the following:

- name: Students with Math above 80
      debug:
        msg: "{{ students | json_query(jmesquery) }}"
      vars:
        jmesquery: "[].{Id: Id, Name: Name, Grades: Grades, Math: (Grades[?Key=='Math'].Value)[0]} | [?Math>'80']"

Resulting in the following:

Hopefully this helped show how to turn what may seem like a complex query into something relatively simple.