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
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