N1QL Group By Column Name - Help

Couchbase Server
1

Hi Team,
Need help on one of the N1QL query. we have routematerial documents for each route( multiple documents for route based on product gtins), we need to get distinct MtrlUomIds for each route.

Ex: Document 1:
{
“Id”: “RouteAuthorizedGtin::34437_0028400089913_157”,
“$DocVrsn”: “1.0”,
“$Type”: “RouteAuthorizedGtin”,
“$TxId”: “1582651334”,
“$MdfdById”: “batch-id”,
“$MdfdTmstmp”: “2020-04-05T22:06:43.000Z”,
“RteId”: “34437”,
“RteCtryCd”: “157”,
“MtrlUomIdList”: [
{
“MtrlUomId”: “05680100”,
“MtrlId”: “05680100”,
“CtryCd”: “157”,

	},
	{
		"MtrlUomId": "**10257400**",
		"MtrlId": "10257400",
		"CtryCd": "157",
	}
]

}
document 2:
{
“Id”: "RouteAuthorizedGtin::34437_0028400089914_157",
“$DocVrsn”: “1.0”,
“$Type”: “RouteAuthorizedGtin”,
“$TxId”: “1582651334”,
“$MdfdById”: “batch-id”,
“$MdfdTmstmp”: “2020-04-05T22:06:43.000Z”,
“RteId”: “34437”,
“RteCtryCd”: “157”,
“MtrlUomIdList”: [
{
“MtrlUomId”: “05680104”,
“MtrlId”: “05680100”,
“CtryCd”: “157”,

	},
	{
		"MtrlUomId": "**10257403**",
		"MtrlId": "10257400",
		"CtryCd": "157",
	}
]

}

Expected results - RteId : 34437 - MtrlUomIds :[05680100,10257400,05680104,10257403]

2 
CREATE INDEX ix1 ON mybucket(RteId, MtrlUomIdList[*].MtrlUomId) WHERE `$Type` = "RouteAuthorizedGtin";

SELECT b.RteId , ARRAY_DISTINCT(ARRAY_FLATTEN(ag,1)) AS MtrlUomIds
FROM default AS b
WHERE b.RteId IS NOT NULL AND b.`$Type` = "RouteAuthorizedGtin"
GROUP by b.RteId
LETTING ag = ARRAY_AGG(b.MtrlUomIdList[*].MtrlUomId);


SELECT b.RteId, ARRAY_AGG(DISTINCT mu) AS MtrlUomIds
FROM mybucket AS b
UNNEST b.MtrlUomIdList[*].MtrlUomId AS mu
WHERE b.RteId IS NOT NULL AND b.`$Type` = "RouteAuthorizedGtin"
GROUP by b.RteId ;

OR

CREATE INDEX ix2 ON mybucket(RteId) WHERE `$Type` = "RouteAuthorizedGtin";
SELECT b.RteId, ARRAY_AGG(DISTINCT mu.MtrlUomId) AS MtrlUomIds
FROM mybucket AS b
UNNEST b.MtrlUomIdList AS mu
WHERE b.RteId IS NOT NULL AND b.`$Type` = "RouteAuthorizedGtin"
GROUP by b.RteId ;