CODEWITHSUNDEEP
jsonshellcommand-linemergejq
Ateny
Ateny

Reputation: 3

How to merge and aggregate values in 2 JSON files using jq?

I am using jq in a shell script to manipulate JSON files.

I have 2 files and I'd like to merge them into one file while also aggregating (sum) the values when names in the name/value pairs are the same.

As an example:

Input1.json

[
  {
    "A": "Name 1",
    "B": "1.1",
    "C": "2"
  },
  {
    "A": "Name 2",
    "B": "3.2",
    "C": "4"
  }
]

Input2.json

[
  {
    "A": "Name 2",
    "B": "5",
    "C": "6"
  },
  {
    "A": "Name 3",
    "B": "7",
    "C": "8"
  }
]

Expected result:

Output.json

[
  {
    "A": "Name 1",
    "B": "1.1",
    "C": "2"
  },
  {
    "A": "Name 2",
    "B": "8.2",
    "C": "10"
  },
  {
    "A": "Name 3",
    "B": "7",
    "C": "8"
  }
]

I can use other tools other than jq but prefer to ultimately keep the solution contained into a shell script I can call from the Terminal.

Any help is appreciated. Thank you.

Upvotes: 0

Views: 273

Answers (3)

Jeff Mercado
Jeff Mercado

Reputation: 134521

jq is beautiful for problems like this:

$ jq -n '
reduce inputs[] as {$A,$B,$C} ({};
    .[$A] |= {
        $A,
        B: (.B + ($B|tonumber)),
        C: (.C + ($C|tonumber))
    }
)
| map({
    A,
    B: (.B|tostring),
    C: (.C|tostring)
  })
' input1.json input2.json

The first reduce creates a map from the different "A" values to the aggregated result object. Then given the mapping, converts back to an array of the result objects adjusting the types of the results.

jqplay

Upvotes: 0

Reino
Reino

Reputation: 3443

I can use other tools other than jq but prefer to ultimately keep the solution contained into a shell script I can call from the Terminal.

You could give the JSON parser a try:

$ xidel -se '
  array{
    let $src:=(json-doc("Input1.json")(),json-doc("Input2.json")())
    for $name in distinct-values($src/A)
    let $obj:=$src[A=$name]
    return
    if (count($obj) gt 1) then
      map:merge(
        $obj[1]() ! {
          .:if ($obj[1](.) castable as decimal) then
            string($obj[1](.) + $obj[2](.))
          else
            $obj[1](.)
        }
      )
    else
      $obj
  }
'

Intermediate steps.

Upvotes: 1

peak
peak

Reputation: 116910

Here's one way, but there are others:

jq -s '
 def to_n: tonumber? // null;
 def merge_values($x;$y): 
   if $x == $y then $x
   elif $x == null then $y
   elif $y == null then $x
   else ($x|to_n) as $xn
   | if $xn then ($y|to_n) as $yn | ($xn+$yn)|tostring
     else [$x, $y]
     end
   end;
 def merge($x;$y):
   reduce ($x + $y |keys_unsorted)[] as $k (null;
     .[$k] = merge_values($x[$k]; $y[$k]) );

   

 INDEX(.[0][]; .A) as $in1
 | INDEX(.[1][]; .A) as $in2
 | ($in1 + $in2|keys_unsorted) as $keys
 | reduce $keys[] as $k ([];
     . + [merge($in1[$k]; $in2[$k]) ])

    
    
' input1.json inut2.json

Upvotes: 0

Related Questions