Reconstructing JSON with jq

Question

I have a JSON like this (sample.json):

{
  "sheet1": [
    {
      "hostname": "sv001",
      "role": "web",
      "ip1": "172.17.0.3"
    },
    {
      "hostname": "sv002",
      "role": "web",
      "ip1": "172.17.0.4"
    },
    {
      "hostname": "sv003",
      "role": "db",
      "ip1": "172.17.0.5",
      "ip2": "172.18.0.5"
    }
  ],
  "sheet2": [
    {
      "hostname": "sv004",
      "role": "web",
      "ip1": "172.17.0.6"
    },
    {
      "hostname": "sv005",
      "role": "db",
      "ip1": "172.17.0.7"
    },
    {
      "hostname": "vsv006",
      "role": "db",
      "ip1": "172.17.0.8"
    }
  ],
  "sheet3": []
}

I want to extract data like this:

sheet1

jq '(something command)' sample.json

{
    "web": {
        "hosts": [
            "172.17.0.3",
            "172.17.0.4"
        ]
    },
    "db": {
        "hosts": [
            "172.17.0.5"
        ]
    }
}

Is it possible to perform the reconstruction with jq map? (I will reuse the result for ansible inventory.)

peak · Accepted Answer · 2019-09-22T02:16:35.910

1

Here's a short, straight-forward and efficient solution -- efficient in part because it avoids group_by by courtesy of the following generic helper function:

def add_by(f;g): reduce .[] as $x ({}; .[$x|f] += [$x|g]);

.sheet1
| add_by(.role; .ip1) 
| map_values( {hosts: .} )

Output

This produces the required output:

{
 "web": {
    "hosts": [
      "172.17.0.3",
      "172.17.0.4"
    ]
  },
  "db": {
    "hosts": [
      "172.17.0.5"
    ]
  }
}

edited Sep 22 '19 at 02:16

answered Sep 21 '19 at 20:18

peak

105,803
17
152
177

But, what about `ip2`? Am I missing something? – oguz ismail Sep 21 '19 at 20:50
1

@OguzIsmail - The requirements make no mention of .ip2, and the question and indicated output can be understood in terms of .ip1 only. – peak Sep 21 '19 at 22:50
This command was what I looking for. I needed only ip1 from sheetX. (ansible uses it.) I appreciate your reply. – asby Sep 22 '19 at 08:13

score 0 · Answer 2 · answered Sep 21 '19 at 18:03

If the goal is to regroup the ips by their roles within each sheet you could do this:

map_values(
    reduce group_by(.role)[] as $g ({};
        .[$g[0].role].hosts = [$g[] | del(.hostname, .role)[]]
    )
)

Which produces something like this:

{
  "sheet1": {
    "db": {
      "hosts": [
        "172.17.0.5",
        "172.18.0.5"
      ]
    },
    "web": {
      "hosts": [
        "172.17.0.3",
        "172.17.0.4"
      ]
    }
  },
  "sheet2": {
    "db": {
      "hosts": [
        "172.17.0.7",
        "172.17.0.8"
      ]
    },
    "web": {
      "hosts": [
        "172.17.0.6"
      ]
    }
  },
  "sheet3": {}
}

https://jqplay.org/s/3VpRc5l4_m

If you want to flatten all to a single object keeping only unique ips, you can keep everything mostly the same, you'll just need to flatten the inputs prior to grouping and remove the map_values/1 call.

$ jq -n '
reduce ([inputs[][]] | group_by(.role)[]) as $g ({};
    .[$g[0].role].hosts = ([$g[] | del(.hostname, .role)[]] | unique)
)
'

{
  "db": {
    "hosts": [
      "172.17.0.5",
      "172.17.0.7",
      "172.17.0.8",
      "172.18.0.5"
    ]
  },
  "web": {
    "hosts": [
      "172.17.0.3",
      "172.17.0.4",
      "172.17.0.6"
    ]
  }
}

https://jqplay.org/s/ZGj1wC8hU3

Thank you very much! I will be able to use these json data for ansible dyanamic inventory. — asby, Sep 22 '19 at 08:13

Reconstructing JSON with jq

sheet1

2 Answers2

Output