Question 110024 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – format jq output into a table

I want to fetch some data from below JSON code:

I’m able to get the output using below command but now I want to format it in such a way that my output will look like the expected output.

Command:

cat dump | jq -r '["name","IP","NAT","location","method"], 
                  (.objects[] | [.name, ."ipv4-address", ."nat-settings"."ipv4-address", ."nat-settings"."install-on", ."nat-settings".method]) 
                              | @csv' 
                              | sed -e 's/"//g'

After using @csv I got below output:

name,IP,NAT,location,method
H_103.109.135.25,103.109.135.25,1.1.1.1,All,static
H_103.109.135.250,103.109.135.250,,,

and whenever I use @tsv I get "jq: error: tsv is not a valid format"

Can any one suggest me how can I achieve below output:

Expected Output:

enter image description here

Raw JSON Code:

{
  "from" : 1,
  "to" : 2,
  "total" : 2,
  "objects" : [ {
    "uid" : "73b7036d-e8ec-47b7-99b5-19ca89eb5fd0",
    "name" : "H_103.109.135.25",
    "type" : "host",
    "domain" : {
      "uid" : "41e821a0-3720-11e3-aa6e-0800200c9fde",
      "name" : "SMC User",
      "domain-type" : "domain"
    },
    "ipv4-address" : "103.109.135.25",
    "interfaces" : [ ],
    "nat-settings" : {
      "auto-rule" : true,
      "ipv4-address" : "1.1.1.1",
      "ipv6-address" : "",
      "install-on" : "All",
      "method" : "static"
    },
    "comments" : "",
    "color" : "black",
    "icon" : "Objects/host",
    "tags" : [ ],
    "meta-info" : {
      "lock" : "unlocked",
      "validation-state" : "ok",
      "last-modify-time" : {
        "posix" : 1674820459413,
        "iso-8601" : "2023-01-27T17:24+0530"
      },
      "last-modifier" : "admin",
      "creation-time" : {
        "posix" : 1674818326777,
        "iso-8601" : "2023-01-27T16:48+0530"
      },
      "creator" : "admin"
    },
    "read-only" : false,
    "available-actions" : {
      "edit" : "true",
      "delete" : "true",
      "clone" : "true"
    }
  }, {
    "uid" : "7300c38a-a496-497a-b9e3-5701fa081393",
    "name" : "H_103.109.135.250",
    "type" : "host",
    "domain" : {
      "uid" : "41e821a0-3720-11e3-aa6e-0800200c9fde",
      "name" : "SMC User",
      "domain-type" : "domain"
    },
    "ipv4-address" : "103.109.135.250",
    "interfaces" : [ ],
    "nat-settings" : {
      "auto-rule" : false
    },
    "comments" : "",
    "color" : "black",
    "icon" : "Objects/host",
    "tags" : [ ],
    "meta-info" : {
      "lock" : "unlocked",
      "validation-state" : "ok",
      "last-modify-time" : {
        "posix" : 1674818341888,
        "iso-8601" : "2023-01-27T16:49+0530"
      },
      "last-modifier" : "admin",
      "creation-time" : {
        "posix" : 1674818341888,
        "iso-8601" : "2023-01-27T16:49+0530"
      },
      "creator" : "admin"
    },
    "read-only" : false,
    "available-actions" : {
      "edit" : "true",
      "delete" : "true",
      "clone" : "true"
    }
  } ]
}

Note:

It’s not mandatory that the output should be printed in table using jq only. "awk" or "sed" is also fine.

I have extracted data that required from the below raw json data:

Extracted data:

{
    "name": "H_103.109.135.25",
    "IP": "103.109.135.25",
    "NAT": "1.1.1.1",
    "location": "All",
    "method": "static"
  },
  {
    "name": "H_103.109.135.250",
    "IP": "103.109.135.250",
    "NAT": "NA",
    "location": "NA",
    "method": "NA"
  }

I now just need to format this data into table like below or somewhat similar:

| name              | IP              | NAT     | location   | method   |
|-------------------|-----------------|---------|------------|----------|
| H_103.109.135.25  | 103.109.135.25  | 1.1.1.1 | All        | static   |
| H_103.109.135.250 | 103.109.135.250 | NA      | NA         | NA       |
Tags:

3

Answers


  1. 0

    is handy for pretty-printing output.

    echo 'name,IP,NAT,location,method
H_103.109.135.25,103.109.135.25,1.1.1.1,All,static
H_103.109.135.250,103.109.135.250,,,' 
| mlr --c2p --barred put 'for (i,v in $*) {if (v == "") {$[i] = "NA"}}'

    --c2p is a shortcut for --icsv --opprint which reads CSV input and outputs pretty-printed tabular form.

    +-------------------+-----------------+---------+----------+--------+
| name              | IP              | NAT     | location | method |
+-------------------+-----------------+---------+----------+--------+
| H_103.109.135.25  | 103.109.135.25  | 1.1.1.1 | All      | static |
| H_103.109.135.250 | 103.109.135.250 | NA      | NA       | NA     |
+-------------------+-----------------+---------+----------+--------+

    The miller put verb takes an awk-like script.

    See https://miller.readthedocs.io/en/latest/

    A bit more functional style:

    mlr --c2p --barred put '$* = apply($*, func(k,v) {return {k: v == "" ? "NA" : v}})'

    I’d suggest removing quotes and adding "NA" inside jq, and then pipe the output to column

    jq -r '
  [
    ["name","IP","NAT","location","method"],
    ( .objects[]
    | {"nat-settings": {"ipv4-address": "NA", "install-on": "NA", method: "NA"}} * .
    | [.name, ."ipv4-address"] + (."nat-settings" | [."ipv4-address", ."install-on", .method])
    )
  ][] | join(",")
' dump | column -s, -t

    That assumes that the "nat-settings" object is missing the "ipv4-address", etc, keys.

    Login or Signup to reply.
  2. 0

    There is jbtl which may produce what you’re looking for. If you have this in output.jq for example:

    .objects
| map(
    { name, IP: ."ipv4-address" } +
    (."nat-settings" | {
      NAT: (."ipv4-address" // "NA"), 
      location: (."install-on" // "NA"), 
      method: (.method // "NA")
    })
  )

    then passing the data through this filter and piping it into jtbl with the -m option, like this:

    cat dump | jq -f output.jq | jtbl -m

    gives this

    | name              | IP              | NAT     | location   | method   |
|-------------------|-----------------|---------|------------|----------|
| H_103.109.135.25  | 103.109.135.25  | 1.1.1.1 | All        | static   |
| H_103.109.135.250 | 103.109.135.250 | NA      | NA         | NA       |
    Login or Signup to reply.
  3. 0

    I would recommend using jq’s @tsv and the very standard tool, column, e.g. as follows:

    < dump jq -r '
  ["name","IP","NAT","location","method"], 
  (.objects[] | [.name, ."ipv4-address", ."nat-settings"."ipv4-address", ."nat-settings"."install-on", ."nat-settings".method]) 
  | @tsv' | column -t
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search