Get META().id of JOINed bucket as keys

SQL++
,
1

Hello,

I have created a query where I would call a list of “followers”, and for an array of them, I want to join the “user_account” type information. I have been able to retrieve the results I want, however I would like to have the META().id of the JOINed table as the key for each user below.

My current query looks like:

select users FROM (SELECT `default` m, mbrs
FROM `default`
LET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(default.followers) END
WHERE default._type = "followers"AND
default._id = 1) as q JOIN default users
ON KEYS q.mbrs;

My documents structure:

Document Type Followers (“user_account::6::followers”):


{
  "_id": 1,
  "_type": "followers",
  "created_at": 1472741410,
  "followers": {
    "user_account::1": {
      "status": 0,
      "created_at": 1472741410
    },
    "user_account::3": {
      "status": 0,
      "created_at": 1472741410
    }
  }
}

Document Type User Account (“user_account::1”):

{
  "_id": 1,
  "_type": "user_account",
  "name": "João Carlos",
  "active": 1,
  "updated_at": 1472741410,
  "created_at": 1472741410
}

Expected output:

{
  "user_account::1": {
    {
      "_id": 1,
      "_type": "user_account",
      "name": "João Carlos",
      "active": 1,
      "updated_at": 1472741410,
      "created_at": 1472741410
    }
  },
  "user_account::3": {
    {
      "_id": 3,
      "_type": "user_account",
      "name": "Pedro Silva",
      "active": 1,
      "updated_at": 1472741410,
      "created_at": 1472741410
    }
  }
}

Instead of “user_account::X” keys I am getting “users” as each key (obviously, as it is in the query).

What am I missing to get keys to show up as document keys?

Thanks!

2

Hi @ivoecpereira,

select {meta(users).id : users} d FROM (SELECT default m, mbrs
FROM default
LET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(default.followers) END
WHERE default._type = "followers"AND
default._id = 1) as q JOIN default users
ON KEYS q.mbrs;

Please note that ON KEYS is array and it can produce multiple users and can have duplicates in result. If you interested each LEFT document as separate user object try the following.

select q.m, OBJECT v.id:v.v FOR v IN ARRAY_AGG({“id”:meta(users).id, “v”:users}) END FROM (SELECT default m, mbrs
FROM default
LET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(default.followers) END
WHERE default._type = "followers"AND
default._id = 1) as q LEFT JOIN default users
ON KEYS q.mbrs group by g.m;

3

In both queries, you can make the following change. Instead of

LET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(default.followers) END

You can use

LET mbrs = OBJECT_NAMES(default.followers)

4

Hey @vsr1,

Thanks for your reply.

I am afraid the query you provided does not work:

select {meta(users).id : users} d FROM (SELECT default m, mbrs
FROM default
LET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(default.followers) END
WHERE default._type = "followers"AND
default._id = 1) as q JOIN default users
ON KEYS q.mbrs;

The error:

[
  {
    "code": 3000,
    "msg": "syntax error - at meta",
    "query_from_user": "select {meta(users).id : users} d FROM (SELECT default m, mbrs\nFROM default\nLET mbrs = OBJECT_NAMES(default.followers)\nWHERE default._type = \"followers\"AND\ndefault._id = 1) as q JOIN default users\nON KEYS q.mbrs;"
  }
]

Personally I’ve never saw that use of {} in SELECT expressions so I can’t really debug what should be wrong about it.

Your second query throws an error too in the last line about an ambiguous reference to the field g. However I wasn’t even able to find wherever the “g” field was declared.

[
  {
    "code": 3000,
    "msg": "Ambiguous reference to field g.",
    "query_from_user": "select q.m, OBJECT v.id:v.v FOR v IN ARRAY_AGG({\"id\":meta(users).id, \"v\":users}) END FROM (SELECT default m, mbrs\nFROM default\nLET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(default.followers) END\nWHERE default._type = \"followers\"AND\ndefault._id = 1) as q LEFT JOIN default users\nON KEYS q.mbrs group by g.m;"
  }
]

I guess you mistyped it, and tried with a group by q.m; instead of group by g.m;, but the following error was thrown:

[
  {
    "code": 4210,
    "msg": "Expression must be a group key or aggregate: object (`v`.`id`) : (`v`.`v`) for `v` in array_agg({\"id\": (meta(`users`).`id`), \"v\": `users`}) end",
    "query_from_user": "select q.m, OBJECT v.id:v.v FOR v IN ARRAY_AGG({\"id\":meta(users).id, \"v\":users}) END FROM (SELECT default m, mbrs\nFROM default\nLET mbrs = ARRAY m.name FOR m IN OBJECT_PAIRS(default.followers) END\nWHERE default._type = \"followers\"AND\ndefault._id = 1) as q LEFT JOIN default users\nON KEYS q.mbrs group by q.m;"
  }
]

Any ideas?

@geraldss thanks! Once I get the main query working I will change that!

5

@ivoecpereira, The earlier syntax works in 4.5.1. What is version of CB?

Check if this works.

SELECT OBJECT v.id:v.v FOR v IN ARRAY_AGG({“id”:meta(users).id , “v”:users}) END AS d FROM default m JOIN default users ON KEYS
OBJECT_NAMES(m.followers) WHERE m._type = “followers” AND m._id = 1 group by m;

SELECT OBJECT v.id:v.v FOR v IN q.d END AS d FROM
(SELECT ARRAY_AGG({“id”:meta(users).id , “v”:users}) d FROM default m JOIN default users ON KEYS
OBJECT_NAMES(m.followers) WHERE m._type = “followers” AND m._id = 1 group by m) q;

6

I am using CB 4.1 on my production server. However I am testing some queries on 4.5 locally (so far so good. The ones that are working in one, are working in another).

SELECT OBJECT v.id:v.v FOR v IN ARRAY_AGG({"id":meta(users).id , "v":users}) END AS d FROM default m JOIN default users ON KEYS
OBJECT_NAMES(m.followers) WHERE m.type = "user_account_followers" AND m.id = 1 group by m;

throws:

[
  {
    "code": 4210,
    "msg": "Expression must be a group key or aggregate: object (`v`.`id`) : (`v`.`v`) for `v` in array_agg({\"id\": (meta(`users`).`id`), \"v\": `users`}) end as `d`",
    "query_from_user": "SELECT OBJECT v.id:v.v FOR v IN ARRAY_AGG({\"id\":meta(users).id , \"v\":users}) END AS d FROM default m JOIN default users ON KEYS\nOBJECT_NAMES(m.followers) WHERE m.type = \"followers\" AND m.id = 1 group by m;"
  }
]

The second one did worked! The collection name is named as “d”. Is it much complicated to just get an array of objects instead of having an additional object layer?

Apart of that, excellent! Thanks a lot once more @vsr1!

EDIT: Against me I am gonna talk. As I’ve said, I was testing it on 4.5 and it worked like a charm, however it seems to don’t work in 4.1 unfortunately.