0

I have a list of credit card transactions. I would like to sum the Amount from the stream of JSON transactions. Each object contains data for 1 transaction. I use JQ to process JSON.

I have referenced this question: How do I sum the values in an array of maps in jq?

However after implementing the solution described in the question.

jq -n '
reduce (inputs | to_entries[]) as {$key,$value} ({}; .[$key] += $value)
' input.json

I noticed that the first number is not included in the addition. Below find sample file, output of command, and expected result

Sample File CC Transactions "jqAdditionSample.json"

{
  "Originating Account Number": "####-####-####-0000",
  "Cardholder Name": "NAME ON CARD",
  "Merchant Name": "VENDOR NAME",
  "Amount": 342.75,
  "MCC Description": "Computer Software"
}
{
  "Originating Account Number": "####-####-####-0000",
  "Cardholder Name": "NAME ON CARD",
  "Merchant Name": "VENDOR NAME",
  "Amount": 492.71,
  "MCC Description": "Computer Software"
}
{
  "Originating Account Number": "####-####-####-0000",
  "Cardholder Name": "NAME ON CARD",
  "Merchant Name": "VENDOR NAME",
  "Amount": 573,
  "MCC Description": "Computer Software"
}

Expected Actual Total

Add the Amounts manually to find total 342.75+492.71+573=1408.46

Run the command

cat ~/Desktop/jqAdditionSample.json | \
  jq 'reduce (inputs | to_entries[]) as {$key,$value} ({}; .[$key] += $value)'

Result:

{
  "Originating Account Number": "####-####-####-0000####-####-####-0000",
  "Cardholder Name": "NAME ON CARDNAME ON CARD",
  "Merchant Name": "VENDOR NAMEVENDOR NAME",
  "Amount": 1065.71,
  "MCC Description": "Computer SoftwareComputer Software"
}

Note Discrepancy

The output 1065.71 adds sums all numbers except the first number. We can verify by subtracting 342.75 from expected value 1408.46. I want it to sum all numbers

In fact, no data from the first object is included in the sum.

Where I need help

Is there a way to make JQ sum all numbers from a field in a stream, am I doing something wrong?

Is there an easier method to sum all numbers for a field in JSON stream where I am taking the hard route?

peak
  • 105,803
  • 17
  • 152
  • 177
Goldfish
  • 576
  • 1
  • 7
  • 22
  • 2
    You aren't using `-n` in the "run the command" section, even though it's present in the "solution described in the question" code you quote. It's critical if you don't want your first entry to be consumed. – Charles Duffy Dec 26 '18 at 22:05
  • 1
    BTW, when you use backticks instead of four-space indents to format code (or structured data like JSON), you defeat Stack Overflow's syntax highlighting. Please use code sections when quoting a line or more of code or structured (syntax-highlightable) data. – Charles Duffy Dec 26 '18 at 22:08
  • 1
    ...as another aside, `cat file | jq ...` is much better written as ` – Charles Duffy Dec 26 '18 at 22:09

1 Answers1

2

The problem is the way you're using inputs. According to the documentation, inputs "outputs all remaining inputs, one by one". Fine, but note the "remaining" part. By the time your code is run, the first input has already been read into ., so inputs returns all of the records except that one. The simple fix is to use the -n option to jq, which runs the filter once, with the null value in .. This leaves all of the records available for reading by inputs, which makes your code work correctly.

hobbs
  • 223,387
  • 19
  • 210
  • 288
  • Ah interesting. I had always used `-n` to construct JSON objects from scratch while providing no input, an oversight indeed. That's useful that it can also be used to run the first iteration of a filter as well. Thank you – Goldfish Dec 26 '18 at 22:12