Multiple arrays of objects to nest/join data with

SQL++
1

Hello. I’ve been silently watching these forums for many months, but have finally come across an issue I can’t seem to pass, no matter how much I search for answers.

I have a “team” document (which I specify via USE KEY):

{
  "name": "Test",
  "members": [{
    "role": 3,
    "userID": "abc123"
  }, {
    "role": 1,
    "userID": "abcd1234"
  }],
  "projects": [{
    "access": 5,
    "projectID": "321cba"
  }]
}

Then subsequent documents for “member”:

{
  "_id": "abc123",
  "name": "Bob",
  "password": "abc"
}

{
  "_id": "abcd1234",
  "name": "Fred",
  "password": "cba"
}

And “project”:

{
  "_id": "321cba",
  "title": "Project 1"
}

I’m trying to make it return it like so:

{
  "name": "Test",
  "members": [{
    "role": 3,
    "user": {
      "_id": "abc123",
      "name": "Bob"
    }
  }, {
    "role": 1,
    "user": {
      "_id": "abcd1234",
      "name": "Fred"
    }
  }],
  "projects": [{
    "access": 3,
    "project": {
      "_id": "abc123",
      "title": "Project 1"
    }
  }]
}

I am able to get one or the other to work, but not together, since it aggregates both sets of arrays together (so if I have 3 members, and 1 project, it’ll actually put 3 duplicate projects instead of 1):

SELECT t.*, ARRAY_AGG({ 'role': tm.`role`, '_id': m.id, 'name': m.name }) members
FROM default t USE KEYS 'teamtest'
UNNEST t.members AS tm
LEFT JOIN default m ON KEYS tm.userID
GROUP BY t

But as soon as I try to unnest “projects”, is when everything goes wrong and I start getting duplicates. I’ve tried LEFT / OUTER joins, unnest, everything. I’ve tried doing LET variables that combine the data… but that only yielded the member / project data… and not the original data that’s part of team (I was losing the member’s “role” field and the project’s “access” field).
Any help would be so much appreciated. I’ve spent hours on this, and I fear that I may have to reconstruct my data layer to make this possible :frowning:
Thanks ahead of time

2

this might be not the best,but it should work. FYI

SELECT a.name,a.members,ARRAY_AGG({"access":a.`access`,"project":a.project}) AS projects
  FROM (
        SELECT tuaa.name,tuaa.members,tuaap.`access`, {"_id":META(p).id,"title":p.title} AS project
          FROM (
                  SELECT tua.name,tua.projects,ARRAY_AGG({"role":tua.user_role,"user":tua.`user`}) AS members
                  FROM (
                        SELECT t.name, {"_id": META(u).id, "name": u.name} AS `user`, tm.`role` AS user_role,t.projects
                          FROM default t USE KEYS ["teamtest"]  unnest t.members tm
                          JOIN default u ON KEYS (tm.userID)  
                  ) tua
                  GROUP BY tua.name,tua.projects
          ) tuaa UNNEST tuaa.projects tuaap
          JOIN default p ON KEYS (tuaap.projectID)  
  ) a
  GROUP BY a.name,a.members
3

Try this out.

SELECT  t.name,
 ARRAY FIRST {v.`role`, "user": {m._id, m.name}} FOR m IN mbrs WHEN v.userID = m._id END FOR v IN t.members END AS members,
 ARRAY FIRST {v.`access`, "project": {p._id, p.title}} FOR p IN projs WHEN v.projectID = p._id END FOR v IN t.projects END AS projects
FROM default t USE KEYS ["teamtest"]
LET mbrs = (SELECT RAW m FROM default m USE KEYS t.members[*].userID),
projs = (SELECT RAW p FROM default p USE KEYS t.projects[*].projectID);
4

your N1QL is better, and the following rewrite N1QL works:

SELECT  t.name,
 ARRAY FIRST {"role":v.`role`, "user": {"_id":m.id, "name":m.name}} FOR m IN mbrs WHEN v.userID = m.id END FOR v IN t.members END AS members,
 ARRAY FIRST {"access":v.`access`, "project": {"_id":p.id, "title":p.title}} FOR p IN projs WHEN v.projectID = p.id END FOR v IN t.projects END AS projects
FROM default t USE KEYS ["teamtest"]
LET mbrs = (SELECT META(m).id,m.name FROM default m USE KEYS t.members[*].userID),
projs = (SELECT META(p).id,p.title FROM default p USE KEYS t.projects[*].projectID);
5

Amazing! It worked so well.
Thank you both for your time. I really appreciate it!