Joining documents based on nested ids inside array

Mobile Couchbase Lite
, ,
1

Lets say we have some documents of type “file” like this:

{
  "_id": "file::001",
  "type": "file",
  "name": "this is a file",
  "tags": [
    {
      "tagId": "tag::001",
      "addedAt": "32095823",
    },
    {
      "tagId": "tag::002",
      "addedAt": "32482931",
    }
  ]
}, ...

and want to join them with the following documents of type “tag”:

{
  "_id": "tag::001",
  "type": "tag",
  "color": "green",
  "name": "this is a green tag"
},
{
  "_id": "tag::002",
  "type": "tag",
  "color": "blue",
  "name": "this is a blue tag"
}

to this expected result:

{
  "_id": "file::001",
  "type": "file",
  "name": "File 1",
  "tags": [
    {
      "tagId": "tag::001",
      "addedAt": "32095823",
      // tags will be joined into this nested property
      "tag": {
        "type": "tag",
        "color": "green",
        "name": "this is a green tag"
      }
    },
    {
      "tagId": "tag::002",
      "addedAt": "32482931",
      "tag": {
        "type": "tag",
        "color": "blue",
        "name": "this is a blue tag"
      }
    }
  ]
}

I’ve been searching the whole day yesterday and couldn’t find any solution to this problem (which seems very basic and common to me). Maybe i’ve been using the wrong keywords, as i’m very new to n1ql. Keep in mind, that i am searching for a Couchbase Lite / Mobile solution.

If anyone could help me write a query or at least nudge me into the right direction with some documentation links, i’d be very thankfull.

2

The only solution i could imagine is to query in two steps:
First the files, then collect all the distinct tagIds, secondly request all the tags by the tagIds.

But this approach feels way too complicated to me and i’d imagine that quering with a large list of tagIds would be slow.

3

You need to fetch the document, no other option.
If you don’t need further processing. Get file and use SDK get the tags using collection.get()

SELECT d.*,
     ARRAY OBJECT_ADD (t, "tag", (SELECT RAW t1 FROM default AS t1 USE KEYS t.tagId)[0]) FOR t IN d.tags END AS tags
FROM default AS d
WHERE d.type ="file";

How many tags you are talking. If reasonable

WITH tagsObj AS ( OBJECT o.n: o.v
                  FOR o IN (SELECT t1._id AS n, OBJECT_REMOVE(t,"_id") AS v 
                            FROM default AS t1 
                            WHERE t1.type = "tag")
                  END)
SELECT d.*,
     ARRAY OBJECT_ADD (t, "tag", tagsObj.[t.tagId]) FOR t IN d.tags END AS tags
FROM default AS d
WHERE d.type ="file";
4

Correct me if i’m wrong, but with what i’ve seen in the documentation OBJECT_ADD and nested queries are not supported on couchbase mobile.

5

Please ignore all my replies. I thought it is Server. Did not see topic Mobile.

1 Like
6

There are a lot of limitations around arrays in the mobile implementation (as well as other limitations in general). I don’t think you will be able to achieve this without going through two steps as you say but as for your concern of

i’d imagine that quering with a large list of tagIds would be slow.

Even if we were doing it under the hood it wouldn’t make it any faster. There is no choice but to query to find each of those tag documents, the only difference is where it happens (inside the large mega query or inside the second sequential query). If you use 3.1 or higher and use collections you might be able to mitigate some of the performance as well by putting your tags in a separate collection.