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