JSON Data – How to Fetch Partial Data from JSON Format Using jq

jqjson

I have a sample JSON format data

[
{
    "info":{
        "disk_num":"2",
        "hostname":"hostA",
        "net":[
            "eth0",
            "eth1"
        ],
        "software":{
            "version":"1.0.0",
            "build_date":"2021-10-08",
            "commit":"bfdaee"
        },
        "kernel":"5.0.0"
    },
    "ip":"10.240.82.192",
    "status":"PASS"
},
{
    "info":{
        "disk_num":"2",
        "hostname":"hostA",
        "net":[
            "eth0",
            "eth1"
        ],
        "software":{
            "version":"1.0.0",
            "build_date":"2021-10-08",
            "commit":"bfdaee"
        },
        "kernel":"5.0.3"
    },
    "ip":"10.240.82.193",
    "status":"PASS"
},
{
    "info":{
        "disk_num":"5",
        "hostname":"hostB",
        "net":[
            "eth0",
            "eth1"
        ],
        "software":{
            "version":"1.0.2",
            "build_date":"2021-10-11",
            "commit":"935678"
        },
        "kernel":"5.0.1"
    },
    "ip":"10.240.82.194",
    "status":"PASS"
}
]

I want to get partial data and construct another json FORMAT Like

[
    {
      "IP": "10.240.82.192",
      "STATUS": "PASS",
      "SOFTWARE": "1.0.0",
      "KERNEL": "5.0.0"
    },
    {
      "IP": "10.240.82.193",
      "STATUS": "PASS",
      "SOFTWARE": "1.0.0",
      "KERNEL": "5.0.3"
    },
    {
      "IP": "10.240.82.194",
      "STATUS": "PASS",
      "SOFTWARE": "1.0.2",
      "KERNEL": "5.0.1"
    }
]

I have tried this command (I have saved sample data to json.data)

cat json.data|jq -r '{IP:.[].ip,STATUS:.[].status,SOFTWARE:.[].info.software.version,KERNEL:.[].info.kernel}'

It didn't work , it will output as the followings:

{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.192",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.193",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.0",
  "KERNEL": "5.0.1"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.0"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.3"
}
{
  "IP": "10.240.82.194",
  "STATUS": "PASS",
  "SOFTWARE": "1.0.2",
  "KERNEL": "5.0.1"
}

I wonder to know the correct cli command to get what I want

Thanks very much

Best Answer

Your command iterates across each of the array elements, for each of the four elements you've specified, so instead of getting three objects, you're getting 3⁴ = 81 objects.

To iterate across each object you can use a map() function, like this:

jq 'map({IP:.ip, STATUS:.status, SOFTWARE:.info.software.version, KERNEL:.info.kernel})' json.data

[
  {
    "IP": "10.240.82.192",
    "STATUS": "PASS",
    "SOFTWARE": "1.0.0",
    "KERNEL": "5.0.0"
  },
  {
    "IP": "10.240.82.193",
    "STATUS": "PASS",
    "SOFTWARE": "1.0.0",
    "KERNEL": "5.0.3"
  },
  {
    "IP": "10.240.82.194",
    "STATUS": "PASS",
    "SOFTWARE": "1.0.2",
    "KERNEL": "5.0.1"
  }
]

Related Solutions

jq – Nested Reduce and Grouping of JSON Data

Thank you for this question. It was enjoyable to work with.

Here's a variation without any reduce:

group_by(.item) |
map({
    item: first.item,
    attributes: (
        group_by(.attributes[].type) |
        map({
            type: first.attributes[].type,
            colour: ( map(.attributes[].colour[]) )
        })
    )
})

I start by grouping the original elements by their item key. This gives us an array that is grouped by item. Each element in that array consists of a sub-array containing all the original objects with the same item.

The first map() brings these groups together by creating one object for each group. The object has item and attributes keys, and the value for the item key is taken arbitrarily from the first element of the group (they are all the same).

A further group_by() and map() creates the value for the attributes key. This time grouping over the type key down in the attributes array of the original objects and, for each created group, collecting the type and colour values from the original objects.

You can also do it with reduce, like so:

group_by(.item) |
map(reduce .[] as $a ({}; .item = $a.item | .attributes += $a.attributes)) |
map(.attributes |= (
    group_by(.type) |
    map(reduce .[] as $a ({}; .type = $a.type | .colour += $a.colour))
))

This is more or less group_by()+map(reduce) to create the outer structure of the result, i.e., the grouping of the data into parts based on item and organising that outer structure. The values for the attributes array are just passed along.

