How to parse from nested arrays - API/JQ

[ArtW]

Recently I pulled a CSV using the API on our MDS to see the last policy installation date of gateways within a domain:

mgmt_cli show gateways-and-servers limit 500 offset 0 details-level "full" -d "1.1.1.1" --root true --format json | /opt/CPshrd-R80.30/bin/jq '.objects[] | [ .["name"], .["policy"]["access-policy-name"], .["policy"]["access-policy-installation-date"]["iso-8601"] ] | @csv' -r > csv-policy-installation-date.csv

I'd hoped to modify the jq query to pull the hostnames along with the interface names - a similar query failed because the interface information is part of an array nested inside of the objects array.  

How would I pull the info so I can get the just the names of the gateways and their associated interface details (IP/Mask/Interface-name)?

Example of JSON I'm using on jqplay.org to drive myself to drinking:

{
"objects" : [ {
    "name" : "FW1",
    "interfaces" : [ {
      "interface-name" : "WAN",
      "ipv4-address" : "99.99.99.222",
      "ipv4-network-mask" : "255.255.255.252"
    }, {
      "interface-name" : "LAN1",
      "ipv4-address" : "10.99.99.4",
      "ipv4-network-mask" : "255.255.255.240"
    }  ],
    "junk" : "errata"
}, {
    "name" : "FW2",
    "interfaces": [ {
      "interface-name" : "WAN",
      "ipv4-address" : "99.99.99.223",
      "ipv4-network-mask" : "255.255.255.252"
     }, {
      "interface-name" : "LAN1",
      "ipv4-address" : "10.99.99.5",
      "ipv4-network-mask" : "255.255.255.240"
     } ]
} ]
}

[Greg_Dunlap]

personally this is where I've found simple jq parsing fall short and you need to extend into higher level programming language.  if you can export the json and import it to a box with python you can parse this out with a couple of nested loops looking at the length of the json (since in python json = dict native type).

<code>

#!/usr/bin/python3

import json

to_parse = {

    "objects" : [ {

          "name" : "FW1",

          "interfaces" : [ {

               "interface-name" : "WAN",

               "ipv4-address" : "99.99.99.222",

               "ipv4-network-mask" : "255.255.255.252"

           }, {

        "interface-name" : "LAN1",

"ipv4-address" : "10.99.99.4",

"ipv4-network-mask" : "255.255.255.240"

} ],

"junk" : "errata"

}, {

"name" : "FW2",

"interfaces": [ {

"interface-name" : "WAN",

"ipv4-address" : "99.99.99.223",

"ipv4-network-mask" : "255.255.255.252"

}, {

"interface-name" : "LAN1",

"ipv4-address" : "10.99.99.5",

"ipv4-network-mask" : "255.255.255.240"

} ]

} ]

}

for x in range(len(to_parse['objects'])):

       print(to_parse['objects'][x]['name'])

       for y in range(len(to_parse['objects'][x]['interfaces'])):

             print(to_parse['objects'][x]['interfaces'][y]['interface-name'])

             print(to_parse['objects'][x]['interfaces'][y]['ipv4-address'])

             print(to_parse['objects'][x]['interfaces'][y]['ipv4-network-mask'])

       print("----------------------------")

print("**************************************")

</code>

can give an output like :

FW1
WAN
99.99.99.222
255.255.255.252
LAN1
10.99.99.4
255.255.255.240
----------------------------
FW2
WAN
99.99.99.223
255.255.255.252
LAN1
10.99.99.5
255.255.255.240
----------------------------
**************************************

[Bob_Zimmerman]

The right way to do it depends on the exact output you want. Here are two examples which process the data from your example JSON:

$ cat test.json | jq ".objects[]|{name:.name,interfaces:[.interfaces[]|{name:.\"interface-name\"}]}"
{
  "name": "FW1",
  "interfaces": [
    {
      "name": "WAN"
    },
    {
      "name": "LAN1"
    }
  ]
}
{
  "name": "FW2",
  "interfaces": [
    {
      "name": "WAN"
    },
    {
      "name": "LAN1"
    }
  ]
}
$ cat test.json | jq ".objects[]|{name:.name,interfaces:.interfaces[]|.\"interface-name\"}" 
{
  "name": "FW1",
  "interfaces": "WAN"
}
{
  "name": "FW1",
  "interfaces": "LAN1"
}
{
  "name": "FW2",
  "interfaces": "WAN"
}
{
  "name": "FW2",
  "interfaces": "LAN1"
}

The first maintains more of the original structure of the input JSON.

The second is useful for times when you want to break it out into a full description of each individual inner item. Not great for this specific instance, but it would, for example, let you split out rules to each individual source:destination:service combination for mathematical analysis.

[Bob_Zimmerman]

Thought I should add an example of how the second could be useful. From this JSON:

