4

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)

3 Answers 3

9
... | 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.

1
  • Thanks for the excellent answer and explanation. I was initially having problems figuring out how to access the fields with dots in the name, I see now how you have to quote them.
    – rb1980
    Jul 22, 2017 at 18:01
3

There's probably a "rule of the universe" that says something like "as soon as you post a question to SE, the answer will come to you"... Shortly after posting this question, I persisted with a try/google/rtfm loop, and eventually came up with this that worked for me:

knife search node "name:foo*" -a name -a cpu.total -a memory.total -Fj | jq -r '.rows[] | map(.[]) | @csv '

Which returns:

"foo-01",12,"16267000kB"
"foo-02",12,"16267000kB"

This imports cleanly right into google sheets. I can probably tweak that even further with some of the examples Michael gave. I kinda prefer this, because I don't have to specify the field names explicitly. Looking forward to playing more with JQ, what a great tool!

1
  • The trouble here is that I don't think you're guaranteed to get the fields in the right (perhaps even same) order - that's why I listed out the names. I'm not entirely certain about that though, and it might work in this case anyway. Jul 22, 2017 at 20:36
2

The jq expression

.rows[] | map(.[]) | @csv

in your own answer is neat, but taking Michael's concern about the ordering of the keys into account, you may want to instead explicitly extract the values that you need:

.rows[][] | [ .name, ."cpu.total", ."memory.total" ] | @csv

or,

.rows[] | map( .name, ."cpu.total", ."memory.total" ) | @csv

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .