Niql Sub queries could not be able to search

Hello

I am trying to get the redeemed needles which is nested array list in the document. Now i am trying to sum all the needles based on dates. I tried reading the n1ql docs but could not be able to execute this query.

SELECT
        MIN(inv.cus_id)                 AS cus_id,
        MIN(inv.branch_name)            AS branch_name,
        MIN(inv.branch_code)            AS branch_code,
        SUM(inv.current_needles)        AS earned,
        SUM(SELECT ip.trans_total_amount
	   from stitchit_data_bucket inv UNNEST inv.invoice_payment as ip
	   WHERE  inv.type = 'invoice' AND ip.trans_date BETWEEN '2022-12-01' AND  '2022-12-31'
	   AND inv.store_id = '3221' AND ip.payment_type = 'Needle') as redeemed
        from stitchit_data_bucket inv
        WHERE  inv.type = 'invoice' AND inv.date BETWEEN '2022-12-01' AND  '2022-12-31'  AND inv.store_id = '3221'
        GROUP BY inv.branch_code

The subquery returns ARRAY of OBJECTS and SUM expects integer.
Even your subquery is not correlated. Don’t make sense.

Is this what you want.

SELECT
        MIN(inv.cus_id)                 AS cus_id,
        MIN(inv.branch_name)            AS branch_name,
        MIN(inv.branch_code)            AS branch_code,
        SUM(inv.current_needles)        AS earned,
        SUM(nsum) AS redeemed
FROM stitchit_data_bucket inv
LET nsum = ARRAY_SUM(ARRAY ip.trans_total_amount
           FOR ip IN inv.invoice_payment
           WHEN ip.payment_type = 'Needle' AND ip.trans_date BETWEEN '2022-12-01' AND  '2022-12-31' END)
WHERE inv.type = 'invoice' AND inv.date BETWEEN '2022-12-01' AND  '2022-12-31'  AND inv.store_id = '3221'
GROUP BY inv.branch_code

Thanks this what i needed :slight_smile: