Couchbase join & nest

[bellpumpkin]

Create 4 collections as a single document

Example

collection :

• address
  {
  “city”: “Sejong”,
  “dong”: “language-dong”,
  “gu”: “behave-gu”,
  “zipcode”: 10000
  }

• customer
  {
  “birthday”: “1997-01-16”,
  “byword”: “A drowning man will clutch a straw. When someone is in a difficult situation, s/he will take any available opportunity to come out of it.”,
  “custid”: “cus_46974”,
  “email”: “gold@shows.com”,
  “name”: “Jang pound”,
  “phone”: “010-2165-8424”,
  “street”: “deliver undervalue 523 load”,
  “zipcode”: 10000
  }

• orders,
  {
  “order_date”: “2013-11-02”,
  “orderno”: 266106,
  “ship_date”: “2013-12-03”
  } …

• orders_detail
  {
  “itemno”: 8519,
  “orderno”: 266106,
  “qty”: 2,
  “seqno”: 6,
  “total_price”: 39200,
  “unit_price”: 19600
  },
  {
  “itemno”: 3504,
  “orderno”: 266106,
  “qty”: 5,
  “seqno”: 3,
  “total_price”: 68000,
  “unit_price”: 13600
  }…

I want result

{
“address”: {
“city”: “Sejong”,
“dong”: “language-dong”,
“gu”: “behave-gu”,
“zipcode”: 10000
},
“birthday”: “1997-01-16”,
“custid”: “cus_46974”,
“email”: “gold@shows.com”,
“name”: “Jang pound”,
“orders”: [
{
“order_date”: “2013-11-02”,
“orderno”: 266106,
“ship_date”: “2013-12-03”
“Item” : {
“itemno”: 8519,
“orderno”: 266106,
“qty”: 2,
“seqno”: 6,
“total_price”: 39200,
“unit_price”: 19600
},
{
“itemno”: 3504,
“orderno”: 266106,
“qty”: 5,
“seqno”: 3,
“total_price”: 68000,
“unit_price”: 13600
}
},
{
“order_date”: “2016-11-25”,
“orderno”: 495767,
“ship_date”: “2016-11-27”
…
}
],
“phone”: “010-0000-0000”
}

SQL :
SELECT META(c).id AS k,
{c.custid,c.name,c.phone, c.email, c.birthday,
“address” :{ad.zipcode, ad.city, ad.gu,ad.dong},
“orders” : ARRAY{i.orderno, i.order_date, i.ship_date, “Item” : {od.itemno, od.qty, od.seqno, od.unit_price, od.total_price }} FOR i IN t END} AS v
– “Item”:{od.itemno, od.orderno, od.qty, od.seqno, od.unit_price, od.total_price}
FROM MIG.Postgre.customer AS c
JOIN MIG.Postgre.address AS ad ON ad.zipcode = c.zipcode
NEST MIG.Postgre.orders AS t ON t.custid = c.custid
JOIN MIG.Postgre.orders_detail AS od ON od.orderno = t.orderno
LIMIT 1

Not Worling

[dh]

In your example data “orders” is missing a “custid” field.
When you NEST something, it is an ARRAY, so when you try join to “orders_detail” you must specify the array element you want to join to. I presume what you’re actually after is an embedded array of orders each of which has an embedded array of items.
You can try:

SELECT META(customer).id AS k,
       customer.*,
       address,
       orders
FROM MIG.Postgre.customer
JOIN MIG.Postgre.address ON address.zipcode = customer.zipcode
LET orders = (SELECT orders.*, items
              FROM MIG.Postgre.orders AS orders
              NEST MIG.Postgre.orders_detail AS items ON items.orderno = orders.orderno
              WHERE orders.custid = customer.custid)
LIMIT 1;

along with:

CREATE INDEX ix1 ON MIG.Postgre.orders(custid);
CREATE INDEX ix2 ON  MIG.Postgre.orders_detail(orderno);

And you should get something like:

    "results": [
    {
        "address": {
            "city": "Sejong",
            "dong": "language-dong",
            "gu": "behave-gu",
            "zipcode": 10000
        },
        "birthday": "1997-01-16",
        "byword": "A drowning man will clutch a straw. When someone is in a difficult situation, s/he will take any available opportunity to come out of it.",
        "custid": "cus_46974",
        "email": "gold@shows.com",
        "k": "k0",
        "name": "Jang pound",
        "orders": [
            {
                "custid": "cus_46974",
                "items": [
                    {
                        "itemno": 8519,
                        "orderno": 266106,
                        "qty": 2,
                        "seqno": 6,
                        "total_price": 39200,
                        "unit_price": 19600
                    }
                ],
                "order_date": "2013-11-02",
                "orderno": 266106,
                "ship_date": "2013-12-03"
            }
        ],
        "phone": "010-2165-8424",
        "street": "deliver undervalue 523 load",
        "zipcode": 10000
    }
    ],

(You have the fields “street” & “zipcode” in the “customer” record hence they appear as they do; you could be more explicit with the fields from customer in order to exclude them.)

HTH.