JSON aggregation functions

Feature Requests
1

Right now, for JSON there are the ALL,INDEXOF and ANY functions, which can tell if elements in a JSON array match a condition.
It would be great to add some functions that can perform calculations and aggregate the results of it.
For example, let’s say we have this JSON stored:
{
“item1”: {
“price”: 36,
“lat”: 33.3,
“long”: -40.8
},
"item12: {
“price”: 30,
“lat”: 32.6,
“long”: -41.9
},
“item3”: {
“price”: 50,
“lat”: 33.1,
“long”: -40.4
},
“item4”: {
“price”: 10,
“lat”: 32.1,
“long”: -41.4
}
}
I would like to be able to get the minimum price, something like MIN( x.price for x in myjson). It would be also interesting to be able to get the item key - which in this case would be ‘item4’.
This could also support expression, giving the example to be able to calculate all the geo distances and return the smallest one, like MIN( GEODIST(mylat,mylong,x.lat,xlong) for x in myjson)

3

If the keys can take only certain values, this can be done with expression in select composed of neste IFs/MIN, like

if(
   json.item1.p IS NOT NULL,
	MIN (
        json.item1.p,
         if(
           json.item2.price IS NOT NULL,
            MIN(
				DOUBLE(json.item2.price),
                99999
				),
           99999
          )
	),
	9999
)

It can end up big and ugly, but works.
However if the keys are not known, yes, such functions are needed.

4

there is also least \ greatest expression that might be used directly with JSON.field instead IF tree, like

SELECT least(j.a), greatest(j.a), least(j.t), greatest(j.t) FROM arrays_idx

and aggregates might be implemented in similar way. Just create ticket at issue tracker and be informed when it got closed.

5

Hello! Now we’re adding your request in our backlog. You can check the progress here JSON aggregation functions · Issue #55 · manticoresoftware/manticoresearch · GitHub Thanks for your post.