I am trying to extract the ranges from the below JSON output,
{
"zscaler.net": {
"continent : EMEA": {
"city : Amsterdam II": [
{
"range": "165.225.240.0/23",
"vpn": "ams2-2-vpn.zscaler.net",
"gre": "165.225.240.12",
"hostname": "ams2-2.sme.zscaler.net",
"latitude": "52",
"longitude": "5"
},
{
"range": "147.161.132.0/23",
"vpn": "",
"gre": "",
"hostname": "",
"latitude": "52",
"longitude": "5"
}
],
"city : Brussels II": [
{
"range": "147.161.156.0/23",
"vpn": "",
"gre": "",
"hostname": "",
"latitude": "50",
"longitude": "5"
}
]
},
"continent : Americas": {
"city : Atlanta II": [
{
"range": "104.129.204.0/23",
"vpn": "atl2-vpn.zscaler.net",
"gre": "104.129.204.32",
"hostname": "atl2.sme.zscaler.net",
"latitude": "34",
"longitude": "-84"
},
{
"range": "136.226.2.0/23",
"vpn": "",
"gre": "104.129.204.32",
"hostname": "",
"latitude": "34",
"longitude": "-84"
}
]
}
}
}
Which I am successfully able to extract using the following command,
.["zscaler.net"]["continent : EMEA"] | .[][].range
What I am looking for is to try to get the parent continent and parent city of each range beside each other as comma separated,
165.225.240.0/23, EMEA, Amsterdam II
147.161.132.0/23, EMEA, Amsterdam II
147.161.156.0/23, EMEA, Brussels II
I am trying to achieve this completely in jq and bash.
Please help.
4
Answers
You can use
to_entriescombined withsub()to clean the key, then usejoin(",")(or@csv) to get the desired output:to_entriesto get the object key.key as $k[]) item in.value.rangeof the current object$k), but we usesubto replacecity :with nothing ("")join(", ")back the array to a csv stringJqPlay Demo
If you’d like the output for each continent, so remove the hardcoded
["continent : EMEA"], it can be replaced with another[]to loop over them and get the following output:Demo
You can combine the needed fields like so
(.first) (.second):Output:
Here’s the solution that matches the desired output from the question:
Output:
Here’s another way to do it by using
tostream.The output of
tostreamwill have all the parents and key for each value. This stream can be filtered (select) for therangekey and then the desired parts of the stream element can be extracted/formatted/output.Example output:
Try it on jqplay.org.
Solution Elaboration
The inspiration behind this solution was recognizing that all the desired output fields are contained in a single element of the stream output. Understanding the pipeline might be easiest by seeing what happens after each stage.
Filter:
Result:
You can see in the result above that each line where the stream element’s path (the first element of the array, which is an array) that ends with
"range"has all the fields in the desired output..[0][-1]accesses the the last array element of the first array for each element of the result stream above. The rest is just some filtering and formatting.Limit the pipeline data to just the
"range"lines:Result:
To use
join(",")at the end to create the desired CSV, the data needs to be "extracted" and put in an array.From the above result, the
"range"value is accessed with.[-1], i.e., the last element of the array. The"continent"and the"city"are the first and second element of the array that is itself the first element of the overall array, i.e.,.[0][0:2]. Just the value needs to be extracted from these elements and this can be done withsplit(":"). Since this needs to be done for each element, you can iterate each element with.[0][0:2][]. Piping this intosplit(":")formats the output as desired.Filter:
Result:
All that’s left is to format the output as CSV with
join(",")as shown in the original solution above.