This pattern (group_by()+map(reduce)) is then repeated for each group's attributes array for grouping and organising it based on the type values.

Performance-wise, the two solutions above are on par with each other for small inputs, with the second variant (using reduce) being a bit faster for inputs less than 150 times the size in the question (about 64 KB). For these size inputs, a run takes about 70 ms on my system, i.e. the run time is negligible.

When the input grows further, the reduced-based solution seems to require exponentially more time. For an input of size 4 MB, the first code uses around 800 ms, while the second clocks in at 25 seconds for a single run.

If jq becomes difficult to run for larger inputs (a linear extrapolation suggest it should take me about 54 hours to run a 1 GB data set), you may want to look into processing the data in some way (potentially in a database or at least work with the data in some other format which is not JSON).

For example, the given data could be transformed into CSV:

item,type,colour
item1,itemtype,blue
item1,itemtype,grey
item2,itemtype,blue
item2,itemtype2,orange
item2,itemtype2,blue
item3,itemtype,blue

... or into an equivalent database table, and then worked on from there.

For example, with Miller,

$ mlr --csv nest --ivar ';' -f colour file.csv
item,type,colour
item1,itemtype,blue;grey
item2,itemtype,blue
item2,itemtype2,orange;blue
item3,itemtype,blue

How to Format a JSON String as a Table Using jq

Maybe:

$ jq -c '.results[]|map(.key=.field)|from_entries|del(."@ptr")' file.json |
   mlr --ijson --opprint --barred cat
+-------------------------+-------------+-------------------+----------------+----------------+
| @timestamp              | CpuReserved | CpuUtilized       | MemoryReserved | MemoryUtilized |
+-------------------------+-------------+-------------------+----------------+----------------+
| 2023-07-03 08:28:00.000 | 8192.0      | 4056.412942708333 | 61440          | 4311           |
| 2023-07-03 08:28:00.000 | 8192.0      | 4056.412942708333 | 61440          | 4311           |
+-------------------------+-------------+-------------------+----------------+----------------+

Without --barred, that looks like:

@̲t̲i̲m̲e̲s̲t̲a̲m̲p̲              C̲p̲u̲R̲e̲s̲e̲r̲v̲e̲d̲ C̲p̲u̲U̲t̲i̲l̲i̲z̲e̲d̲       M̲e̲m̲o̲r̲y̲R̲e̲s̲e̲r̲v̲e̲d̲ M̲e̲m̲o̲r̲y̲U̲t̲i̲l̲i̲z̲e̲d̲
2023-07-03 08:28:00.000 8192.0      4056.412942708333 61440          4311
2023-07-03 08:28:00.000 8192.0      4056.412942708333 61440          4311

You could also pipe the output of that jq command to vd -f json (VisiData) instead of mlr (Miller) to get an interactive table viewer.

Those use jq to extract the information and mlr only to format the table. There is some overlap between the feature set of jq and that of mlr. For instance, you could also remove the @ptr column with mlr's cut:

jq -c '.results[]|map(.key=.field)|from_entries' file.json |
  mlr --ijson --opprint cut -xf @ptr

That jq command, broken down and commented:

jq -c '
  .results[] | # iterate over the elements of the .results array 
               # (which are also arrays)

  map(.key=.field) | # for each of those arrays, transform the
                     # elements (which are objects) by adding a 
                     # field of key "key" with same value as that
                     # with "field" key in each, as that's what
                     # from_entries needs

  from_entries | # transforms those [{"key":"foo","value":"bar"}]
                 # (the "field" field is ignored) to {"foo":"bar"}

  del(."@ptr") # deletes the field with key "@ptr" from those
               # objects' file.json

The result is not JSON, but several JSONs concatenated together, but both jq and mlr support that. With -c (compact), that's NDJSON (newline-delimited JSON) where we have one JSON per line, also supported by vd. To get proper JSON, we'd need:

jq -c '.results|map(map(.key=.field)|from_entries|del(."@ptr"))' file.json

Where we use map on the .results array so it results in another JSON array instead of iterating over the elements. So the end result is one large arrays. That's also supported by jq (obviously as that's proper JSON), mlr and vd, is a bit longer to type and means those tools need to read up to the closing ] at the very end before they have anything to chew on. In practice, I've not checked whether that made any difference in terms of performance though.

Best Answer

Related Solutions

jq – Nested Reduce and Grouping of JSON Data

How to Format a JSON String as a Table Using jq

Related Question