ESRI JS API multiple groupByFieldsForStatistics columns and having clause

CARNELL_DEVTWO — Sun, 12 Dec 2021 03:56:25 GMT

I'm trying to query some data using ESRI JS API version 4.0. The data table that looks like a longer version of this:

`Object_ID`	`Item_ty_co`	`Serv_grade`
1	"MH"	5
2	"MH"	3
3	"IN"	3
4	"IN"	2

I am trying to group this data based on the item_ty_co and Serv_grade fields, and count the number of records in each group.

In regular SQL, my query looks like:

SELECT Item_ty_co, Serv_grade ,count(Object_ID) FROM public."pointAssets"
GROUP BY Item_ty_co ,Serv_grade  
HAVING ITEM_TY_CO not in ('PN','GN','RN','CN')

which gives me exactly the result I want. However, I want to recreate this in ESRI JS API by querying the layerview like this:

const query = layerView.layer.createQuery();       
query.outStatistics = [   
{        
   onStatisticField: "Object_ID",        
   outStatisticFieldName: "numberOfAssets",        
   statisticType: "count"   },];
query.groupByFieldsForStatistics = ["Item_ty_co ","Serv_grade" ];
query.having = "ITEM_TY_CO NOT IN ('PN','GN','RN','CN')"

However, I'm having two problems here.

It seems like the API just ignores any columns in the groupByFieldsForStatistics array after the first element, and doesn't use them for the groupby. When I switch the items around, it just uses the first (0th) element.
query.having does not filter out the groups that I specify.

So the output for the above is an array of objects like this, with one record for the unique ITEM_TY_CO's:

0: Object { numberOfAssets: 4176, ITEM_TY_CO: "PN" }
1: Object { numberOfAssets: 4662, ITEM_TY_CO: "MH" }
2: Object { numberOfAssets: 5472, ITEM_TY_CO: "CN" }

I don't know where my mistakes are. I can write workarounds, but I'd really like a clean solution. Any help/ advice would be greatly appreciated.

topic ESRI JS API multiple groupByFieldsForStatistics columns and having clause in ArcGIS JavaScript Maps SDK Questions

ESRI JS API multiple groupByFieldsForStatistics columns and having clause