jsonlinuxubuntujq

jq referencing an index/key with its inner values

I have a JSON similar to the following:

File: my_data.json

{
  "hosts": {
    "1.2.3.4": { "name": "name4", "env": "test", "options": "" },
    "1.2.3.5": { "name": "name5", "env": "prod", "options": "opt1,opt2" },
    "1.2.3.6": { "name": "name6", "env": "qa", "options": "" },
    "1.2.3.7": { "name": "name7", "env": "staging", "options": "opt3,opt4" },
    "1.2.3.8": { "name": "name8", "env": "dev", "options": "opt5" },
    "1.2.3.9": { "name": "name9", "env": "prod", "options": "opt6,opt7" }
  }
}

I want to extract a CSV file out of this, I have tried several options I found by Googling, but none work. My objective is to get something like the following:

"1.2.3.4","name4","test",""
"1.2.3.5","name5","prod","opt1,opt2"
"1.2.3.6","name6","dev",""
"1.2.3.7","name7","staging","opt3,opt4"
"1.2.3.8","name8","dev","opt5"
"1.2.3.9","name9","prod","opt6,opt7"

I got the following, but I can't address the key and I don't find a way to get it.

jq --raw-output '.hosts[] | [.name, .env, .options] | @csv' my_data.json

This is my result, but as seen is not complete. How do I add the key to every object?

"name4","test",""
"name5","prod","opt1,opt2"
"name6","qa",""
"name7","staging","opt3,opt4"
"name8","dev","opt5"
"name9","prod","opt6,opt7"

I made this:

jq --raw-output '.hosts | keys_unsorted[] | [.] | @csv' my_data.json

But that only gets the keys, and I have not found a way to address the object by using the key.

"1.2.3.4"
"1.2.3.5"
"1.2.3.6"
"1.2.3.7"
"1.2.3.8"
"1.2.3.9"

I could try to join both tables, but This could lead to errors and I know there should be a way to do it in jq, I just can't find the way.

I did several google searches and I got one promising, but it does not work:

jq --raw-output '.hosts | keys_unsorted[] as $k | [$k, \(.[$k] | .name), \(.[$k] | .env), \(.[$k] | .options)] | @csv' my_data.json

This ones returns a compile errors:

jq: error: syntax error, unexpected INVALID_CHARACTER (Unix shell quoting issues?) at <top-level>, line 1:
.hosts | keys_unsorted[] as $k | [$k, \(.[$k] | .name), \(.[$k] | .env), \(.[$k] | .options)] | @csv
jq: error: syntax error, unexpected ')', expecting $end (Unix shell quoting issues?) at <top-level>, line 1:
.hosts | keys_unsorted[] as $k | [$k, \(.[$k] | .name), \(.[$k] | .env), \(.[$k] | .options)] | @csv
jq: 2 compile errors
Solution

  • Use keys_unsorted or to_entries to get access to the keys:

    .hosts | keys_unsorted[] as $key
| [$key, (.[$key] | .name, .env, .options)] | @csv

    Demo

    or

    .hosts | to_entries[]
| [.key, (.value | .name, .env, .options)] | @csv

    Demo

    "1.2.3.4","name4","test",""
"1.2.3.5","name5","prod","opt1,opt2"
"1.2.3.6","name6","qa",""
"1.2.3.7","name7","staging","opt3,opt4"
"1.2.3.8","name8","dev","opt5"
"1.2.3.9","name9","prod","opt6,opt7"