Group by query base on a composed field on the select

Question

So, I have these documents:

[
    { 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" },
]

I'm using Group By with this query and it's working fine by a existing field on the documents:

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, I need this to check if the products are equal, 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, currently 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 the help, Thanks

Answer 1

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)