Use JQ to generate CSV from knife search

chefcsvjq

JQ looks like a great tool, but I'm struggling with it. Here is what I am trying to do:
Extract just the values from this chef knife search and generate a CSV.

given this command and output:

knife search node "name:foo*" -a name -a cpu.total -a memory.total -Fj

{
  "results": 2,
  "rows": [
    {
      "foo-01": {
        "name": "foo-01",
        "cpu.total": 12,
        "memory.total": "16267368kB"
      }
    },
    {
      "foo-02": {
        "name": "foo-02",
        "cpu.total": 12,
        "memory.total": "16264296kB"
      }
    }
  ]
}

I would like to get the values extracted to CSV like this:

foo-01,12,16267368kB
foo-02,12,16264296kB

(I can deal with the quotes)

Best Answer

... | jq -r '.rows[] | .[] | [.name, .["cpu.total"], .["memory.total"]] | map(tostring) | join(",")'

This:

  1. Expands the array in .rows into the output stream (.rows.[]).
  2. Pipes that stream into the next step (|).
  3. Expands the object it's given into the (in this case) single value it contained (.[]).
  4. Creates an array with the results of .name, .["cpu.total"], and .["memory.total"] each evaluated on that object (.[ .name, ... ]).
  5. Converts all the values of that array into strings (map(tostring)).
  6. Joins the elements of each array with a comma (join(",")).

jq -r outputs raw data, rather than quoting and escaping it. The output is then:

foo-01,12,16267368kB
foo-02,12,16264296kB

as you wanted. Depending on your CSV parser & the real data, you might need extra quoting around the strings, which you can add in, or use @csv in place of the last two steps.

... | jq -r '.rows[] | .[] | [.name, .["cpu.total"], .["memory.total"]] | @csv'

We could skip the map by converting only the one value inside, which takes some extra brackets:

... | jq -r '.rows[]|.[]|[.name, (.["cpu.total"] | tostring), .["memory.total"]] | join(",")'

And probably the ugliest alternative:

... | jq -r '.rows[]|to_entries|.[]|.key + "," + (.value["cpu.total"] | tostring) + "," + .value["memory.total"]'

In this case, we don't rely on the .name field, and build up the whole string manually. If you need a highly customised format, this is the most flexible option.

Related Question