Sum values of JSON array using JMESPath

Question

I am trying to use the sum function of JMESPath but having some trouble. I managed to figure out how to use the search function with multiple conditions. This statement:

var x = search(myData, "Account[].Details[? Year=='2018' && Title=='ABC'].Amount");

returns this JSON array:

["2404.00", "2404.00", "2402.67", "2699.00", "2699.00", "2698.49"]

Now what I would like to do is to sum these values. The JMESPath specification says to use this syntax for the built-in sum function:

number sum(array[number] $collection)

I don't understand how to use this function. Can someone please help?

BJT · Accepted Answer · 2018-08-11 06:58:56Z

2

I'we took your last example and added some more example data, we will sum Prices

const testData =
{
"ServiceAccount": [
    {
        "Type": "WIDGET",
        "ID": [
            {
                "OrderNum": "12345",
                "OrderTyp": "ABDCD",
                "Price": "10",
            }
        ]
      },
      {
        "Type": "WIDGET",
        "ID": [
            {
                "OrderNum": "22345",
                "OrderTyp": "ZBDCD",
                "Price": "20",
            }
        ]
      },
      {
        "Type": "WIDGET",
        "ID": [
            {
                "OrderNum": "22385",
                "OrderTyp": "ZBDXD",
                "Price": "30",
            }
        ]
      }
    ]
};

const result = jmespath.search(testData, 'sum(ServiceAccount[].ID[].Price.to_number(@))');

console.log(result);

<script src="https://cdnjs.cloudflare.com/ajax/libs/jmespath/0.15.0/jmespath.min.js"></script>

Answer to your current problem then would be this:

var x = search(myData, "sum(Account[].Details[? Year=='2018' && Title=='ABC'].Amount.to_number(@))");

edited Aug 11, 2018 at 6:58

answered Aug 11, 2018 at 6:46

BJT

6585 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DanielAttard Over a year ago

Using the testData from this question, how would we sum the Price values, only where the OrderNum is > 20000? I tried using var x = search(myData, "sum(Account[].Details[? OrderNum> 20000].Price.to_number(@))"); but I get this error: Uncaught Error: TypeError: sum() expected argument 1 to be type 8 but received type 3 instead.

BJT Over a year ago

@DanielAttard like this: var x = search(myData, "sum(Account[].Details[? OrderNum> 20000].Price | [].to_number(@))")

BJT Over a year ago

@DanielAttard i frogot to explain the error, the error is caused because the result is nested list, so you need to use pipe and "Flat operator" to flatten the list and then sum it.

Collectives™ on Stack Overflow

Sum values of JSON array using JMESPath

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related