Array Distinct N1Ql

SQL++
2

NOTE: Given “MtrlId”: “08052200” can have different CasePckTyp values.
Only way you can do that aggregate query and get all CasePckTyp 20M documents array that can be huge.

If you need DISTINCT of MtrlId, CasePckTyp try this. Check query is covered.

CREATE INDEX ix1 ON  default( ALL ARRAY [m.MtrlId, m.CasePckTyp]  FOR m IN  MtrlUomIdList END );

SELECT DISTINCT fltr[0] AS MtrlId, fltr[1] AS CasePckTyp
FROM default AS d
UNNEST d.MtrlUomIdList AS m
LET fltr = [m.MtrlId, m.CasePckTyp] 
WHERE fltr > [];

If CasePckTyp is same for each MtrlId, above query works. You can also try the following.

SELECT  fltr[0] AS MtrlId, MAX(fltr[1]) AS CasePckTyp
FROM default AS d
UNNEST d.MtrlUomIdList AS m
LET fltr = [m.MtrlId, m.CasePckTyp] 
WHERE fltr > []
GROUP BY  fltr[0];
1 Like