How can I JOIN array property values in 2 document types

SQL++
,
1

Hi Couchbase Gurus,

Need help. How can I JOIN array properties values in 2 document types.

Heres my details below.

locations field:

"locations": [
  "location::001",
  "location::002"
]

location documents

[
   {
       " id":  "location::001",
       "name": "Location Name 1",
       "address": "Address 01",
       "territory":  "territory::501",
   },
   {
      "name": "Location Name 2",
       "address": "Address 02",
      " id":  "location::002",
      "territory:  "territory::505",
   },
]

territory documents:

[
   {
       "id": "territory::501",
       "name": "Territory Name 1"",
   },
   {
       "id": "territory::505",
       "name": "Territory Name 2",
   },
]

expected output:

"id": "task::6daa6",
"name": "test title",
"status": "pending",
"locations": [
   {
       "id"": location::001",
       "name": "Location Name 1",
       "address": "Address 01",
        "territory": {
           id:  "territory::501",
           "name": "Territory Name 2",
        } 
   },
   {
       "id": "location::002",
        "name": "Location Name 2",
         "address": "Address 02",
         "territory":  {
            "id": "territory::505",
           "name": "Territory Name 2",
         },
   },
]

My Initial Query:

SELECT
  meta(`tasks`).id,
  `tasks`.name,
  `tasks`.status,
   locationx AS locations
FROM `bucket` AS tasks
USE KEYS ['task::6daa6']

LEFT NEST `bucket` AS locs
  ON META(locs).id IN tasks.locations
  AND locs.`type` = 'location'
  AND locs.tenant = 'tenant::01' 
    
LEFT JOIN `bucket` AS `users`
  ON META(`users`).id = `tasks`.createdBy
  AND `users`.`type` = 'user'
  AND `users`.tenant = 'tenant::01'

LET locationx = (ARRAY { META(loc).id, loc.name, loc.address, loc.imagePlaceholderColor, loc.territory } FOR loc IN locs END)

WHERE tasks.`type` = 'task'
AND tasks.tenant = 'tenant::o1'

Sincerely,

2 
SELECT
  meta(`tasks`).id,
  `tasks`.name,
  `tasks`.status,
   (SELECT locs.*,
           (SELECT META(t).id, t.name
            FROM  `bucket` AS t USE KEYS locs.territory)[0] AS territory
    FROM `bucket` AS locs USE KEYS tasks.locations
    WHERE locs.`type` = 'location' AND locs.tenant = 'tenant::01') AS locations
FROM `bucket` AS tasks USE KEYS ['task::6daa6']
WHERE tasks.`type` = 'task' AND tasks.tenant = 'tenant::01';