How to gather values into array by keys?

Question

Given input in entries format, possibly with duplicated keys, e.g.

[
  {"key": "a", "value": 0},
  {"key": "a", "value": 1},
  {"key": "a", "value": 2},
  {"key": "b", "value": 3},
  {"key": "b", "value": 4},
  {"key": "b", "value": 5}
]

I want to produce

{"a": [0, 1, 2], "b": [3, 4, 5]}

Namely, to gather all values with the same key in an array for each unique key.

How can I do this in jq?

Answer 1

Using reduce() in jq to gradually build up the resulting object by adding the given value values to the keys given by the key values:

$ jq -c 'reduce .[] as $a ({}; .[$a.key] += [$a.value])' file
{"a":[0,1,2],"b":[3,4,5]}

The reduce() acts like a sort of loop that, in this case, iterates over .[], i.e. all the objects in your top-level array. For each object, $a, the value $a.value is added to the result object (which is initially the empty {} object) under the key $a.key.

---

An additional tip for people who like playing with jq: By using debug, you can look at the data at any point in your expression when it's running. Here I'm investigating the state of the accumulator object after each iteration in the reduce() loop:

$ jq -c 'reduce .[] as $a ({}; .[$a.key] += [$a.value] | debug)' file
["DEBUG:",{"a":[0]}]
["DEBUG:",{"a":[0,1]}]
["DEBUG:",{"a":[0,1,2]}]
["DEBUG:",{"a":[0,1,2],"b":[3]}]
["DEBUG:",{"a":[0,1,2],"b":[3,4]}]
["DEBUG:",{"a":[0,1,2],"b":[3,4,5]}]
{"a":[0,1,2],"b":[3,4,5]}

And here I'm investigating the values that are assigned to $a in each iteration:

$ jq -c 'reduce (.[]|debug) as $a ({}; .[$a.key] += [$a.value])' file
["DEBUG:",{"key":"a","value":0}]
["DEBUG:",{"key":"a","value":1}]
["DEBUG:",{"key":"a","value":2}]
["DEBUG:",{"key":"b","value":3}]
["DEBUG:",{"key":"b","value":4}]
["DEBUG:",{"key":"b","value":5}]
{"a":[0,1,2],"b":[3,4,5]}

Answer 2

You can use group_by , map and from_entries

jq 'group_by(.key) | map({key: .[0].key, value: [.[].value]}) | from_entries' data.json 

Answer 3

If you already know a programming language, that's a easy option rather than having to use the very specific jq language. With perl:

$ perl -MJSON -l -0777 -ne '
    for (@{decode_json$_}) {push @{$out->{$_->{key}}}, $_->{value}}
    print encode_json $out' your-file.json
{"a":[0,1,2],"b":[3,4,5]}

Answer 4

Using Raku (formerly known as Perl_6)

You need to use a JSON parser:

~$ raku -MJSON::Tiny -e 'my @json = from-json($_).list given slurp;  \
         my %accum.push: .<key> => .<value>.map(*.Num).Slip for @json>>.split(", ");  \
         .say for to-json(%accum.sort);'  file

Sample Input:

[
  {"key": "a", "value": 0},
  {"key": "a", "value": 1},
  {"key": "a", "value": 2},
  {"key": "b", "value": 3},
  {"key": "b", "value": 4},
  {"key": "b", "value": 5}
]

Sample Output (values have been Num-ified and Slipped, i.e. flattened):

[ { "a" : [ 0, 1, 2 ] }, { "b" : [ 3, 4, 5 ] } ]

The final code adds a call to to-json() at the end. To figure out what's going on in the intermediate steps, it's probably easiest to show you the output of each individual data structure. Printing the @json array with .say for @json; returns the following:

{key => a, value => 0}
{key => a, value => 1}
{key => a, value => 2}
{key => b, value => 3}
{key => b, value => 4}
{key => b, value => 5}

Generating an %accum hash with only .<key> => .<value> (in other words, without .map(*.Num).Slip) returns the following via a call to .say for %accum.sort;:

[ { "a" : [ [ "0" ], [ "1" ], [ "2" ] ] }, { "b" : [ [ "3" ], [ "4" ], [ "5" ] ] } ]

Note, you can do various manipulations of the values with function calls like Num, Int, etc., and you can "flatten" with a call to Slip as in the code posted at top.

Raku's JSON::Tiny module will stringify values by default, and if you want those stringified values as a single list (instead of sub-lists), you Slip them together.

Below, stringified values Slipped together:

~$ raku -MJSON::Tiny -e 'my @json = from-json($_).list given slurp;  \
         my %accum.push: .<key> => .<value>.Slip for @json>>.split(", ");  \  
        .say for to-json(%accum.sort);'  file
[ { "a" : [ "0", "1", "2" ] }, { "b" : [ "3", "4", "5" ] } ]

So a lot of options here for data output, the simplest being taking the code at the top and removing the final to-json() call, to see Raku's "pairs" representation of the %accum hash:

a => [0 1 2]
b => [3 4 5]

https://raku.land/cpan:MORITZ/JSON::Tiny
https://raku.org