How to output key values from json to a table using jq?

Question

I have this json which I get from an API.

{
"result": {
    "key1": "val1",
    "key2": "val2",
    "key3": "val3"
 }
}

I have the following shell script which creates headers for table in a text file. I want to extract key values from the above result object and put in the same text file where keys should go under KEYS and values under VALUES in the table. I am new to jq and shell and struggling to achieve this.

echo "%table"
echo -e "KEYS\tVALUES" > outputfile.txt
KEYVALS=$(curl -uuser:password 
"http://localhost:8080/customapi")
# here I want to split the key values using jq and write to the outputfile.txt 
cat outputfile.txt

Outcome I am expecting is:

KEYS      VALUES
key1      val1
key2      val2
key3      val3

How can I achieve this?

Does this answer your question? [jq: print key and value for each entry in an object](https://stackoverflow.com/questions/34226370/jq-print-key-and-value-for-each-entry-in-an-object) — ggorlen, Nov 04 '20 at 19:53

score 3 · Answer 1 · answered Nov 04 '20 at 19:51

3

The key is to convert .result to an array of key/value pairs using to_entries, then outputing a set of strings (created using string interpolation) in raw mode.

% cat tmp.json
{
"result": {
    "key1": "val1",
    "key2": "val2",
    "key3": "val3"
 }
}
% jq -r '{"KEYS": "VALUES"} + .result | to_entries[] | "\(.key)\t\(.value)"' tmp.json
KEYS    VALUES
key1    val1
key2    val2
key3    val3

I added the header to the input before conversion to the key/value list.

answered Nov 04 '20 at 19:51

chepner

497,756
71
530
681

Thank you. I edited mine based on your answer as follows: echo "%table" curl -uuser:password "http://localhost:8080/customapi" | jq -r '{"KEYS": "VALUES"} + .result | to_entries[] | "\(.key)\t\(.value)"'> outputfile.txt cat outputfile.txt Output I got was just the `KEYS` column. Any thoughts? – BlueStar Nov 04 '20 at 20:10
1

In case .result already contains "KEYS: VALUES" it would be safer to use `["KEYS","VALUES"], (....) | @tsv` or similar. – peak Nov 04 '20 at 23:57
Oh, I forgot about `@tsv`. I'm not sure how you mean that is safer in case the object already has `KEYS: VALUES`, though; `@tsv` doesn't appear to do any deduplication in that case. – chepner Nov 05 '20 at 01:52
It’s the + that’s the problem. – peak Nov 05 '20 at 05:08
I still get duplicates with `jq -rn '["KEYS", "VALUES"], ({KEYS: "VALUES", "a": "B"} | to_entries[] | [.key, .value]) | @tsv'`. – chepner Nov 05 '20 at 12:52

score 0 · Answer 2 · answered Jun 17 '21 at 10:51

by adding the column call at the end the alignment will work for longer values as well ... Note the usage of the @ char as token separator ... of course if your data contains it this will not work ...

 aws cognito-idp list-user-pools --max-results 20 | \
 jq -r '.UserPools[]|to_entries[]|select (.key == "Name")|("\(.key):@\(.value)")'| column -t -s'@'

output

 Name:  corp_stg_user_pool
 Name:  corp_dev_user_pool

How to output key values from json to a table using jq?

2 Answers2