Access nested JSON with unknown keys?

Question

I have the following json data:

{"results":
{"xxxxxx":{"id":"as23","status":1,"res":"xsd"},
{"xxxxxx":{"id":"fds34","status":2,"res":"dox"},
{"xxxxxx":{"id":"as23","status":1,"res":"kog"},
{"xxxxxx":{"id":"dgs5","status":2,"res":"dox"},
{"xxxxxx":{"id":"as23","status":2,"res":"dox"},
{"xxxxxx":{"id":"as23","status":1,"res":"dox"}

The list is very long, but you get the idea. What I want, is to look up for every object containing the id as23, and count how many of them have the status 1.

So, I am using this in HomeAssistant as a rest sensor, and my filter expression looks like this:

{{ value_json.results|selectattr("id", "==", "as23")|selectattr("status", "eq", 1)|list|length }}

My issue is: I don't know what the value of xxxxxx are, and they can differ, and still contain the id as23.
How to achieve this?

What you are proving here is not a valid JSON, are you sure you cannot have a value as multiple objects like that without having an array. Please provide a real [mre]. — β.εηοιτ.βε, Oct 07 '22 at 10:33

score 3 · Answer 1 · answered Oct 07 '22 at 10:46

Assuming a correct version of your JSON that would be:

{
   "results":[
      {
         "xxxxxx":{
            "id":"as23",
            "status":1,
            "res":"xsd"
         }
      },
      {
         "xxxxxx":{
            "id":"fds34",
            "status":2,
            "res":"dox"
         }
      },
      {
         "xxxxxx":{
            "id":"as23",
            "status":1,
            "res":"kog"
         }
      },
      {
         "xxxxxx":{
            "id":"dgs5",
            "status":2,
            "res":"dox"
         }
      },
      {
         "xxxxxx":{
            "id":"as23",
            "status":2,
            "res":"dox"
         }
      },
      {
         "xxxxxx":{
            "id":"as23",
            "status":1,
            "res":"dox"
         }
      }
   ]
}

Your requirement can be easily achieved with the help of the filter json_query and the JMESPath query language, that help you achieve wildcards on properties.

Given the debug task:

- debug:
    var: >-
      value_json | json_query(
        'length(results[].*[] | [?id == `as23` && status == `1`])'
      )

This would yield the expected:

ok: [localhost] => 
  ? |-
    value_json | json_query(
      'length(results[].*[] | [?id == `as23` && status == `1`])'
    )
  : '3'

score 0 · Accepted Answer · answered Oct 09 '22 at 13:14

Building up on @β.εηοιτ.βε's answer, this can be achieved without json_query (in case you don't want to / can't pip install jmespath) using only core functions.

Using the same assumed correct json structure as in @β.εηοιτ.βε's answer, the following task:

    - debug:
        msg: "{{ 
          value_json.results | 
          map('dict2items') | 
          map('map', attribute='value') |
          flatten |
          selectattr('id', '==', 'as23') |
          selectattr('status', '==', 1)
        }}"

Gives:

TASK [debug] ***************************************************************************************************************************************************************************************************************************
ok: [localhost] => {
    "msg": [
        {
            "id": "as23",
            "res": "xsd",
            "status": 1
        },
        {
            "id": "as23",
            "res": "kog",
            "status": 1
        },
        {
            "id": "as23",
            "res": "dox",
            "status": 1
        }
    ]
}

Access nested JSON with unknown keys?

2 Answers2