USE KEYS for Flating an Array and applying filter

SQL++
1

SELECT catalog.offerId, catalog.product.productId as productId, UNNEST childSkus.fee.chargefee as cf,ARRAY s FOR s IN catalog.product.childSkus
WHEN s.skuId IN [“sku8510261”] END AS childSkus
FROM
idp_catalog
as catalog
use keys [“price::prod9220234”]
WHERE catalog.product.productId IN [“prod9220234”] and cf.commitmentTerm satisfies in [“NE24M50P”]

this is my query

Structure
Structure.jpg1920×1028 46.9 KB

I am trying to read the commitment term array… but getting error… how can i traverse

2

child skus is an array and charge fee is an array and commitment term is an array

3

Error
Error.jpg1920×1028 133 KB

I am getting this above error and not sure why its giving this error

4

UNNEST can be used in FROM clause (it is join between document and array elements inside document).
SATISFIES needs to be used with ANY clause.

SELECT catalog.offerId, catalog.product.productId as productId, cf, sku AS childSkus
FROM idp_catalog AS catalog USE KEYS ["price::prod9220234"]
UNNEST catalog.childSkus AS sku
UNNEST sku.fee.chargeFee AS cf
WHERE catalog.product.productId IN ["prod9220234"] AND
      sku.skuId IN ["sku8510261"]  AND
      ANY v IN cf.commitmentTerm SATISFIES v IN ["NE24M50P"] END;
5

hey thnaks

but its not returning me any results

6

I just tried a simple use case as atatched… getting error

SImple_Error
SImple_Error.png1920×1028 31.3 KB

7

Post sample document that can return row. Remove end;
Check documentation and examples

8

SELECT catalog.offerId, catalog.product.productId as productId, cf, sku AS childSkus
FROM idp_catalog AS catalog USE KEYS [“price::prod9220234”]
UNNEST catalog.childSkus AS sku
UNNEST sku.fee.chargefee AS cf
WHERE catalog.product.productId IN [“prod9220234”] AND
sku.skuId IN [“sku8510261”] AND
ANY v IN cf.commitmentTerm SATISFIES v IN [“NE24M50P”] END;

In the above query i guess UNNEST sku.fee.chargefee AS cf is having some issue which is resulting in empty array. I tries removing this line and alaso refernces … i was able to retrive the response

9

field names are case-sensitive so change UNNEST sku.fee.chargeFee AS cf
F is uppercase in chargeFee