CODEWITHSUNDEEP
couchbasesql++
Ravi Bhagat
Ravi Bhagat

Reputation: 11

NEST in COUCHBASE

How to nest data in couhcbase similar to mongo DB we do on reference key. we have two table In a bucket first table is "CHAIN", and second table is "STORE".

I am MONGO user previously and very new to couchbase.

Please suggest how I can nest using N1QL for couchbase

Table 1 CHAIN
{
     "chId": "chid_1",
     "chName": "Walmart",
      "type": "CHAIN"
}

2nd table STORE
{
     "chId": "chid_1",
     "csName": "store1",
     "type": "STORE"
}
{
     "chId": "chid_1",
     "csName": "store2",
     "type": "STORE"
}
I want to get data by joing these table as 
{
   "chId": "chid_1",
   "chName": "Walmart",
   "type": "CHAIN",
   "stores": [
            {"csName": "store1", "type": "STORE"},
            {"csName": "store2", "type": "STORE"}]
}

Upvotes: 1

Views: 263

Answers (1)

vsr
vsr

Reputation: 7414

Use JOIN, GROUP BY. Also checkout https://blog.couchbase.com/ansi-join-support-n1ql/

CREATE INDEX ix1 ON (chId) WHERE type = "CHAIN";
CREATE INDEX ix2 ON (chId) WHERE type = "STORE";

    SELECT c.*, ARRAY_AGG({s.type, s.csName}) AS stores
    FROM default AS c
    JOIN default AS s ON c.chId = s.chId
    WHERE c.type = "CHAIN" AND s.type = "STORE"
    GROUP BY c;

You Can also use ANSI NEST if you want include whole document

SELECT c.*, s AS stores
FROM default AS c
NEST default AS s ON c.chId = s.chId AND s.type = "STORE"
WHERE c.type = "CHAIN";

Upvotes: 1

Related Questions