Filter json query complex nested list

afrancopt
July 22, 2023
122 views
1 vote
2 Answers

I have the following:

          {
              "additionalInfo": [],
              "id": "8d929134-0c71-48d9-baba-28fb5eab92f2",
              "instanceTenantId": "62f4c8ab6a041c1c090f7994",
              "name": "Global",
              "siteHierarchy": "8d929134-0c71-48d9-baba-28fb5eab92f2",
              "siteNameHierarchy": "Global"
          },
          {
              "additionalInfo": [
                  {
                      "attributes": {
                          "addressInheritedFrom": "0d5e4dc7-ce4e-4aff-bf9f-bb8242797dce",
                          "type": "area"
                      },
                      "nameSpace": "Location"
                  }
              ],
              "id": "1720c283-a903-43f9-996e-a25d63409d69",
              "instanceTenantId": "62f4c8ab6a041c1c090f7994",
              "name": "SOUTH SOUTH",
              "parentId": "90483b39-e1b3-4135-b1b9-3b7b94d5b2f5",
              "siteHierarchy": "8d929134-0c71-48d9-baba-28fb5eab92f2/0d5e4dc7-ce4e-4aff-bf9f-bb8242797dce/90483b39-e1b3-4135-b1b9-3b7b94d5b2f5/1720c283-a903-43f9-996e-a25d63409d69",
              "siteNameHierarchy": "Global/FCMB NETWORK/BRANCH SITES/SOUTH SOUTH"
          },
          {
              "additionalInfo": [
                  {
                      "attributes": {
                          "floorIndex": "1",
                          "imageURL": "",
                          "isCadFile": "false",
                          "rfModel": "103103"
                      },
                      "nameSpace": "mapsSummary"
                  },
                  {
                      "attributes": {
                          "height": "15.0",
                          "length": "100.0",
                          "offsetX": "0.0",
                          "offsetY": "0.0",
                          "width": "100.0"
                      },
                      "nameSpace": "mapGeometry"
                  },
                  {
                      "attributes": {
                          "address": "Nigeria",
                          "addressInheritedFrom": "d518b161-85f7-434c-91ae-3eface9cc1d5",
                          "type": "floor"
                      },
                      "nameSpace": "Location"
                  }
              ],
              "id": "33e64ab2-641b-4778-adcd-95c832199f47",
              "instanceTenantId": "62f4c8ab6a041c1c090f7994",
              "name": "FLOOR-1",
              "parentId": "d518b161-85f7-434c-91ae-3eface9cc1d5",
              "siteHierarchy": "8d929134-0c71-48d9-baba-28fb5eab92f2/0d5e4dc7-ce4e-4aff-bf9f-bb8242797dce/90483b39-e1b3-4135-b1b9-3b7b94d5b2f5/aef6ef8c-d3ba-4a1c-a0e7-fad4e00e1c5f/d518b161-85f7-434c-91ae-3eface9cc1d5/33e64ab2-641b-4778-adcd-95c832199f47",
              "siteNameHierarchy": "Global/FCMB NETWORK/BRANCH SITES/LAGOS/ASPAMDA/FLOOR-1"
          }

What I want to do is to filter the json query in such a way that obtain the "id" value only if there’s and attribute "type": "floor"

Initially I tried to at least save only the attributes if type was equal to floor using the following with no success (among other options):

    - name: Save results to variable 
      set_fact:
        my_test: "{{ results.dnac_response | json_query(myquery) }}"
      vars: 
        myquery: "response[*].additionalInfo[*].attributes[? type=='floor].type"

But, ideally what I would like is to get a list of "id" values under the condition indicated above. Any ideas? Thank you in advance.

Tags: ansible json

Answers

Chosen as BEST ANSWER

With this:

  - name: Save results to variable 2
  set_fact:
    my_test: "{{ results.dnac_response | json_query(myquery) }}"
  vars: 
    myquery: "response[*].{ID: id, Attributes: additionalInfo[*].attributes.type}"

I manage to obtain this:

