CODEWITHSUNDEEP
jsonbashjq
MAzZY
MAzZY

Reputation: 35

Replace objects in json with their sum using JQ

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.

Upvotes: 1

Views: 108

Answers (1)

pmf
pmf

Reputation: 36296

This solution aggregates into the first items within each 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

Upvotes: 2

Related Questions