{
	"rules":[
		{
			"name":"rule1",
			"source":[
				{"name":"SrcA","ipAddress":"10.10.0.1"},
				{"name":"SrcB","ipAddress":"10.10.0.2"},
				{"name":"SrcC","ipAddress":"10.10.0.3"}
			],
			"dest":[
				{"name":"DstD","ipAddress":"10.0.10.1"},
				{"name":"DstE","ipAddress":"10.0.10.2"},
				{"name":"DstF","ipAddress":"10.0.10.3"}
			],
			"service":[
				{"name":"ssh","port":22}
			]
		},{
			"name":"rule2",
			"source":[
				{"name":"SrcW","ipAddress":"10.20.0.1"},
				{"name":"SrcX","ipAddress":"10.20.0.2"}
			],
			"dest":[
				{"name":"DstY","ipAddress":"1.1.1.1"},
				{"name":"DstZ","ipAddress":"1.1.1.2"}
			],
			"service":[
				{"name":"http","port":80},
				{"name":"https","port":443}
			]
		}
	]
}

I can break the rules down into individual source:destination:service combinations like so:

$ cat test.json | jq -c ".rules[]|{name:.name,source:.source[]|.,dest:.dest[]|.,service:.service[]|.}"
{"name":"rule1","source":{"name":"SrcA","ipAddress":"10.10.0.1"},"dest":{"name":"DstD","ipAddress":"10.0.10.1"},"service":{"name":"ssh","port":22}}
{"name":"rule1","source":{"name":"SrcA","ipAddress":"10.10.0.1"},"dest":{"name":"DstE","ipAddress":"10.0.10.2"},"service":{"name":"ssh","port":22}}
{"name":"rule1","source":{"name":"SrcA","ipAddress":"10.10.0.1"},"dest":{"name":"DstF","ipAddress":"10.0.10.3"},"service":{"name":"ssh","port":22}}
{"name":"rule1","source":{"name":"SrcB","ipAddress":"10.10.0.2"},"dest":{"name":"DstD","ipAddress":"10.0.10.1"},"service":{"name":"ssh","port":22}}
{"name":"rule1","source":{"name":"SrcB","ipAddress":"10.10.0.2"},"dest":{"name":"DstE","ipAddress":"10.0.10.2"},"service":{"name":"ssh","port":22}}
{"name":"rule1","source":{"name":"SrcB","ipAddress":"10.10.0.2"},"dest":{"name":"DstF","ipAddress":"10.0.10.3"},"service":{"name":"ssh","port":22}}
{"name":"rule1","source":{"name":"SrcC","ipAddress":"10.10.0.3"},"dest":{"name":"DstD","ipAddress":"10.0.10.1"},"service":{"name":"ssh","port":22}}
{"name":"rule1","source":{"name":"SrcC","ipAddress":"10.10.0.3"},"dest":{"name":"DstE","ipAddress":"10.0.10.2"},"service":{"name":"ssh","port":22}}
{"name":"rule1","source":{"name":"SrcC","ipAddress":"10.10.0.3"},"dest":{"name":"DstF","ipAddress":"10.0.10.3"},"service":{"name":"ssh","port":22}}
{"name":"rule2","source":{"name":"SrcW","ipAddress":"10.20.0.1"},"dest":{"name":"DstY","ipAddress":"1.1.1.1"},"service":{"name":"http","port":80}}
{"name":"rule2","source":{"name":"SrcW","ipAddress":"10.20.0.1"},"dest":{"name":"DstY","ipAddress":"1.1.1.1"},"service":{"name":"https","port":443}}
{"name":"rule2","source":{"name":"SrcW","ipAddress":"10.20.0.1"},"dest":{"name":"DstZ","ipAddress":"1.1.1.2"},"service":{"name":"http","port":80}}
{"name":"rule2","source":{"name":"SrcW","ipAddress":"10.20.0.1"},"dest":{"name":"DstZ","ipAddress":"1.1.1.2"},"service":{"name":"https","port":443}}
{"name":"rule2","source":{"name":"SrcX","ipAddress":"10.20.0.2"},"dest":{"name":"DstY","ipAddress":"1.1.1.1"},"service":{"name":"http","port":80}}
{"name":"rule2","source":{"name":"SrcX","ipAddress":"10.20.0.2"},"dest":{"name":"DstY","ipAddress":"1.1.1.1"},"service":{"name":"https","port":443}}
{"name":"rule2","source":{"name":"SrcX","ipAddress":"10.20.0.2"},"dest":{"name":"DstZ","ipAddress":"1.1.1.2"},"service":{"name":"http","port":80}}
{"name":"rule2","source":{"name":"SrcX","ipAddress":"10.20.0.2"},"dest":{"name":"DstZ","ipAddress":"1.1.1.2"},"service":{"name":"https","port":443}}

This form of rule is ripe for optimization (that is, finding an efficient way to build rules or groups to reduce or eliminate duplication). You can also apply graph theory to find out if there's some other SrcD which has very similar access, but is missing from rule1.