format jq output into a table-CodePudding

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:

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"
    }
  } ]
}

CodePudding user response：

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       |

CodePudding user response：

miller 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}})'