Join documents by using array properties

SQL++
,
1

Hi there,

I’m stucking with the join operation by using a property contained into an array of objects.
I have these documents:

{
    "type": "project",
    "id": "project-1",
    "lines": [
        {
            "id": "line-1",
            "name": "Name Line 1",
            "length": 20.0
        },
        {
            "id": "line-2",
            "name": "Name Line 2",
            "length": 24.0
        }
    ]
}

{
    "type": "section",
    "id": "section-1",
    "lineId": "line-1",
    "name":"name-section01"
}

I would like to write a query which has this result:

{
    "type": "project",
    "id": "project-1",
    "lineSections": [
        {
            "lienId": "line-1",
            "lineName": "Name Line 1",
            "lineLength": 20.0,
            "sectionId": "section-1",
            "sectionName": "name-section01"
        },
        {
            "id": "line-2",
            "name": "Name Line 2",
            "length": 24.0
        }
    ]
}

Here is my attempt, which returns an empty array

SELECT *
FROM `data` AS prj
LEFT JOIN `data` AS sct ON prj.lines[*].id = sct.id
WHERE prj.type = `project` AND sct.type = `section`

Can anyone give me an help?
Thank you.

2

Using 7.0

SELECT prj.type, prj.id,
      (SELECT l.id  AS lineId, l.name  AS lineName, l.length AS lineLength, sct.id AS sectionId, sct.name AS sectionName
       FROM prj.lines AS l  
       LEFT JOIN data AS sct ON  l.id = sct.lineId AND  sct.type = "section") AS lineSections
FROM `data` AS prj
WHERE prj.type = "project"
3

Thank you! I’m learning the details of ANSI JOIN altough the subquery for the ‘lineSection’ properties gives me an error when executed.

Unable to run subquery - cause: FROM in correlated subquery must have USE KEYS clause: FROM data.

I’m using Couchbase v6.6. Could this be the cause?

4

Yes.

Try this

SELECT prj.type, prj.id,
      ARRAY_AGG( {"lineid": l.id, "lineName": l.name , "lineLength":l.length,  "sectionId":sct.id, "sectionName": sct.name }) AS lineSections
FROM `data` AS prj
LEFT UNNEST prj.lines l
LEFT JOIN data AS sct ON  l.id = sct.lineId AND  sct.type = "section"
WHERE prj.type = "project"
GROUP BY prj
5

Thank you so much. I was missing the GROUP BY logic in a NO-SQL engine.