JQ - Denormalize nested object

Question

I've been trying to convert some JSON to csv and I have the following problem:

I have the following input json:

{"id": 100, "a": [{"t" : 1,"c" : 2 }, {"t": 2, "c" : 3 }] }
{"id": 200, "a": [{"t": 2, "c" : 3 }] }
{"id": 300, "a": [{"t": 1, "c" : 3 }] }

And I expect the following CSV output:

id,t1,t2
100,2,3
200,,3
300,3,

Unfortunately JQ doesn't output if one of select has no match. Example:

echo '{ "id": 100,  "a": [{"t" : 1,"c" : 2 }, {"t": 2, "c" : 3 }] }' | jq '{t1: (.a[] | select(.t==1)).c , t2: (.a[] | select(.t==2)).c }'

output:

{ "t1": 2,  "t2": 3   }

but if one of the objects select returns no match it doesn't return at all. Example:

echo '{ "id": 100,  "a": [{"t" : 1,"c" : 2 }] }' | jq '{t1: (.a[] | select(.t==1)).c , t2: (.a[] | select(.t==2)).c }'

Expected output:

{ "t1": 2,  "t2": null   }

Does anyone know how to achieve this with JQ?

EDIT:

Based on a comment made by @peak I found the solution that I was looking for.

jq -r '["id","t1","t2"],[.id, (.a[] | select(.t==1)).c//null, (.a[] | select(.t==2)).c//null ]|@csv'

The alternative operator does exactly what I was looking for. Alternative Operator

Please also describe the basic requirements so that others can more readily benefit. — peak
– peak, Commented Jan 20, 2020 at 17:32

peak · Accepted Answer · 2020-01-20 16:55:01Z

2

Here's a simple solution that does not assume anything about the ordering of the items in the .a array, and easily generalizes to arbitrarily many .t values:

# Convert an array of {t, c} to a dictionary:
def tod: map({(.t|tostring): .c}) | add;

["id", "t1", "t2"],   # header
(inputs 
 | (.a | tod) as $dict
 | [.id, (range(1;3) as $i | $dict[$i|tostring]) ])
| @csv

Command-line options

Use the -n option (because inputs is being used), and the -r option (to produce CSV).

answered Jan 20, 2020 at 16:55

peak

119k21 gold badges185 silver badges218 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

chepner Over a year ago

I almost added "Wait for @peak to produce something a third the size" to my answer.

Inian Over a year ago

@peak: Anyway I can fix jq -r '["id","t1","t2"],[.id, (.a[] | select(.t==1)).c//null, (.a[] | select(.t==2)).c//null ]|@csv' json to avoid printing header multiple times?

peak Over a year ago

@Inian - The simplest approach is to use inputs.

Luis Santos Over a year ago

@peak thanks for your comment. It helped me find the answer.

chepner · Accepted Answer · 2020-01-20 15:57:43Z

0

This is an absolute mess, but it works:

$ cat tmp.json
{"id": 100, "a": [{"t" : 1,"c" : 2 }, {"t": 2, "c" : 3 }] }
{"id": 200, "a": [{"t": 2, "c" : 3 }] }
{"id": 300, "a": [{"t": 1, "c" : 3 }] }
$ cat filter.jq
def t(id):
  .a |
  map({key: "t\(.t)", value: .c}) |
  ({t1:null, t2:null, id:id} | to_entries) + . | from_entries
  ;

inputs |
  map(.id as $id | t($id)) |
  (.[0] | keys) as $hdr |
  ([$hdr] + map(to_entries |map(.value)))[]|
  @csv
$ jq -rn --slurp -f filter.jq tmp.json
"id","t1","t2"
2,3,100
,3,200
3,,300

In short, you produce a direct object containing the values from your input, then add it to a "default" object to fill in the missing keys.

answered Jan 20, 2020 at 15:57

chepner

538k77 gold badges594 silver badges746 bronze badges

Collectives™ on Stack Overflow

JQ - Denormalize nested object

2 Answers 2

Command-line options

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Command-line options

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related