Search code examples
jqreformatting

How can I reformat multiple levels of JSON using JQ?


I suspect I've set myself an impossible task, and have made some small progress looking at similar questions posted on this forum, but this one has me stumped.

So here's my source JSON.

{
  "totalCount": "3",
  "imdata": [
    {
      "fvTenant": {
        "attributes": {
          "dn": "uni/tn-Tenant10",
          "name": "Tenant10"
        },
        "children": [
          {
            "fvAp": {
              "attributes": {
                "name": "2Tier_AP"
              },
              "children": [
                {
                  "fvAEPg": {
                    "attributes": {
                      "name": "WebServers_EPG"
                    },
                    "children": [
                      {
                        "fvRsCons": {
                          "attributes": {
                            "tnVzBrCPName": "AppServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-common/brc-AppServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                },
                {
                  "fvAEPg": {
                    "attributes": {
                      "name": "AppServers_EPG"
                    },
                    "children": [
                      {
                        "fvRsProv": {
                          "attributes": {
                            "tnVzBrCPName": "AppServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-common/brc-AppServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                }
              ]
            }
          }
        ]
      }
    },
    {
      "fvTenant": {
        "attributes": {
          "dn": "uni/tn-Tenant18",
          "name": "Tenant18"
        },
        "children": [
          {
            "fvAp": {
              "attributes": {
                "name": "3Tier_AP"
              },
              "children": [
                {
                  "fvAEPg": {
                    "attributes": {
                      "name": "DBServers_EPG"
                    },
                    "children": [
                      {
                        "fvRsCustQosPol": {
                          "attributes": {}
                        }
                      },
                      {
                        "fvRsBd": {
                          "attributes": {},
                          "children": [
                            {
                              "fvSubnetBDDefCont": {
                                "attributes": {
                                  "bddefDn": "uni/bd-[uni/tn-Tenant18/BD-App_BD]-isSvc-no",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                },
                {
                  "fvAEPg": {
                    "attributes": {
                      "name": "AppServers_EPG"
                    },
                    "children": [
                      {
                        "fvRsCons": {
                          "attributes": {
                            "tnVzBrCPName": "MgmtServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-Tenant18/brc-MgmtServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      },
                      {
                        "fvRsCons": {
                          "attributes": {
                            "tnVzBrCPName": "DBServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-common/brc-DBServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      },
                      {
                        "fvRsProv": {
                          "attributes": {
                            "tnVzBrCPName": "AppServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-Tenant18/brc-AppServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      },
                      {
                        "fvRsProv": {
                          "attributes": {
                            "tnVzBrCPName": "MgmtServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-Tenant18/brc-MgmtServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                },
                {
                  "fvAEPg": {
                    "attributes": {
                      "name": "WebServers_EPG"
                    },
                    "children": [
                      {
                        "fvRsCons": {
                          "attributes": {
                            "tnVzBrCPName": "MgmtServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-Tenant18/brc-MgmtServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      },
                      {
                        "fvRsCons": {
                          "attributes": {
                            "tnVzBrCPName": "AppServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-Tenant18/brc-AppServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      },
                      {
                        "fvRsProv": {
                          "attributes": {
                            "tnVzBrCPName": "MgmtServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-Tenant18/brc-MgmtServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                }
              ]
            }
          }
        ]
      }
    },
    {
      "fvTenant": {
        "attributes": {
          "dn": "uni/tn-Tenant17",
          "name": "Tenant17"
        },
        "children": [
          {
            "fvAp": {
              "attributes": {
                "name": "2Tier_AP"
              },
              "children": [
                {
                  "fvAEPg": {
                    "attributes": {
                      "name": "WebServers_EPG"
                    },
                    "children": [
                      {
                        "fvRsDomAtt": {
                          "attributes": {
                            "tDn": "uni/phys-T17:MappedVLANs_PhysDom"
                          }
                        }
                      },
                      {
                        "fvRsCustQosPol": {
                          "attributes": {}
                        }
                      },
                      {
                        "fvRsBd": {
                          "attributes": {},
                          "children": [
                            {
                              "fvSubnetBDDefCont": {
                                "attributes": {
                                  "bddefDn": "uni/bd-[uni/tn-Tenant17/BD-Web_BD]-isSvc-no",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                },
                {
                  "fvAEPg": {
                    "attributes": {
                      "name": "AppServers_EPG"
                    },
                    "children": [
                      {
                        "fvRsCons": {
                          "attributes": {
                            "tnVzBrCPName": "Any.IP_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-common/brc-Any.IP_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                }
              ]
            }
          },
          {
            "fvAp": {
              "attributes": {
                "name": "3Tier_AP"
              },
              "children": [
                {
                  "fvAEPg": {
                    "attributes": {
                      "name": "DBServers_EPG"
                    },
                    "children": [
                      {
                        "fvRsProv": {
                          "attributes": {
                            "tnVzBrCPName": "DBServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-common/brc-DBServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                },
                {
                  "fvAEPg": {
                    "attributes": {
                      "name": "AppServers_EPG"
                    },
                    "children": [
                      {
                        "fvRsCons": {
                          "attributes": {
                            "tnVzBrCPName": "DBServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-common/brc-DBServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      },
                      {
                        "fvRsCons": {
                          "attributes": {
                            "tnVzBrCPName": "MgmtServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-Tenant17/brc-MgmtServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      },
                      {
                        "fvRsProv": {
                          "attributes": {
                            "tnVzBrCPName": "AppServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-Tenant17/brc-AppServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      },
                      {
                        "fvRsProv": {
                          "attributes": {
                            "tnVzBrCPName": "MgmtServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-Tenant17/brc-MgmtServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                },
                {
                  "fvAEPg": {
                    "attributes": {
                      "name": "WebServers_EPG"
                    },
                    "children": [
                      {
                        "fvRsCons": {
                          "attributes": {
                            "tnVzBrCPName": "MgmtServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-Tenant17/brc-MgmtServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      },
                      {
                        "fvRsCons": {
                          "attributes": {
                            "tnVzBrCPName": "AppServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-Tenant17/brc-AppServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      },
                      {
                        "fvRsProv": {
                          "attributes": {
                            "tnVzBrCPName": "MgmtServices_Ct"
                          },
                          "children": [
                            {
                              "fvCollectionCont": {
                                "attributes": {
                                  "collectionDn": "uni/tn-Tenant17/brc-MgmtServices_Ct",
                                  "name": "",
                                  "nameAlias": ""
                                }
                              }
                            }
                          ]
                        }
                      }
                    ]
                  }
                }
              ]
            }
          }
        ]
      }
    }
  ]
}

I need to turn this into something more readable, ideally, I'd like it to look like this (the following is valid JSON, but I'm not fussy if it is not strictly accurate - the general format is the important thing)

[
   {
      "fvTenant": "Tenant10",
      "fvAp": [
         {
            "2Tier_AP": {
               "fvAEPg": [
                  {
                     "WebServers_EPG": {
                        "fvRsCons": [
                           "AppServices_Ct"
                        ],
                        "fvRsProv": []
                     },
                     "AppServers_EPG": {
                        "fvRsCons": [],
                        "fvRsProv": [
                           "AppServices_Ct"
                        ]
                     }
                  }
               ]
            }
         }
      ]
   },
   {
      "fvTenant": "Tenant18",
      "fvAp": [
         {
            "3Tier_AP": {
               "fvAEPg": [
                  {
                     "DBServers_EPG": {
                        "fvRsCons": [],
                        "fvRsProv": []
                     },
                     "AppServers_EPG": {
                        "fvRsCons": [
                           "MgmtServices_Ct",
                           "DBServices_Ct"
                        ],
                        "fvRsProv": [
                           "AppServices_Ct",
                           "MgmtServices_Ct"
                        ]
                     },
                     "WebServers_EPG": {
                        "fvRsCons": [
                           "MgmtServices_Ct",
                           "AppServices_Ct"
                        ],
                        "fvRsProv": [
                           "MgmtServices_Ct"
                        ]
                     }
                  }
               ]
            }
         }
      ]
   },
   {
      "fvTenant": "Tenant17",
      "fvAp": [
         {
            "2Tier_AP": {
               "fvAEPg": [
                  {
                     "WebServers_EPG": {
                        "fvRsCons": [],
                        "fvRsProv": []
                     },
                     "AppServers_EPG": {
                        "fvRsCons": [
                           "Any.IP_Ct"
                        ],
                        "fvRsProv": []
                     }
                  }
               ]
            }
         },
         {
            "3Tier_AP": {
               "fvAEPg": [
                  {
                     "DBServers_EPG": {
                        "fvRsCons": [
                           "DBServices_Ct",
                           "MgmtServices_Ct"
                        ],
                        "fvRsProv": [
                           "AppServices_Ct",
                           "MgmtServices_Ct"
                        ]
                     },
                     "AppServers_EPG": {
                        "fvRsCons": [
                           "DBServices_Ct",
                           "MgmtServices_Ct"
                        ],
                        "fvRsProv": [
                           "AppServices_Ct",
                           "MgmtServices_Ct"
                        ]
                     },
                     "WebServers_EPG": {
                        "fvRsCons": [
                           "MgmtServices_Ct",
                           "AppServices_Ct"
                        ],
                        "fvRsProv": [
                           "MgmtServices_Ct"
                        ]
                     }
                  }
               ]
            }
         }
      ]
   }
]

I've managed to separate out the "fvTenant" key and value, and get the "fvAp" into an array using jqplay - here's my attempt https://jqplay.org/s/tNIPrGaEI96

jq '.imdata[].fvTenant | {fvTenant: .attributes.name, fvAp: [.children[].fvAp]} '

gives me the following:

{"fvTenant":"Tenant10","fvAp":[{"attributes":{"name":"2Tier_AP"},"children":[{"fvAEPg":{"attributes":{"name":"WebServers_EPG"},"children":[{"fvRsCons":{"attributes":{"tnVzBrCPName":"AppServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-common/brc-AppServices_Ct","name":"","nameAlias":""}}}]}}]}},{"fvAEPg":{"attributes":{"name":"AppServers_EPG"},"children":[{"fvRsProv":{"attributes":{"tnVzBrCPName":"AppServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-common/brc-AppServices_Ct","name":"","nameAlias":""}}}]}}]}}]}]}
{"fvTenant":"Tenant18","fvAp":[{"attributes":{"name":"3Tier_AP"},"children":[{"fvAEPg":{"attributes":{"name":"DBServers_EPG"},"children":[{"fvRsCustQosPol":{"attributes":{}}},{"fvRsBd":{"attributes":{},"children":[{"fvSubnetBDDefCont":{"attributes":{"bddefDn":"uni/bd-[uni/tn-Tenant18/BD-App_BD]-isSvc-no","name":"","nameAlias":""}}}]}}]}},{"fvAEPg":{"attributes":{"name":"AppServers_EPG"},"children":[{"fvRsCons":{"attributes":{"tnVzBrCPName":"MgmtServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-Tenant18/brc-MgmtServices_Ct","name":"","nameAlias":""}}}]}},{"fvRsCons":{"attributes":{"tnVzBrCPName":"DBServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-common/brc-DBServices_Ct","name":"","nameAlias":""}}}]}},{"fvRsProv":{"attributes":{"tnVzBrCPName":"AppServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-Tenant18/brc-AppServices_Ct","name":"","nameAlias":""}}}]}},{"fvRsProv":{"attributes":{"tnVzBrCPName":"MgmtServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-Tenant18/brc-MgmtServices_Ct","name":"","nameAlias":""}}}]}}]}},{"fvAEPg":{"attributes":{"name":"WebServers_EPG"},"children":[{"fvRsCons":{"attributes":{"tnVzBrCPName":"MgmtServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-Tenant18/brc-MgmtServices_Ct","name":"","nameAlias":""}}}]}},{"fvRsCons":{"attributes":{"tnVzBrCPName":"AppServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-Tenant18/brc-AppServices_Ct","name":"","nameAlias":""}}}]}},{"fvRsProv":{"attributes":{"tnVzBrCPName":"MgmtServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-Tenant18/brc-MgmtServices_Ct","name":"","nameAlias":""}}}]}}]}}]}]}
{"fvTenant":"Tenant17","fvAp":[{"attributes":{"name":"2Tier_AP"},"children":[{"fvAEPg":{"attributes":{"name":"WebServers_EPG"},"children":[{"fvRsDomAtt":{"attributes":{"tDn":"uni/phys-T17:MappedVLANs_PhysDom"}}},{"fvRsCustQosPol":{"attributes":{}}},{"fvRsBd":{"attributes":{},"children":[{"fvSubnetBDDefCont":{"attributes":{"bddefDn":"uni/bd-[uni/tn-Tenant17/BD-Web_BD]-isSvc-no","name":"","nameAlias":""}}}]}}]}},{"fvAEPg":{"attributes":{"name":"AppServers_EPG"},"children":[{"fvRsCons":{"attributes":{"tnVzBrCPName":"Any.IP_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-common/brc-Any.IP_Ct","name":"","nameAlias":""}}}]}}]}}]},{"attributes":{"name":"3Tier_AP"},"children":[{"fvAEPg":{"attributes":{"name":"DBServers_EPG"},"children":[{"fvRsProv":{"attributes":{"tnVzBrCPName":"DBServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-common/brc-DBServices_Ct","name":"","nameAlias":""}}}]}}]}},{"fvAEPg":{"attributes":{"name":"AppServers_EPG"},"children":[{"fvRsCons":{"attributes":{"tnVzBrCPName":"DBServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-common/brc-DBServices_Ct","name":"","nameAlias":""}}}]}},{"fvRsCons":{"attributes":{"tnVzBrCPName":"MgmtServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-Tenant17/brc-MgmtServices_Ct","name":"","nameAlias":""}}}]}},{"fvRsProv":{"attributes":{"tnVzBrCPName":"AppServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-Tenant17/brc-AppServices_Ct","name":"","nameAlias":""}}}]}},{"fvRsProv":{"attributes":{"tnVzBrCPName":"MgmtServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-Tenant17/brc-MgmtServices_Ct","name":"","nameAlias":""}}}]}}]}},{"fvAEPg":{"attributes":{"name":"WebServers_EPG"},"children":[{"fvRsCons":{"attributes":{"tnVzBrCPName":"MgmtServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-Tenant17/brc-MgmtServices_Ct","name":"","nameAlias":""}}}]}},{"fvRsCons":{"attributes":{"tnVzBrCPName":"AppServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-Tenant17/brc-AppServices_Ct","name":"","nameAlias":""}}}]}},{"fvRsProv":{"attributes":{"tnVzBrCPName":"MgmtServices_Ct"},"children":[{"fvCollectionCont":{"attributes":{"collectionDn":"uni/tn-Tenant17/brc-MgmtServices_Ct","name":"","nameAlias":""}}}]}}]}}]}]}

It seems I need to process the fvAp array in a similar way, but I've not had any success isolating the array to process it. Open to any suggestions.


Solution

  • Finally got it figured, thanks to @knittl the suggestions and example using map. Using the following, I was able to get very close to my target output. For those who want to experiment, you can see the result on https://jqplay.org/s/HpUiVJAd00u

    .imdata
    | map(.fvTenant | {
      fvTenant: .attributes.name,
      fvAp: (.children
        | map(.fvAp |{
          fvAp: .attributes.name,
          fvAEPg: (.children | map(.fvAEPg |{
            fvAEPg: .attributes.name,
            fvRsCons: ([.children[].fvRsCons.attributes.tnVzBrCPName | values] ),
            fvRsProv: ([.children[].fvRsProv.attributes.tnVzBrCPName | values] )
          }))
        })
      )
    })
    

    I guess my next challenge is to get it working with the alternate method using collections that was suggested. If I manage it, I'll update this