jq - extract parent key values as comma separated values next to child elements

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.

You can use to_entries combined with sub() to clean the key, then use join(",") (or @csv) to get the desired output:

.["zscaler.net"]["continent : EMEA"] 
    | to_entries[] 
    | .key as $k 
    | .value[] 
    | [ .range, ($k | sub("city : "; "" )) ] | join(", ")

1. First we target the correct object

   .["zscaler.net"]["continent : EMEA"]
   

2. Then we use to_entries to get the object key
3. Save the key: .key as $k
4. For each ([]) item in .value
5. Create a new array, containing
   • The .range of the current object
   • The key ($k), but we use sub to replace city : with nothing ("")
6. join(", ") back the array to a csv string

"165.225.240.0/23, Amsterdam II"
"147.161.132.0/23, Amsterdam II"
"147.161.156.0/23, Brussels II"

JqPlay 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:

.["zscaler.net"][] | to_entries[] | .key as $k | .value[] | [ .range, ($k | sub("city : "; "" )) ] | join(", ")

165.225.240.0/23, Amsterdam II
147.161.132.0/23, Amsterdam II
147.161.156.0/23, Brussels II
104.129.204.0/23, Atlanta II
136.226.2.0/23, Atlanta II

Demo

Here's the solution that matches the desired output from the question:

.["zscaler.net"] | to_entries[]
| (.key|ltrimstr("continent : ")) as $cont
| .value | to_entries[]
| "\(.value[].range), \($cont), \(.key|ltrimstr("city : "))"

Output:

165.225.240.0/23, EMEA, Amsterdam II
147.161.132.0/23, EMEA, Amsterdam II
147.161.156.0/23, EMEA, Brussels II
104.129.204.0/23, Americas, Atlanta II
136.226.2.0/23, Americas, Atlanta II

Here's another way to do it by using tostream.

The output of tostream will have all the parents and key for each value. This stream can be filtered (select) for the range key and then the desired parts of the stream element can be extracted/formatted/output.

.["zscaler.net"] | tostream
| select(.[0][-1] == "range")
| [.[-1], .[0][0:2][]|split(":")[-1]]
| join(",")

Example output:

165.225.240.0/23, EMEA, Amsterdam II
147.161.132.0/23, EMEA, Amsterdam II
147.161.156.0/23, EMEA, Brussels II
104.129.204.0/23, Americas, Atlanta II
136.226.2.0/23, Americas, Atlanta II

Try it on [jqplay.org](https://jqplay.org/s/d0xhGQMiRJf "Click me!").

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:

.["zscaler.net"] | tostream

[Result](https://jqplay.org/s/PouQf4LDuE1 "link to jqplay -->"):

[["continent : EMEA","city : Amsterdam II",0,"range"],"165.225.240.0/23"]
[["continent : EMEA","city : Amsterdam II",0,"vpn"],"ams2-2-vpn.zscaler.net"]
[["continent : EMEA","city : Amsterdam II",0,"gre"],"165.225.240.12"]
[["continent : EMEA","city : Amsterdam II",0,"hostname"],"ams2-2.sme.zscaler.net"]
[["continent : EMEA","city : Amsterdam II",0,"latitude"],"52"]
[["continent : EMEA","city : Amsterdam II",0,"longitude"],"5"]
[["continent : EMEA","city : Amsterdam II",0,"longitude"]]
[["continent : EMEA","city : Amsterdam II",1,"range"],"147.161.132.0/23"]
[["continent : EMEA","city : Amsterdam II",1,"vpn"],""]
[["continent : EMEA","city : Amsterdam II",1,"gre"],""]
[["continent : EMEA","city : Amsterdam II",1,"hostname"],""]
[["continent : EMEA","city : Amsterdam II",1,"latitude"],"52"]
[["continent : EMEA","city : Amsterdam II",1,"longitude"],"5"]
[["continent : EMEA","city : Amsterdam II",1,"longitude"]]
[["continent : EMEA","city : Amsterdam II",1]]
[["continent : EMEA","city : Brussels II",0,"range"],"147.161.156.0/23"]
[["continent : EMEA","city : Brussels II",0,"vpn"],""]
[["continent : EMEA","city : Brussels II",0,"gre"],""]
[["continent : EMEA","city : Brussels II",0,"hostname"],""]
[["continent : EMEA","city : Brussels II",0,"latitude"],"50"]
[["continent : EMEA","city : Brussels II",0,"longitude"],"5"]
[["continent : EMEA","city : Brussels II",0,"longitude"]]
[["continent : EMEA","city : Brussels II",0]]
[["continent : EMEA","city : Brussels II"]]
[["continent : Americas","city : Atlanta II",0,"range"],"104.129.204.0/23"]
[["continent : Americas","city : Atlanta II",0,"vpn"],"atl2-vpn.zscaler.net"]
[["continent : Americas","city : Atlanta II",0,"gre"],"104.129.204.32"]
[["continent : Americas","city : Atlanta II",0,"hostname"],"atl2.sme.zscaler.net"]
[["continent : Americas","city : Atlanta II",0,"latitude"],"34"]
[["continent : Americas","city : Atlanta II",0,"longitude"],"-84"]
[["continent : Americas","city : Atlanta II",0,"longitude"]]
[["continent : Americas","city : Atlanta II",1,"range"],"136.226.2.0/23"]
[["continent : Americas","city : Atlanta II",1,"vpn"],""]
[["continent : Americas","city : Atlanta II",1,"gre"],"104.129.204.32"]
[["continent : Americas","city : Atlanta II",1,"hostname"],""]
[["continent : Americas","city : Atlanta II",1,"latitude"],"34"]
[["continent : Americas","city : Atlanta II",1,"longitude"],"-84"]
[["continent : Americas","city : Atlanta II",1,"longitude"]]
[["continent : Americas","city : Atlanta II",1]]
[["continent : Americas","city : Atlanta II"]]
[["continent : Americas"]]

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](https://jqplay.org/s/LgguLhoXTA1 "link to jqplay -->"):

.["zscaler.net"] | tostream
| select(.[0][-1] == "range")

Result:

[["continent : EMEA","city : Amsterdam II",0,"range"],"165.225.240.0/23"]
[["continent : EMEA","city : Amsterdam II",1,"range"],"147.161.132.0/23"]
[["continent : EMEA","city : Brussels II",0,"range"],"147.161.156.0/23"]
[["continent : Americas","city : Atlanta II",0,"range"],"104.129.204.0/23"]
[["continent : Americas","city : Atlanta II",1,"range"],"136.226.2.0/23"]

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 with split(":"). Since this needs to be done for each element, you can iterate each element with .[0][0:2][]. Piping this into split(":") formats the output as desired.

[Filter](https://jqplay.org/s/Q_VRLVyIXJQ "link to jqplay -->"):

.["zscaler.net"] | tostream
| select(.[0][-1] == "range")
| [.[-1], .[0][0:2][]|split(":")[-1]]

Result:

["165.225.240.0/23"," EMEA"," Amsterdam II"]
["147.161.132.0/23"," EMEA"," Amsterdam II"]
["147.161.156.0/23"," EMEA"," Brussels II"]
["104.129.204.0/23"," Americas"," Atlanta II"]
["136.226.2.0/23"," Americas"," Atlanta II"]

All that's left is to format the output as CSV with join(",") as shown in the original solution above.

You can combine the needed fields like so \(.first) \(.second):

jq -r '.["zscaler.net"]["continent : EMEA"] | .[][]|"\(.range), \(.latitude), \(.longitude)"'

Output:

165.225.240.0/23, 52, 5
147.161.132.0/23, 52, 5
147.161.156.0/23, 50, 5