Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

jq count the number of items in json by a specific key

The following is the first two items in my json file

{
"ReferringUrl": "N",
"OpenAccess": "0",
"Properties": {
    "ItmId": "1694738780"
   }
}
{
"ReferringUrl": "L",
"OpenAccess": "1",
"Properties": {
    "ItmId": "1347809133"
  }
}

I want to count the number of items by each ItmId appeared in the json. For example, items that with "ItmId" 1694738780 appears 10 times and items with "ItmId" 1347809133 appears 14 times in my json file. Then return a json like this

{"ItemId": "1694738780",
 "Count":  10
}
{"ItemId": "1347809133",
 "Count":  14
}

I am using bash. And prefer do this totally by jq. But it's ok to use other method.

Thank you!!!

like image 859
Eleanor Avatar asked Jul 18 '17 15:07

Eleanor


2 Answers

Here's a super-efficient solution -- in particular, no sorting is required. The following implementation requires a version of jq with inputs but it is easy to adapt the program to use earlier versions of jq. Please remember to use the -n command-line option if using the following:

# Count the occurrences of distinct values of (stream|tostring).
# To avoid unwanted collisions, or to recover the exact values,
# consider using tojson
def counter(stream):
  reduce stream as $s ({}; .[$s|tostring] += 1);

counter(inputs | .Properties.ItmId)
| to_entries[]
| {ItemId: (.key), Count: .value}
like image 88
peak Avatar answered Oct 19 '22 21:10

peak


Using jq command

cat json.txt | jq '.Properties .ItmId' | sort | uniq -c | awk -F " " '{print "{\"ItmId\":" $2 ",\"count\":" $1"}"}'| jq .
like image 42
skr Avatar answered Oct 19 '22 21:10

skr