N1QL Sub query with GROUP BY on CASE WHEN on a previous result

SQL++
1

Hello all,
So I’ve been working on this query:

SELECT custom_key, ARRAY_AGG(p) AS products 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

It is working fine and the result query is like this:

[
	{
		products: [
			{ 
				id: "px0", 
				defaultName: "product X", 
				brand: { name: "brand 00", id: "brand00-00" }, 
				externalSpec: "spec01", 
				suppliers: [ { id: "sup00", name: "Supplier Pope" }, { id: "sup99", name: "Supplier Jhon" , { id: "sup01", name: "Supplier Pope" }} ] 
			},
		],
		unique_key: "product X|brand00-00|spec01"
	},
	{
		products: [
			{ defaultName: "product Y", brand: null, externalSpec: "spec01", suppliers: [] }
		],
		unique_key: "product Y||spec01"
	},
	{
		products: [
			{ defaultName: "product LL", brand: { name: "brand 01", id: "brand00-01" }, externalSpec: "spec00", suppliers: null },
		],
		unique_key: "product LL|brand00-01|spec00"
	},
]

Now I need to perform some operations on this result in order to return a true or false value…

SELECT RAW 
    CASE 
       WHEN COUNT(repeated _ products) == 0 THEN TRUE
       WHEN COUNT(repeated_products) > 1 THEN FALSE
       WHEN 
             COUNT(repeated_products) == 1 
            AND repeated_products[0] IS NOT NULL
            AND repeated_products[0].products[0] IS NOT NULL
            AND repeated_products[0].products[0].suppliers IS NOT NULL
            AND 
                 COUNT(SELECT  suppliers.id FROM  repeated_products[0].products[0].suppliers AS suppliers GROUP BY  suppliers.id ) > 1 THEN FALSE
       ELSE TRUE
   END
FROM (SELECT custom_key, ARRAY_AGG(p) AS products 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) AS repeated_products

For that case the two first cases seems to be working fine but the more last elaborated case where I want to check if the supplier is repeated by grouping is giving an error code 3000, any help would be appreciated,

Thanks

2

so I think I just fixed my issues… basically I should be using ARRAY_LENGTH instead of COUNT and the sub query on the last case when should be wrapped on parenthesis.

SELECT RAW 
    CASE 
       WHEN ARRAY_LENGTH(repeated _ products) == 0 THEN TRUE
       WHEN ARRAY_LENGTH(repeated_products) > 1 THEN FALSE
       WHEN 
             ARRAY_LENGTH(repeated_products) == 1 
            AND repeated_products[0] IS NOT NULL
            AND repeated_products[0].products[0] IS NOT NULL
            AND repeated_products[0].products[0].suppliers IS NOT NULL
            AND 
                 ARRAY_LENGTH((SELECT  suppliers.id FROM  repeated_products[0].products[0].suppliers AS suppliers GROUP BY  suppliers.id )) > 1 THEN FALSE
       ELSE TRUE
   END
FROM (SELECT custom_key, ARRAY_AGG(p) AS products 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) AS repeated_products
3

Please revisit your query logic. repeated_products will not be ARRAY because it is FROM clause alias it will be individual documents. products[0] will not be same always because there is no preserving order in ARRAY_AGG().

If you need explain what are the requirements will provide query.

1 Like
4

It seems u are right the ARRAY_LENGTH is not working as expected, only by using COUNT is returning the correct evalutation from number THEN, but once I combine the COUNT in the last CASE WHEN it gives me a 4210 code error, modifying my query:

SELECT RAW 
    CASE 
       WHEN COUNT(repeated _ products) == 0 THEN TRUE
       WHEN COUNT(repeated_products) > 1 THEN FALSE
       WHEN 
             COUNT(repeated_products) == 1 
            AND ARRAY_LENGTH(repeated_products[0].products[0]) == 1
            AND 
                 ARRAY_LENGTH((SELECT  suppliers.id FROM  repeated_products[0].products[0].suppliers AS suppliers GROUP BY  suppliers.id )) > 1 THEN FALSE
       ELSE TRUE
   END
FROM (SELECT custom_key, ARRAY_AGG(p) AS products 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) AS repeated_products

The expected result would be check if there’s a repeated supplier inside a repeated products, but of course I need to ensure that there’s only one product on repeated_products products field.

5

Check this out.

SELECT RAW CASE ARRAY_LENGTH(repeated_products)
                WHEN 0 THEN TRUE
                WHEN 1 THEN (ARRAY_LENGTH(repeated_products[0].products) == 1 AND ARRAY_LENGTH(repeated_products[0].products[0]) < 2 )
                WHEN 2 THEN FALSE
                ELSE FALSE END
LET repeated_products = (SELECT custom_key,
                                ARRAY_AGG(IFMISSINGORNULL((SELECT RAW COUNT(DISTINCT s.id)
                                                           FROM p.suppliers AS s)[0], 0)) AS products
                         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
                         LIMIT 2);