Group by n1ql from a composed field on the select

SQL++
,
1

So, documents would be:

[
    { defaultName: "product X", brand: { name: "brand 00", id: "brand00-00" }, externalSpec: "spec00" },
	{ defaultName: "product Y", brand: null, externalSpec: "spec01" },
	{ defaultName: "product LL", brand: { name: "brand 01", id: "brand00-01" }, externalSpec: "spec00" },
	{ defaultName: "product X", brand: { name: "brand 00", id: "brand00-00" }, externalSpec: "spec01" },
]

And I have a working group by query:

SELECT ARRAY_AGG(p) FROM `BucketXXX` AS p
WHERE p.type = 'product'
AND p.deleted = false
GROUP BY p.defaultName

Now I would like to use my group by query but using a concatenated field on the select clause, something like:

    SELECT p.defaultName || p.brand.id || p.externalSpec AS custom_key, ARRAY_AGG(p) FROM `BucketXXX` AS p
    WHERE p.type = 'product'
    AND p.deleted = false
    GROUP BY custom_key

but no luck, still getting error 3000 syntax error near AS, also keep in mind that brand could be null and I would like to add an empty string if that is null to continue with the externalSpec field.

Appreciate for help,

Thanks

2

Projection Alias can be used in ORDER BY Only. Due to evaluation order (FROM, JOIN, LET, WHERE, GROUP BY, LETTING, PROJECTION, ORDER BY).

CB 6.5 or higher GROUP BY alias is supported

SELECT custom_key, ARRAY_AGG(p) FROM `BucketXXX` AS p
WHERE p.type = 'product'
AND p.deleted = false
GROUP BY p.defaultName || p.brand.id || p.externalSpec AS custom_key

OR

SELECT custom_key, ARRAY_AGG(p) FROM `BucketXXX` AS p
LET custom_key = p.defaultName || p.brand.id || p.externalSpec
WHERE p.type = 'product'
AND p.deleted = false
GROUP BY custom_key

You have null brand, If required use CONCAT(p.defaultName, IFMISSINGORNULL(p.brand.id,""), p.externalSpec)

1 Like
3

Thanks, actually I’m using 5.5.2 build so the second option worked for me… also instead of CONCAT I apply the “||” " double pipe operator" to build the unique key