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

Siva · June 25, 2021, 9:42am

I have student and course buckets.

A_course:
[
{
“A_course”: {
“description”: “none”,
“id”: “1001”,
“location”: “uk”,
“name”: “ABC”,
}
},
{
“A_course”: {
“description”: “none”,
“id”: “1002”,
“location”: “uk”,
“name”: “DEF”,
}
},
{
“A_course”: {
“description”: “none”,
“id”: “1003”,
“location”: “uk”,
“name”: “GHI”,
}
}
]
A_student:
[
{
“A_student”: {
“course”: “MS”,
“course_id”: “1001”,
“stud_id”: “S1”,
“stud_name”: “Student1”
}
},
{
“A_student”: {
“course”: “BE”,
“course_id”: “1002”,
“stud_id”: “S2”,
“stud_name”: “Student2”
}
},
{
“A_student”: {
“course”: “BE”,
“course_id”: “1002”,
“stud_id”: “S3”,
“stud_name”: “Student3”
}
},
{
“A_student”: {
“course”: “PHD”,
“course_id”: “1003”,
“stud_id”: “S4”,
“stud_name”: “Student4”
}
}
]

I need output like below:
[
{
“A_course”: {
“description”: “none”,
“id”: “1001”,
“location”: “uk”,
“name”: “ABC”,
“stud_ids”: [
“S1”
]
}
},
{
“A_course”: {
“description”: “none”,
“id”: “1002”,
“location”: “uk”,
“name”: “DEF”,
“stud_ids”: [
“S2”,
“S3”
]
}
},
{
“A_course”: {
“description”: “none”,
“id”: “1003”,
“location”: “uk”,
“name”: “GHI”,
“stud_ids”: [
“S4”
]
}
}
]

I got this output with the help of below three queries.
UPDATE A_course
SET stud_ids = (SELECT RAW s.stud_id FROM A_student s JOIN A_course c ON c.id = s.course_id where s.course_id = “1001”)
where id=“1001”;
UPDATE A_course
SET stud_ids = (SELECT RAW s.stud_id FROM A_student s JOIN A_course c ON c.id = s.course_id where s.course_id = “1002”)
where id=“1002”;
UPDATE A_course
SET stud_ids = (SELECT RAW s.stud_id FROM A_student s JOIN A_course c ON c.id = s.course_id where s.course_id = “1003”)
where id=“1003” ;

but I want single query to get this out put.
Thanks in advance
-Siva

vsr1 · June 25, 2021, 1:28pm

CREATE INDEX ix70 ON student(stud_id,course_id);
CREATE INDEX ix71 ON course(id);
INSERT INTO course VALUES (UUID(), { "description": "none", "id": "1001", "location": "uk", "name": "ABC"});
INSERT INTO course VALUES (UUID(), { "description": "none", "id": "1002", "location": "uk", "name": "DEF"});
INSERT INTO course VALUES (UUID(), { "description": "none", "id": "1003", "location": "uk", "name": "GHI"});

INSERT INTO student VALUES (UUID(), { "course": "MS", "course_id": "1001", "stud_id": "S1", "stud_name": "Student1" });
INSERT INTO student VALUES (UUID(), { "course": "BE", "course_id": "1002", "stud_id": "S2", "stud_name": "Student2" });
INSERT INTO student VALUES (UUID(), { "course": "BE", "course_id": "1002", "stud_id": "S3", "stud_name": "Student3" });
INSERT INTO student VALUES (UUID(), { "course": "PHD", "course_id": "1003", "stud_id": "S4", "stud_name": "Student4" });

N1QL doesn’t have UPDATE JOINS. Also doesn’t support correlated subqueries with out USE KEYS until CB 7.0.
You can use one of the following option.

MERGE statement

MERGE INTO course AS m
USING (SELECT d.course_id, ARRAY_AGG(d.stud_id) AS stud_ids
       FROM student AS d WHERE d.stud_id IS NOT NULL
       GROUP BY d.course_id) AS s
ON m.id = s.course_id
WHEN MATCHED THEN UPDATE SET m.stud_ids = s.stud_ids;

OR

UPDATE statement with FIRST construct with non correlated subquery (subquery materialize results into ARRAY once and iterate the ARRAY to find right id).

UPDATE course AS c
SET c.stud_ids = (FIRST v.stud_ids
                  FOR v IN (SELECT d.course_id, ARRAY_AGG(d.stud_id) AS stud_ids
                           FROM student AS d WHERE d.stud_id IS NOT NULL
                           GROUP BY d.course_id)
                  WHEN v.course_id = c.id
                  END)
WHERE c.id IS NOT NULL;

OR

In CB 7.0 (generic correlated subqueries supported)

UPDATE course AS c
SET c.stud_ids = (SELECT RAW d.stud_id
                  FROM student AS d 
                  WHERE d.stud_id IS NOT NULL AND d.course_id = c.id)
WHERE c.id IS NOT NULL;

Siva · June 25, 2021, 4:43pm

Hi,

That is perfect. It worked .Thank you.

One more thing I need.
In my previous question I missed one column. and above solution is not working. Please consider below data.

-Siva

