Array Distinct N1Ql

SQL++
1

Hi, I am struggling with a query.

My doc looks like this:

{
  "RteDocId": "Route::1001_027",
  "TrdGtinEo08Val": null,
  "$MdfdTmstmp": "2021-10-07T11:27:08.602Z",
  "Id": "RAG::1001_0017082876805_027",
  "$DocVrsn": "1.0",
  "$Type": "RAG",
  "MtrlUomIdList": [
    {
      "MtrlId": "08052200",
      "CasePckQty": 1,
      "CasePckTyp": "REG",
      "MtrlUomId": "08052200"
    },
    {
      "MtrlId": "08052200",
      "CasePckQty": 12,
      "CasePckTyp": "REG",
      "MtrlUomId": "08052201"
    }
  ]
}

There are about 20 million of these docs on the server.
What I need is CasePckTyp for all DISTINCT MtrlId across all docs. I am trying to avoid doing an unnest on MtrlUomIdList as that section is quite large (i am showing only 4 fields in it in my example here).

What I was thinking is to create an object or array with just MtrlId and CasePckTyp and then find distinct MtrlId within that array. Struggling to write that query though… Any pointers on how to go about doing this? Thanks

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