Couchbase join between two buckets using array of keys

SQL++
1

Problem-statement: Couchbase Bucket_1 have an array of keys(stud_ids) and Bucket_2(stud_id) require a join with those array of keys(stud_ids). Could you please help.
How to write a query to get Student Name and Course in this scenario using join. Please find the below example for the clear understanding.

Bucket_1:
Document-1
{
“Name”: “ABC”,
“id”: “1001”,
“description”: “none”,
“stud_ids”: [“S1”, “S2”],
“location”: “uk”
}
Document-2
{
…
}

Bucket_2:
Document-1
{
“stud_id”: “S1”,
“stud_name”: “Student1”,
“course”: “MS”
}

Document-2
{
“stud_id”: “S2”,
“stud_name”: “Student2”,
“course”: “MS”
}

Document-3
{
…
}

2

With two buckets, “student” and “course”, and having run:

    insert into student (key,value) values('doc1',{"stud_id":"S1","stud_name":"Student1","course":"MS"}),('doc2',{"stud_id":"S2","stud_name":"Student2","course":"MS"});

    insert into course (key,value) values('doc1',{"name":"ABC","id":"1001","description":"none","stud_ids":["S1","S2"],"location":"uk"});
    insert into course (key,value) values('doc2',{"name":"DEF","id":"1002","description":"none","stud_ids":["S1","S3"],"location":"uk"});


    create primary index on `default`:`student`
    create index ix1 ON `default`:`course`(stud_ids);

cbq> select s.stud_name student, c.name course from student s join course c on s.stud_id IN c.stud_ids;
{
    "requestID": "50d954a9-d623-4d53-93e2-f12e8e4acae4",
    "signature": {
        "course": "json",
        "student": "json"
    },
    "results": [
    {
        "course": "ABC",
        "student": "Student1"
    },
    {
        "course": "DEF",
        "student": "Student1"
    },
    {
        "course": "ABC",
        "student": "Student2"
    }
    ],
    "status": "success",
...

HTH.

1 Like
3

Also checkout Example 12,13,14 at ANSI JOIN Support in N1QL | The Couchbase Blog

4

That’s Great, Thank you. It works.

Also could you provide an update query to update course table with below stud_ids into the course document whose university=“abc”?

Bucket Name: Student
insert into student (key,value) values
(‘doc3’,{“stud_id”:“S3”,“stud_name”:“Student3”,“course”:“MS”, “university”: “abc”},),
(‘doc4’,{“stud_id”:“S4”,“stud_name”:“Student4”,“course”:“MS”, “university”: “abc”});

using a join between Student and Cource Buckets on the key - university I have to update course document like “stud_ids” : [“s3”, “s4”] for each university.

update course
set stud_ids = …
…
…
where university=“abc”

after update the document in course bucket should be like the below.

Bucket Name: Course
document_3
{
“Name”: “MS”,
“id”: “1003”,
“stud_ids”: [“S3”,“S4”],
“location”: “uk”
“university”: “abc”
}
{
…
}

5

With the addition of a primary index (or suitable other index) on ‘course’:
Before:

cbq> select name,stud_ids from course;
{
    "requestID": "66d0ee03-e27b-432c-a3e8-331d6be167a1",
    "signature": {
        "name": "json",
        "stud_ids": "json"
    },
    "results": [
    {
        "name": "ABC",
        "stud_ids": [
            "S1",
            "S2"
        ]
    },
    {
        "name": "DEF",
        "stud_ids": [
            "S1",
            "S2"
        ]
    }
    ],
    "status": "success",
...

Update:

cbq> update course c set stud_ids = (select raw stud_id from student where university = 'uni1') where c.name = 'DEF';
{
    "requestID": "5567f6c1-0389-478a-961a-f85ab4b46cc5",
    "signature": null,
    "results": [
    ],
    "status": "success",
...

After:

cbq> select name,stud_ids from course;
{
    "requestID": "f3b4e762-99ba-4f7c-ad13-9e3c68158285",
    "signature": {
        "name": "json",
        "stud_ids": "json"
    },
    "results": [
    {
        "name": "ABC",
        "stud_ids": [
            "S1",
            "S2"
        ]
    },
    {
        "name": "DEF",
        "stud_ids": [
            "S3",
            "S4"
        ]
    }
    ],
...

The “RAW” keyword is necessary for a plain array of the values.

HTH.

6

With:

cbq> select stud_id,course,university from student s;
{
    "requestID": "09382da9-fbc0-4d38-b41c-7b46b35bb4e6",
    "signature": {
        "course": "json",
        "stud_id": "json",
        "university": "json"
    },
    "results": [
    {
        "course": "ABC",
        "stud_id": "S1",
        "university": "uni1"
    },
    {
        "course": "DEF",
        "stud_id": "S2",
        "university": "uni1"
    },
    {
        "course": "ABC",
        "stud_id": "S3",
        "university": "uni1"
    },
    {
        "course": "DEF",
        "stud_id": "S4",
        "university": "uni1"
    }
    ],
    "status": "success",
...

And:


cbq> select name, stud_ids from course;
{
    "requestID": "860968a1-257e-4d8e-b782-bc4120913aec",
    "signature": {
        "name": "json",
        "stud_ids": "json"
    },
    "results": [
    {
        "name": "ABC",
        "stud_ids": []
    },
    {
        "name": "DEF",
        "stud_ids": []
    }
    ],
    "status": "success",
...

Update:

cbq> update course c set stud_ids = (select raw stud_id from student s where university = 'uni1' and s.course = c.name);
{
    "requestID": "d02f6ab9-2609-4303-aebf-683dcade6fbc",
    "signature": null,
    "results": [
    ],
    "status": "success",
...

After:

cbq> select name, stud_ids from course;
{
    "requestID": "c93674ab-c47d-4aa8-a4d7-e126c7c911ee",
    "signature": {
        "name": "json",
        "stud_ids": "json"
    },
    "results": [
    {
        "name": "ABC",
        "stud_ids": [
            "S1",
            "S3"
        ]
    },
    {
        "name": "DEF",
        "stud_ids": [
            "S2",
            "S4"
        ]
    }
    ],
    "status": "success",
...
7

thank you for update. Actually my requirement is to update based on two indexes, so I have to use JOIN, don’t want to use WHERE clause. Based two buckets indexes I have to USE JOIN to update the documents. Do we have any option for update query using JOIN?

8

Can some one help me on the above update query please?

9

Checkout N1QL query to get array of values to a column for all documents from another bucket by joining it