Replace objects in json with their sum using JQ

January 14, 2022

Trying to solve the problem, but even don’t know how to start.
I have json like this

[
  {
    "Count": "226",
    "Action": "1",
    "Status": "1",
    "Client": "26"
  },
  {
    "Count": "224",
    "Action": "1",
    "Status": "2",
    "Client": "26"
  },
  {
    "Count": "3",
    "Action": "1",
    "Status": "6",
    "Client": "26"
  },
  {
    "Count": "233",
    "Action": "2",
    "Status": "1",
    "Client": "26"
  },
  {
    "Count": "7",
    "Action": "2",
    "Status": "2",
    "Client": "26"
  }
]

And I need to get sum of counters for distinct Action where Status > 1. Result should replace summarized elements.
Example of result:

[
  {
    "Count": "226",
    "Action": "1",
    "Status": "1",
    "Client": "26"
  },
  {
    "Count": "227",
    "Action": "1",
    "Status": "2",
    "Client": "26"
  },
  {
    "Count": "233",
    "Action": "2",
    "Status": "1",
    "Client": "26"
  },
  {
    "Count": "7",
    "Action": "2",
    "Status": "2",
    "Client": "26"
  }
]

As you can see, field Count in element with "Action": "1" and "Status": "2" was increased on value of object with "Action": "1" and "Status": "6"

I don’t know how to do it. Any help or advice would be appreciated.

>Solution :

This solution aggregates into the first items within the duplicate list (which is not obvious from your sample data, see the comments).

group_by groups the items to aggregate which is determined by a .Status greater 1 and the content of .Action. Then, with map each grouped list’s first (.[0]) item’s .Count field is calculated by adding up all .Count fields’ values. The output within each group is the first (.[0]) element.

jq '
  group_by([(.Status | tonumber > 1), .Action])
  | map(.[0].Count = (map(.Count | tonumber) | add | tostring) | .[0])
'

[
  {
    "Count": "226",
    "Action": "1",
    "Status": "1",
    "Client": "26"
  },
  {
    "Count": "233",
    "Action": "2",
    "Status": "1",
    "Client": "26"
  },
  {
    "Count": "227",
    "Action": "1",
    "Status": "2",
    "Client": "26"
  },
  {
    "Count": "7",
    "Action": "2",
    "Status": "2",
    "Client": "26"
  }
]

Demo

As .Status and .Count are strings, not numbers, it was necessary to convert them back and forth using tonumber and tostring. You may want to, especially if you have more calculations to process, first convert them into numbers, then do all the (now simpler) processing, and finally convert them back (if necessary).

jq '
  map((.Status, .Count) |= tonumber)
  | group_by([(.Status > 1), .Action])
  | map(.[0].Count = (map(.Count) | add) | .[0])
  | map((.Status, .Count) |= tostring)'

Demo