Couchbase join between two buckets using array of keys

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
{

}

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

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

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”
}
{

}

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.

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",
...

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?

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

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