N1QL specific query

SQL++
1

Hi, I have to make a specific query with N1QL and I want to figure out how to structure it.

Let’s say I have two kinds of objects, project and task:
project:
{
id: “sdfsdf”,
…
}
task:
{
project_id: “dfsdf”,
completed: true,
}

I want to perform a query that selects all the projects and for each one returns the % of tasks completed as a specific property in the object project, let’s call it ‘completed’.
How can I do this?

2

select p.*, count(t) * 100 / sum(case when t.completed then 1 else 0 end) as pct_completed
from task t join project p on keys t.project_id
group by p
;

3

Thank you for the reply. Is this query supposed to run in a system with two buckets, one for project and one for tasks? Because at the moment there’s only a bucket with every object inside. I categorize objects using a ‘type’ field in any of them. Is this a good approach for the future? Or i need to use one bucket per type? I want to be able to manage a lot of different objects.

4

Hi,

You can use either multiple buckets or a single bucket. If you want to manage a lot of different objects, you should probably use a single bucket, so as not to hit the bucket count limit.

The query doesn’t change much. You just add clauses of the form WHERE t.type = … AND p.type = …

You should also add an index on type.

5

Ok, thank you very much!

6

I’ve noticed that the query above returns all the projects related to at least one task. So if I have a project with id ‘x’ and no task in my bucket has the property project_id set to ‘x’, this project cannot be returned by this query.
I also noticed that there’s no way to use a RIGHT JOIN in N1QL right now, that would solve the problem, only INNER or LEFT (OUTER)…

Any help?

7

Good catch.

You can do a UNION to pick up the other projects.

Even better, can you put an array of task_ids inside of each project? In that case, your query can start with project and then LEFT OUTER JOIN to tasks.

You are right, N1QL does not support RIGHT OUTER JOINs. Lmk if adding array of task_ids is an option for you. We want to encourage users to model their data to facilitate performance. One of the benefits of JSON is the possibility of bidirectional references.