{
    "my_test": [
        {
            "Attributes": [],
            "ID": "8d929134-0c71-48d9-baba-28fb5eab92f2"
        },
        {
            "Attributes": [
                "area"
            ],
            "ID": "1720c283-a903-43f9-996e-a25d63409d69"
        },
        {
            "Attributes": [
                "floor"
            ],
            "ID": "33e64ab2-641b-4778-adcd-95c832199f47"
        }, 
        ...output omitted

But I would still to filter that to get "ID" only when attributes is "floor" and create a list that I can use with loop. I now little, so any help would be really appreciated.

(Edit)

Given the data

    response:
      - additionalInfo: []
        id: 8d929134-0c71-48d9-baba-28fb5eab92f2
        instanceTenantId: 62f4c8ab6a041c1c090f7994
        name: Global
        siteHierarchy: 8d929134-0c71-48d9-baba-28fb5eab92f2
        siteNameHierarchy: Global
      - additionalInfo:
        - attributes: {addressInheritedFrom: 0d5e4dc7, type: area}
          nameSpace: Location
        id: 1720c283-a903-43f9-996e-a25d63409d69
        instanceTenantId: 62f4c8ab6a041c1c090f7994
        name: SOUTH SOUTH
        parentId: 90483b39-e1b3-4135-b1b9-3b7b94d5b2f5
        siteHierarchy: 8d929134-0c71-48d9-baba-28fb5eab92f2
        siteNameHierarchy: Global/FCMB NETWORK/BRANCH SITES/SOUTH SOUTH
      - additionalInfo:
        - attributes: {floorIndex: '1', imageURL: '', isCadFile: 'false', rfModel: '103103'}
          nameSpace: mapsSummary
        - attributes: {height: '15.0', length: '100.0', offsetX: '0.0', offsetY: '0.0',
            width: '100.0'}
          nameSpace: mapGeometry
        - attributes: {address: Nigeria, addressInheritedFrom: d518b161, type: floor}
          nameSpace: Location
        id: 33e64ab2-641b-4778-adcd-95c832199f47
        instanceTenantId: 62f4c8ab6a041c1c090f7994
        name: FLOOR-1
        parentId: d518b161-85f7-434c-91ae-3eface9cc1d5
        siteHierarchy: 8d929134-0c71-48d9-baba-28fb5eab92f2
        siteNameHierarchy: Global/FCMB NETWORK/BRANCH SITES/LAGOS/ASPAMDA/FLOOR-1

Create the dictionary

  id_type: "{{ dict(response|
                    json_query('[].[id,
                                    additionalInfo[].attributes.type]')) }}"

gives

  id_type:
    1720c283-a903-43f9-996e-a25d63409d69: [area]
    33e64ab2-641b-4778-adcd-95c832199f47: [floor]
    8d929134-0c71-48d9-baba-28fb5eab92f2: []

Select the id

  my_test: "{{ id_type|dict2items|
               selectattr('value', 'contains', 'floor')|
               map(attribute='key') }}"

gives

  my_test:
    - 33e64ab2-641b-4778-adcd-95c832199f47

Example of a complete playbook for testing

- hosts: localhost

  vars:

    response:
      - additionalInfo: []
        id: 8d929134-0c71-48d9-baba-28fb5eab92f2
        instanceTenantId: 62f4c8ab6a041c1c090f7994
        name: Global
        siteHierarchy: 8d929134-0c71-48d9-baba-28fb5eab92f2
        siteNameHierarchy: Global
      - additionalInfo:
        - attributes: {addressInheritedFrom: 0d5e4dc7, type: area}
          nameSpace: Location
        id: 1720c283-a903-43f9-996e-a25d63409d69
        instanceTenantId: 62f4c8ab6a041c1c090f7994
        name: SOUTH SOUTH
        parentId: 90483b39-e1b3-4135-b1b9-3b7b94d5b2f5
        siteHierarchy: 8d929134-0c71-48d9-baba-28fb5eab92f2
        siteNameHierarchy: Global/FCMB NETWORK/BRANCH SITES/SOUTH SOUTH
      - additionalInfo:
        - attributes: {floorIndex: '1', imageURL: '', isCadFile: 'false', rfModel: '103103'}
          nameSpace: mapsSummary
        - attributes: {height: '15.0', length: '100.0', offsetX: '0.0', offsetY: '0.0',
            width: '100.0'}
          nameSpace: mapGeometry
        - attributes: {address: Nigeria, addressInheritedFrom: d518b161, type: floor}
          nameSpace: Location
        id: 33e64ab2-641b-4778-adcd-95c832199f47
        instanceTenantId: 62f4c8ab6a041c1c090f7994
        name: FLOOR-1
        parentId: d518b161-85f7-434c-91ae-3eface9cc1d5
        siteHierarchy: 8d929134-0c71-48d9-baba-28fb5eab92f2
        siteNameHierarchy: Global/FCMB NETWORK/BRANCH SITES/LAGOS/ASPAMDA/FLOOR-1

    id_type: "{{ dict(response|
                      json_query('[].[id,
                                      additionalInfo[].attributes.type]')) }}"
    my_test: "{{ id_type|dict2items|
                 selectattr('value', 'contains', 'floor')|
                 map(attribute='key') }}"
  tasks:

    - debug:
        var: id_type|to_yaml
    - debug:
        var: my_test

Please signup or login to give your own answer.

Click here to cancel reply.