How to use "USE KEYS" in inner select with QueryBuilder in Java (Android)?

query

#1

Hi there,

Not sure if it is an issue, but i cannot find a way around this:

I have a Task document which has a list of UserIds:
Task



[
  {
    "description": "Description for task Task-21",
    "name": "Task-21",
    "userIds": [
      "1946da8f-bcd9-4a26-a877-397bc9056b3b",
      "84864453-00c6-4746-9945-dd2e0d12b11d",
      "ec83e97d-9532-4413-a92c-f399f3fc5eb2",
      "6de7058f-bf9c-4716-952a-631ff1f47b58"
    ],
    "type": "Task"
  }
]

User

[
  {
    "name": "User1",
    "type": "User"
  }
]

I use the following query in Couchbase Query Editor:

select task., (select users. from bucket users USE KEYS task.userIds) as users
from bucket task
where task.type = “Task”
Which will give me the Task with the array of User objects instead of the IDs:

[
  {
    "description": "Description for task Task-21",
    "name": "Task-21",
    "Users": [
      {
        "name": "User1",
        "type": "User"
      },
      {
        "name": "User2",
        "type": "User"
      },
      {
        "name": "User3",
        "type": "User"
      },
      {
        "name": "User4",
        "type": "User"
      },
    ],
    "type": "Task"
  }
]

How would one convert this into the QueryBuilder.select statement in Java (Android)

Hope someone knows the answer,

Thanks
Denis


#2

There is no USE KEYS in Couchbase Lite. QueryBuilder on CBL supports JOIN queries which are similar to ANSI JOINs. You can accomplish what you need using a JOIN Query.

I would recommend that you check out this blog to get an overview of JOINS in Couchbase Lite. The examples are in Swift but given the unified nature of our QueryBuilder API , it should be straightforward to map this to Java.

Specifically on your question, there is a similar example at the end of the same blog post in the section on using Functions with JOINS. Use that as a reference to implement your query. Basically you will do a JOIN using an ArrayFunction as the join criteria.


#3

Thanks Priya,

I did look into that but the problem with JOIN is that i do not want the Tasks to repeat. As you can see in the example you’ve mentioned Department D1 is being repeated twice as it has two locations.

I guess i will just have to get the User documents individually by looping through the userids array i have in tasks.

Cheers,
Denis