How to iterate over an array and replace the value from another array in different document

Couchbase Server
1

Currently I’m trying to iterate over this document :

> {
>   "type" : "shop_doc",
>   "shops" : 
>     [
>       {
>         "shop_id" : "shopA",
>         "shop_item" : ["shopA_itemId_1", "shopA_itemId_2"]
>       },
>       {
>         "shop_id" : "shopB",
>         "shop_item" : ["shopB_itemId_3"]
>       }
>     ]
> }

to replace shop_item content with the content from :

> {
>     "type" : "item_doc",
>     "items" : 
>       [
>         {
>           "item_id" : "shopA_itemId_1",
>           "item_info" : "original id and other information about item 1"
>         },
>         {
>           "item_id" : "shopA_itemId_2",
>           "item_info" : "original id and other information about item 2"
>         },
>         {
>           "item_id" : "shopB_itemId_3"
>           "item_info" : "original id and other information about item 3"
>         }
>       ]
>   }

and expecting the output to be like this :

> {
>   "cart_detail" : 
>     [
>       {
>         "shop_id" : "shopA",
>         "shop_item" :
>           [
>             {
>               "item_id" : "shopA_itemId_1",
>               "item_info" : "original id and other information about item 1"
>             },
>             {
>               "item_id" : "shopA_itemId_2",
>               "item_info" : "original id and other information about item 2"
>             }
>           ]
>       },
>       {
>         "shop_id" : "shopB",
>         "shop_item" :
>           [
>             {
>               "item_id" : "shopB_itemId_3"
>               "item_info" : "original id and other information about item 3"
>             }
>           ]
>       }
>     ]
> }

I’m really new with N1QL and really appreciate the help from anyone regarding this.
I’ve tried some UNNEST and ANY function but still didn’t get any clue about this, I got stuck by :

SELECT 
    shop.metadata, 
    shop_items
FROM 
    `SCHEMA_1` item JOIN `SCHEMA_1` shop ON KEYS item.cartKey
UNNEST 
    shop.shops AS shop_items
2 
SELECT shop.shop_id, ARRAY_AGG(item) AS shop_item
FROM default AS sd
UNNEST sd.shops AS shop
UNNEST (SELECT RAW item FROM default AS si UNNEST si.items AS item WHERE si.type = "item_doc") AS item
WHERE sd.type = "shop_doc" AND item.item_id IN shop.shop_item;
GROUP BY shop.shop_id;
3

It works like a charm. Thank you very much :slight_smile: