Nest queried documents

SQL++
1

Hi, is there a way to nest documents queried from different buckets? Let’s have an example.

On bucket A we have a doc like this:

ID: master_doc
{ 
  "a": "b",
  "c": "d"
}

On bucket B we have documents like ;

ID: detail_doc_1
{
   "parent_id": "master_doc",
   "detail": "detail 1"
}

ID: detail_doc_2
{
   "parent_id": "master_doc",
   "detail": "detail 2"
}

I’d like to have a query result like that:

{ 
  "a": "b",
  "c": "d"
  "details": [ { "detail": "detail 1" },
               { "detail": "detail 2" } ]
}

Thanks.

P

2

try this

SELECT b.a,b.c,ARRAY_AGG({"detail":a.detail}) AS details
  FROM bucketB a JOIN bucketA b ON KEYS (a.parent_id) 
 GROUP BY b.a,b.c
3

In addition to @atom_yang’s answer, see the N1QL NEST operator, which directly does what you are asking.

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/from.html

4

is this the simplest one for using NEST?

CREATE INDEX idx ON bucketB( parent_id );

SELECT a.*,ARRAY {"detail":i.detail} FOR i IN b END AS details  
  FROM bucketA a  NEST bucketB b ON KEY (b.parent_id) FOR a

another version:

SELECT b[0].*,ARRAY_AGG({"detail":a.detail}) AS details
  FROM bucketB a  NEST bucketA b ON KEYS (a.parent_id)
 GROUP BY b
5

Thanks @geraldss & @atom_yang, I’ll give a try to your suggested solutions and language reference and let you know which best fits my needs.

1 Like
6

You may not need ARRAY_AGG() and GROUP BY with NEST.

7

Ok, for a simple nesting case the NEST statement is the fastest and cleaner solution:

My real use case has two nested levels: a TOUR object refers to multiple STEP objects (TOUR contains STEPs id array and STEP document contains TOUR parent_id property) and each of them can refer multiple TASK objects (TASK contains TOURs parent_id property), this is a sample model:

-- TOP LEVEL
INSERT INTO tour (key, value) VALUES ("TOUR_QUERY_TEST_1", {"title" : "Test 1", "desc" : "Query test 1", "steps": ["STEP_QT_1_1","STEP_QT_1_2"]});
INSERT INTO tour (key, value) VALUES ("TOUR_QUERY_TEST_2", {"title" : "Test 2", "desc" : "Query test 2", "steps": ["STEP_QT_2_1","STEP_QT_2_2","STEP_QT_2_3"]});
-- FIRST NESTED LEVEL
INSERT INTO step (key, value) VALUES ("STEP_QT_1_1", {"title" : "Test 1-1", "desc" : "Query test 1-1", "parent_id": "TOUR_QUERY_TEST_1"});
INSERT INTO step (key, value) VALUES ("STEP_QT_1_2", {"title" : "Test 1-2", "desc" : "Query test 1-2", "parent_id": "TOUR_QUERY_TEST_1"});
INSERT INTO step (key, value) VALUES ("STEP_QT_2_1", {"title" : "Test 2-1", "desc" : "Query test 2-1", "parent_id": "TOUR_QUERY_TEST_2"});
INSERT INTO step (key, value) VALUES ("STEP_QT_2_2", {"title" : "Test 2-2", "desc" : "Query test 2-2", "parent_id": "TOUR_QUERY_TEST_2"});
INSERT INTO step (key, value) VALUES ("STEP_QT_2_3", {"title" : "Test 2-3", "desc" : "Query test 2-3", "parent_id": "TOUR_QUERY_TEST_2"});
-- SECOND NESTED LEVEL
INSERT INTO task (key, value) VALUES ("TASK_QT_1_1_1", {"title" : "Test 1-1-1", "desc" : "Query test 1-1-1", "parent_id": "STEP_QT_1_1"});
INSERT INTO task (key, value) VALUES ("TASK_QT_1_1_2", {"title" : "Test 1-1-2", "desc" : "Query test 1-1-2", "parent_id": "STEP_QT_1_1"});
INSERT INTO task (key, value) VALUES ("TASK_QT_1_2_1", {"title" : "Test 1-2-1", "desc" : "Query test 1-2-1", "parent_id": "STEP_QT_1_2"});
INSERT INTO task (key, value) VALUES ("TASK_QT_2_1_1", {"title" : "Test 2-1-1", "desc" : "Query test 2-1-1", "parent_id": "STEP_QT_2_1"});
INSERT INTO task (key, value) VALUES ("TASK_QT_2_2_1", {"title" : "Test 2-2-1", "desc" : "Query test 2-2-1", "parent_id": "STEP_QT_2_2"});

Thanks to @geraldss solution to this topic I’ve been able to get the solution to my real case:

SELECT OBJECT_ADD(s.tour, "steps_docs", ARRAY_AGG(s.step)) as tour
FROM
(
    SELECT tours as tour, OBJECT_ADD(steps, "tasks_docs", ARRAY_AGG(tasks)) AS step
    FROM tour tours
    LEFT OUTER JOIN step steps ON KEY steps.parent_id FOR tours
    LEFT OUTER JOIN task tasks ON KEY tasks.parent_id FOR steps
    GROUP BY tours, steps
) AS s
GROUP BY s.tour;

I haven’t been able to find a solution with NEST, is it eventually possible?

8

I’m not sure if NEST is possible for your full example, but we are aware of a design issue with NEST. There is ambiguity / conflict about whether the NEST alias refers to the individual RHS elements or to the whole RHS array. This may or may not affect whether NEST can be used for your full example.