How can I join and match 2 array fields values inside LET Clause

SQL++
,
1

Hi Guys,

   Need help, how can I join  and match 2 array fields values inside LET Clause.

// here is my query

SELECT meta(`users`).id, territories, locations
FROM `bucket` AS `users`
LET territories = (
   SELECT META().id, name
   FROM `bucket`
   USE KEYS `users`.territories
),
locations = (
   SELECT META(`location`).id, `location`.name, `location`.territory
   FROM `bucket` `location`
   WHERE `location`.type = 'location'
)
WHERE META().id = 'userId::9b8c'

// query result of the above query:

[
   {
      "id": "userId::9b8c",
      "territories": [
          {
            "id": "territory::0021",
            "name": “T0021"
          },
          {
            "id": "territory::0012",
            "name": “T0012"
          }
      ]
   }
]

// the docs want to be join and match.

"locations": [
      {
        "id": "location::02”,
        "name": "Location 25”,
        "territory": "territory::0021"
      },
      {
        "id": "location::09”,
        "name": "Location 46”,
        "territory": "territory::0058”
      },
      {
        "id": "location::56”,
        "name": “Location 42”,
        "territory": "territory::0012"
      }
]

EXPECTED OUTPUT:

[
   {
      "id": "userId::9b8c",
      "territories": [
          {
            "id": "territory::0021",
            "name": “T0021"
          },
          {
            "id": "territory::0012",
            "name": “T0012"
          }
      ],
      "locations": [
          {
            "id": "location::02”,
            "name": "Location 25”,
            "territory": "territory::0021"
          },
         {
            "id": "location::56”,
            "name": “Location 42”,
            "territory": "territory::0012"
         }
       ]
   }
]

Thanks guys, appreciated.

2 
SELECT META(u).id, ARRAY_AGG(t) AS territories, ARRAY_AGG({META(l).id, l.name, l.territory}) AS locations
FROM `bucket` AS u USE KEYS 'userId::9b8c'
UNNEST (SELECT META(t).id, name FROM `bucket` t USE KEYS u.territories) AS t
JOIN `bucket` AS l ON t.id = l.territory
GROUP BY META(u).id;
3

Hi @vsr1,

I have an error:

[
  {
    "code": 4330,
    "msg": "No index available for ANSI join term l",
    "query": "SELECT \n  META(u).id,\n  ARRAY_AGG(t) AS territories, \n  ARRAY_AGG({META(l).id, l.name, l.territory}) AS locations\n  \nFROM `bucket` AS u USE KEYS 'user::53fe5737-9b8c-4c8b-b9d7-87521055b995'\n\nUNNEST (SELECT META(t).id, name FROM `bucket` t USE KEYS u.territories) AS t\n\n\nJOIN `bucket` AS l ON t.id = l.territory\n\nGROUP BY META(u).id;"
  }
]
4

Check this out https://blog.couchbase.com/ansi-join-support-n1ql/
CREATE INDEX ix1 ON bucket (territory, name);

5

Hi @vsr1,

Thanks for your immediate response. I resolved the INDEX issue thanks.
But, the output I was expecting was different. :frowning:

My expected output would be:

{
    "territories": [
          {
            "id": "territory::0021",
            "name": “T0021"
          },
          {
            "id": "territory::0012",
            "name": “T0012"
          }
      ],
     "locations": [
        {
           "id": "location::02”,
           "name": "Location 25”,
           "territory": "territory::0021"
        },
        {
           "id": "location::56”,
           "name": “Location 42”,
           "territory": "territory::0012"
        }
   ]
}

I have to match the locations that is under a territory.

This the location documents

"locations": [
      {
        "id": "location::02”,
        "name": "Location 25”,
        "territory": "territory::0021"
      },
      {
        "id": "location::09”,
        "name": "Location 46”,
        "territory": "territory::0058”
      },
      {
        "id": "location::56”,
        "name": “Location 42”,
        "territory": "territory::0012"
      }
]

and the user field is just and additional field

6

You should get results what you expected. What are u geeting

7

Hi @vsr1,

Sorry for the late reply,

I’ve run the query, but I gives me a correlated subquery error

SELECT META(u).id, territories
FROM `bucket` AS u USE KEYS 'userId::9b8c'
LET territories = (SELECT META(t).id AS territory, t.name AS territoty_name,
                          META(l).id AS location, l.name AS location_name
                   FROM `bucket` t USE KEYS u.territories
                   JOIN `bucket` AS l ON t.id = l.territory );

[
  {
    "code": 5370,
    "msg": "Unable to run subquery - cause: FROM in correlated subquery must have USE KEYS clause: FROM bucket.",
    "query": "SELECT META(u).id, territories\nFROM `bucket` AS u USE KEYS 'bucket::user::9b8c-4c'\nLET territories = (\n  SELECT\n    META(t).id AS territory, t.name AS territory_name,\n    META(l).id AS location, l.name AS location_name\n  FROM `bucket` t USE KEYS u.territories\n  JOIN `bucket` AS l ON t.id = l.territory )"
  },
  {
    "code": 5010,
    "msg": "Error evaluating LET. - cause: FROM in correlated subquery must have USE KEYS clause: FROM bucket."
  }
]
8 
INSERT INTO default VALUES ("userId::9b8c", {"territories":["territory::0021","territory::0012"]});
INSERT INTO default VALUES ("territory::0021", {"name":"T0021"});
INSERT INTO default VALUES ("territory::0012", {"name":"T0012"});
INSERT INTO default VALUES ("location::02", { "id": "location::02", "name": "Location 25", "territory": "territory::0021" });
INSERT INTO default VALUES ("location::09", { "id": "location::09", "name": "Location 46", "territory": "territory::0058" });
INSERT INTO default VALUES ("location::56", { "id": "location::56", "name": "Location 42", "territory": "territory::0012" });

CREATE INDEX ix1 ON default (territory, name);

SELECT META(u).id, ARRAY_AGG(t) AS territories, ARRAY_AGG({META(l).id, l.name, l.territory}) AS locations
FROM `default` AS u USE KEYS 'userId::9b8c'
UNNEST (SELECT META(t).id, name FROM `default` t USE KEYS u.territories) AS t
JOIN `default` AS l ON t.id = l.territory
GROUP BY META(u).id;


[
    {
        "id": "userId::9b8c",
        "locations": [
            {
                "id": "location::02",
                "name": "Location 25",
                "territory": "territory::0021"
            },
            {
                "id": "location::56",
                "name": "Location 42",
                "territory": "territory::0012"
            }
        ],
        "territories": [
            {
                "id": "territory::0012",
                "name": "T0012"
            },
            {
                "id": "territory::0021",
                "name": "T0021"
            }
        ]
    }
    ]