Couchbase query for unnest and join together

SQL++
1

I am trying to write a query in which I want to use unnest and join together.

2

This works with current versions:

WITH a AS ([{"id":1,"subids":[2,3,4,5]}]), b AS ([{"id":2},{"id":3}])
SELECT a.id, joinid
FROM a
UNNEST a.subids AS joinid
JOIN b ON joinid = b.id ;

HTH.

3

ANSI JOIN Support in N1QL - The Couchbase Blog Example 12, 13,14

4

Query with unnest, join and agrregate.
For example : I have to find the lessons that have not been created. I want lessonplans = in case of missing lessons but I am getting records of only those in which lessons have been created from the below query.
My query is:

SELECT META(classmap).id,classmap.className,subDetails.section,subDetails.subject,
subDetails.teacherName,lessonplan
FROM `school` AS lessonplan
JOIN `school` AS classmap
ON KEYS lessonplan.lessonInfo.classId
JOIN `school` AS classcalendar
ON KEYS lessonplan.lessonInfo.classCalId
UNNEST classmap.classSectionTeachers.classSectionTeacher subDetails
WHERE classmap._class='com.xtremesoftech.eims.classmap.model.ClassMap'
AND lessonplan._class= 'com.xtremesoftech.eims.lessonplanning.model.LessonPlan'
AND classcalendar._class= 'com.xtremesoftech.eims.classcalendar.model.ClassCalendar'
AND classmap.wing='Sr. Secondary'
AND subDetails.subject = lessonplan.lessonInfo.subject
AND subDetails.section IN lessonplan.lessonInfo.sections
AND classcalendar.academicYear='2022-2023'
AND ANY classDay IN classcalendar.classDay SATISFIES classDay.id BETWEEN lessonplan.lessonInfo.startId AND lessonplan.lessonInfo.endId AND classDay.classDayDate >= '2022-07-11' AND classDay.classDayDate < '2022-07-22' END;
5

There is no lessibplans.
see LEFT JOIN, LEFT UNNEST helps
or use sub query (even nested) in projection vs JOIN

CREATE INDEX ix1 ON school(wing) WHERE _class = "com.xtremesoftech.eims.classmap.model.ClassMap";
CREATE INDEX ix2 ON school(lessonInfo.classCalId) WHERE _class = "com.xtremesoftech.eims.lessonplanning.model.LessonPlan";

SELECT META(classmap).id, classmap.className, subDetails.section, subDetails.subject, subDetails.teacherName, lessonplan
FROM school AS classmap
LEFT JOIN school AS lessonplan ON (META(classmap).id = lessonplan.lessonInfo.classCalId
                                   AND lessonplan._class= "com.xtremesoftech.eims.lessonplanning.model.LessonPlan")
LEFT JOIN school AS classcalendar ON (lessonplan.lessonInfo.classCalId = META(classcalendar).id
                                      AND classcalendar.academicYear="2022-2023"
                                      AND ANY classDay IN classcalendar.classDay
                                          SATISFIES classDay.id BETWEEN lessonplan.lessonInfo.startId AND lessonplan.lessonInfo.endId
                                                    AND classDay.classDayDate >= "2022-07-11" AND classDay.classDayDate < "2022-07-22" END)
LEFT UNNEST (ARRAY v FOR v IN classmap.classSectionTeachers.classSectionTeacher
             WHEN v.subject = lessonplan.lessonInfo.subject AND v.section IN lessonplan.lessonInfo.sections END) AS subDetails
WHERE classmap._class = "com.xtremesoftech.eims.classmap.model.ClassMap"
      AND classmap.wing = "Sr. Secondary";