A_course:
[
{
“A_course”: {
"course_id":“1”,
“description”: “none”,
“id”: “1001”,
“location”: “uk”,
“name”: “ABC”,
}
},
{
“A_course”: {
"course_id":“2”,
“description”: “none”,
“id”: “1002”,
“location”: “uk”,
“name”: “DEF”,
}
},
{
“A_course”: {
"course_id":“3”,
“description”: “none”,
“id”: “1003”,
“location”: “uk”,
“name”: “GHI”,
}
}
]
A_student:
[
{
“A_student”: {
“course”: “MS”,
“course_id”: “1001”,
“stud_id”: “S1”,
“stud_name”: “Student1”
}
},
{
“A_student”: {
“course”: “BE”,
“course_id”: “1002”,
“stud_id”: “S2”,
“stud_name”: “Student2”
}
},
{
“A_student”: {
“course”: “BE”,
“course_id”: “1002”,
“stud_id”: “S3”,
“stud_name”: “Student3”
}
},
{
“A_student”: {
“course”: “PHD”,
“course_id”: “1003”,
“stud_id”: “S4”,
“stud_name”: “Student4”
}
}
]

I need output like below:
[
{
“A_course”: {
“course_id”:“1”,
“description”: “none”,
“id”: “1001”,
“location”: “uk”,
“name”: “ABC”,
“stud_ids”: [
“S1”
]
}
},
{
“A_course”: {
“course_id”:“2”,
“description”: “none”,
“id”: “1002”,
“location”: “uk”,
“name”: “DEF”,
“stud_ids”: [
“S2”,
“S3”
]
}
},
{
“A_course”: {
“course_id”:“3”,
“description”: “none”,
“id”: “1003”,
“location”: “uk”,
“name”: “GHI”,
“stud_ids”: [
“S4”
]
}
}
]

vsr1 · June 25, 2021, 4:48pm

It will work. Please post exact query you tried. Note: You have relation A_course.id (NOT A_course.course_id) to A_student.course_id

Also you need to change INSERT of course add course_id values.

Siva · June 25, 2021, 5:57pm

Thank you for the reply it is working. This is sample data I gave and I didn’t tested before asking. Sorry for that.

But my actual data is like below. similar to previous data. But nested data.
[
{
“b1”: {
“Description”: “S”,
“EID”: “E001”,
“OUTPUT”: {
"AEC": “SL001”,
“E_Type”: “”,
“Update_date”: “”
},
“P”: “RL”,
“INPUT”: {
“BEC”: “AN002”
}
}
},
{
“b1”: {
“Description”: “P”,
“EID”: “E002”,
“OUTPUT”: {
"AEC": “LLAN001”,
“E_Type”: “”,
“Update_date”: “”
},
“P”: “RL”,
“INPUT”: {
“BEC”: “TEP_03”
}
}
}
]

[
{
“b2”: {
“MID”: “”,
“OUTPUT”: {
“IN1 BDS”: “”,
“IN2”: “INA”,
“IN3”: “”
},
“PID”: “P0001”,
“INPUT”: {
"AEC": “LLAN001”,
“Others1”:“INFO”,
“Others1”: “”
},
“Update date”: “”
}
},
{
“b2”: {
“MID”: “”,
“OUTPUT”: {
“IN1”: “PA”,
“IN2”: “Il”,
“IN3”: “SL001_02”
},
“PID”: “P0002”,
“INPUT”: {
"AEC": “SL001”,
“Others1”: “INFO”,
“Others1”: “”
},
“Update date”: “”
}
},
{
“b2”: {
“MID”: “”,
“OUTPUT”: {
“IN1”: “PA”,
“IN2”: “Il”,
“IN3”: “SL001_02”
},
“PID”: “P0003”,
“INPUT”: {
"AEC": “SL001”,
“Others1”: “INFO”,
“Others1”: “”
},
“Update date”: “”
}
}
]

Query I used :
MERGE INTO b1 AS buc1
USING (select INPUT.AEC, ARRAY_AGG(PID) as PIDs
from b2 AS d WHERE d.PID IS NOT NULL
group by d.INPUT.AEC) AS buc2
ON buc1.OUTPUT.AEC = buc2.INPUT.AEC
WHEN MATCHED THEN UPDATE SET buc1.PIDs = buc2.PIDs;

Query executing, But Not updating b1.

-Siva

vsr1 · June 25, 2021, 6:31pm

@Siva ,

buc2 is subquery alias . Only projected columns in subquery will be there . SELECT INPUT .AEC ; As you selecting nested field it becomes AEC not nested object. So u need to change ON clause buc2.AEC

MERGE INTO b1 AS buc1
USING (select d.`INPUT` .AEC, ARRAY_AGG(d.PID) as PIDs
from b2 AS d WHERE d.PID IS NOT NULL
group by d. `INPUT` .AEC) AS buc2
ON buc1.OUTPUT.AEC = buc2.AEC
WHEN MATCHED THEN UPDATE SET buc1.PIDs = buc2.PIDs;

Siva · June 28, 2021, 9:29am

Hi,

Thank you for your reply.
This is working fine.
And thank you for your explanation of subquery projected columns (No nested column is selected )

-Siva