jsonshellcommand-linemergejq

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.


Solution

  • 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