CODEWITHSUNDEEP
jsonjq
Renato
Renato

Reputation: 181

Query parent data (multi-level) based on a child value, on a json file, using jq

I have a ksh script that retrives (using curl) a json file similar to the one bellow:

{
  "Type1": {
    "dev": {
      "server": [
        {   "group": "APP1",    "name": "DAPP1002", "ip": "10.1.1.1" },
        {   "group": "APP2",    "name": "DAPP2001", "ip": "10.1.1.2" }
      ]
    },
    "qa": {
      "server": [
        {   "group": "APP1",    "name": "QAPP1002", "ip": "10.1.2.1" },
        {   "group": "APP2",    "name": "QAPP2001", "ip": "10.1.2.2" }
      ]
    },
    "prod": {
      "proxy": "type1.prod.proxy.mydomain.com",
      "server": [
        {   "group": "APP1",    "name": "PAPP1001", "ip": "10.1.3.1" },
        {   "group": "APP1",    "name": "PAPP1002", "ip": "10.1.3.2" },
        {   "group": "APP2",    "name": "PAPP2001", "ip": "10.1.3.3" }
      ]
    }
  },

  "Type2": {
    "dev": {
      "server": [
        {   "group": "APP8",    "name": "DAPP8002", "ip": "10.2.1.1" },
        {   "group": "APP9",    "name": "DAPP9001", "ip": "10.2.1.2" }
      ]
    },
    "qa": {
      "server": [
        {   "group": "APP8",    "name": "QAPP8002", "ip": "10.2.2.1" },
        {   "group": "APP9",    "name": "QAPP9001", "ip": "10.2.2.2" }
      ]
    },
    "prod": {
      "proxy": "type2.prod.proxy.mydomain.com",
      "server": [
        {   "group": "APP8",    "name": "PAPP8001", "ip": "10.2.3.1" },
        {   "group": "APP9",    "name": "PAPP9001", "ip": "10.2.3.2" },
        {   "group": "APP9",    "name": "PAPP9002", "ip": "10.2.3.3" }
      ]
    }
  }
}

... based on a server name (field "name") I would have to collect the following info, to pass to a function:

"Type", "name", "ip", "proxy"

(Note that the "proxy" info is optional)

I am new to json, and I am trying to get this filtered with jq but so far, I am out of lucky. What I acomplished so far is the following jq query, when searching for "PAPP9001" :

jq '.[] | .[] | select(.server[].name=="PAPP9001") | .proxy as $proxy | .server[] | {proxy: $proxy, name: .name, ip: .ip} | select(.name=="PAPP9001")' curlreturn.json

which returns me:

{
  "proxy": "type2.prod.proxy.mydomain.com",
  "name": "PAPP9001",
  "ip": "10.2.3.2"
}

but:

  1. I could not get the "Type" info, at the top level
  2. Considering the number of pipes and the 2 selects, I doubt that this is the most efficient way.

Upvotes: 1

Views: 330

Answers (1)

peak
peak

Reputation: 116730

One way to retrieve the key names programmatically is using to_entries. For example, given your input, this jq filter:

to_entries[]
| .key as $type
| .value[]
| .proxy as $proxy
| .server[]
| select(.name == "PAPP9001")
| { Type: $type, name, ip, proxy: $proxy }

yields:

{
  "Type": "Type2",
  "name": "PAPP9001",
  "ip": "10.2.3.2",
  "proxy": "type2.prod.proxy.mydomain.com"
}

Variations

If, for example, you wanted these four fields as a CSV row, then you could replace the last line of the filter above with:

| [$type, .name, .ip, $proxy] | @csv

See the jq manual for how to use string interpolation.

Upvotes: 3

Related Questions