Question

Source:https://stackoverflow.com/questions/52817156/functions-min-max-or-sum-with-jsonpath

According to JsonPath on GitHub it shall be possible to access max(), min() or the sum() of an array but I don’t know how. I have the following example data:

{

"store":{

"book":[

{

"category":"reference",

"author":"NigelRees",

"title":"SayingsoftheCentury",

"price":8.95

},

{

"category":"fiction",

"author":"EvelynWaugh",

"title":"SwordofHonour",

"price":12.99

},

{

"category":"fiction",

"author":"HermanMelville",

"title":"MobyDick",

"isbn":"0-553-21311-3",

"price":8.99

},

{

"category":"fiction",

"author":"J.R.R.Tolkien",

"title":"TheLordoftheRings",

"isbn":"0-395-19395-8",

"price":22.99

}

],

"bicycle":{

"color":"red",

"price":19.95

}

}

}

I would expect it to work like:

$..book.length

so I’m trying:

$..price.sum

But that didn't do the job.

Can someone help me?

Answer

Your JSON data contains a set of records and you need to perform aggregation on these sets. The example code uses a sum operation, and similar way of handling is for both min and max. Yet JSONPath only supports aggregation on a simple set and enumeration before aggregation, but it does not support aggregation on a set of records.

You can use SPL, an open-source Java package, to do it if you want a simple and easy alternative. You just need a single line of code:

	A
1	=json(file("data.json").read()).store.book.sum(price)

SPL offers JDBC driver to be invoked by Java. Just store the above SPL script as sum.splx and invoke it in a Java application as you call a stored procedure:

…

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

st = con.prepareCall("call sum()");
st.execute();

…

View SPL source code.