Looping over an array in side an array


#1

SELECT catalog.offerId, catalog.product.productId as productId, ARRAY s FOR s IN catalog.product.childSkus
WHEN s.skuId IN [“sku8510261”,“sku8440478”,“sku7891027”] END AS childSkus
FROM
idp_catalog
as catalog
UNNEST catalog.product.childSkus.pricing AS pricing
use keys [“price::prod9220234”,“price::prod9150410”,“price::prod9070441”]
WHERE catalog.product.productId IN [“prod9220234”,“prod9150410”,“prod9070441”]

This is my query and i need no loop over the childsku’s array and also any array inside childshu of pricing and need to check the value in it
image


#2

You can cascade UNNEST and apply predicate on unnest

UNNEST catalog.product.childSkus AS csku
UNNEST csku.fee.ChargeFee AS p
....

WHERE ....  AND csku.skuId  IN ["sku8510261","sku8440478","sku7891027"] AND p.fee = 45 ..

If you want reconstruct array

ARRAY (ARRAY p FOR p IN s.fee.ChargeFee WHEN p.fee = 45 END) 
  FOR s IN catalog.product.childSkus WHEN s.skuId IN ["sku8510261","sku8440478","sku7891027"] END

If you want FLATTEN array use ARRAY_FLATTEN().

If still need help, Please post the complete document, what conditions to apply and expected output