Question posted in Json
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Filter json query complex nested list

afrancopt
July 22, 2023
137 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.