Ansible, how to query deeply nested json keys

Question

I'm using Ansible to make an API call that returns a huge data set, and I need to be able to get a nested value to print to the screen. I tried using json_query but not sure what I'm doing wrong.

My task:

- name: Get certificate by CN name.
      uri:
        method: GET
        url: "https://mayapi/api/1/certificates?filter=cn;{{ inventory_hostname }}"
        headers:
          Authorization: Bearer {{ login.json.token }}
          Content-Type: application/json
        validate_certs: no
      register: certs
      
- name: Print certs for application 
  debug:
    msg: "{{ certs.json | json_query(items) }}"

This is a small snippet of the output. I want to be able to print ID, and email.

{
    "msg": {
        "changed": false,
        "connection": "close",
        "content_length": "65833",
        "content_type": "application/json",
        "cookies": {},
        "cookies_string": "",
        "date": "Mon, 10 May 2021 21:33:29 GMT",
        "elapsed": 0,
        "failed": false,
        "json": {
            "items": [
                {
                    "active": true,
                    "application": [
                        {
                            "director": {
                                "active": true,
                                "email": "user@domain.com",
                                "fullname": "John Doe",
                                "id": 1611,
                                "manager": "John Doe",
                                "managerEmail": "johndoe@email.com",
                                "username": "jdoe"
                            },
                            ...
                            ...
                            ...
}

I get the following error indicating "certs.items" doesn't exist:

FAILED! => {"msg": "Error in jmespath.search in json_query filter plugin:\n'items' is undefined"}

I was expecting all of the items to get printed to the screen and then if I wanted something below items I would do items.active, items.application, etc... But this is not correct since I keep erroring out.

I also tried looping through cert.json and cert.json.items:

- name: Print certs for application 
    debug:
      msg: "{{ item.application.name }}"
    loop: "{{ certs.json}}"

But get this error message:

{"msg": "Invalid data passed to 'loop', it requires a list, got this instead: {u'items': [{u'status': u'Active-Pending Install'...shows all the data of the nested json

Then I tried this:

- name: Print certs for application 
    debug:
      msg: "{{ item.application.name }}"
    loop: "{{ certs.json.items}}"

But got this error message:

{"msg": "Invalid data passed to 'loop', it requires a list, got this instead: <built-in method items of dict object at 0x7f0c9ec43050>. Hint: If you passed a list/dict of just one element, try adding wantlist=True to your lookup invocation or use q/query instead of lookup."}

Made some progress with this:

- name: Print certs for application 
    debug:
      msg: "KEY:::: {{ item.key }}, VALUE:::: {{ item.value.0 }}"
    loop: "{{ lookup('dict', certs.json) }}"
    when: "'items' in item.key"
    ignore_errors: yes

But this only prints items in index 0 of the list:

"msg": "KEY:::: items, VALUE:::: {u'status': u'Active-Pending Install', u'serialHex': u'1111', u'validityStart': u'2021-05-10T21:01:36+00:00', u'cn': u'node2.test.corp.net', u'validityEnd': u'2023-05-10T21:11:36+00:00', u'application': [{u'uuid': u'2222', u'name': u'abc'}], u'certType': u'CertType.INTERNAL', u'id': 2582, u'issuer': u'server1'}"

I'm trying to print the 'cn', 'id', and 'serialHex' values from each list element for the key 'items'.

This is the data set that I'm trying to query with Ansible:

{
    "total": 2,
    "items": [
        {
            "application": [
                {
                    "uuid": "111",
                    "name": "CDE"
                }
            ],
            "validityEnd": "2023-05-10T21:11:36+00:00",
            "certType": "CertType.INTERNAL",
            "issuer": "server1",
            "id": 2582,
            "validityStart": "2021-05-10T21:01:36+00:00",
            "status": "Active-Pending Install",
            "serialHex": "aaa",
            "cn": "node2.corp.net"
        },
        {
            "application": [
                {
                    "uuid": "222",
                    "name": "CDE"
                }
            ],
            "validityEnd": "2023-05-10T21:05:26+00:00",
            "certType": "CertType.INTERNAL",
            "issuer": "server1",
            "id": 2581,
            "validityStart": "2021-05-10T20:55:26+00:00",
            "status": "Active-Pending Install",
            "serialHex": "bbbb",
            "cn": "node1.corp.net"
        }
    ]
}

Answer 1

You are regrettably stepping on a quirk of "objects in ansible are python dicts" in that .items() and .keys() and quite a few other attributes-which-are-methods cannot be referenced using the . notation since jinja2 believes you intend to call that method. Rather, one must use the __getitem__ syntax of ["items"] in order to make it abundantly clear that you mean the dict key, and not the method of the same name

  tasks:
    - name: use json_query as you were originally asking
      debug:
        msg: >-
          {{ certs.json | json_query('items[*].{c: cn,i: id,s: serialHex}') }}

    - name: or a jinja2 for loop as you separately attempted
      debug:
        msg: >-
          [
          {%- for i in certs.json["items"] -%}
          {{ "" if loop.first else "," }}
          {{ [i.cn, i.id, i.serialHex ]}}
          {%- endfor -%}
          ]

produces the output from their respective steps:

TASK [debug] ******************************************************************************************************************************
ok: [localhost] => {
    "msg": [
        {
            "c": "node2.corp.net",
            "i": 2582,
            "s": "aaa"
        },
        {
            "c": "node1.corp.net",
            "i": 2581,
            "s": "bbbb"
        }
    ]
}

TASK [debug] ******************************************************************************************************************************
ok: [localhost] => {
    "msg": [
        [
            "node2.corp.net",
            2582,
            "aaa"
        ],
        [
            "node1.corp.net",
            2581,
            "bbbb"
        ]
    ]
}