Querying documents from 2 different buckets (HELP)

SQL++
,
1

I am trying to retrieve data from 2 buckets, no error but nothing shows up (I do have documents I need in these buckets).

1st bucket: a_bucket

here is the document I am interested in (I do have 3 different docs)

author_ID document:
{
“author_ID”: 1,
“profil_creation_date”: “2017/01/01/01:23:05/+5”,
“prefix”: “Mr.”,
“first_name”: “Dylan”,
“middle_name_s”: “Alfred”,
“last_name”: “Kerr”,
“date_of_birth”: “1974/01/02”,
“sex”: “M”,
“marital_status”: “Single”,
“mobile_phone”: “(860) 231-3336”,
“address”: [
{
“address_1”: {
“address_ID”: 1,
“home_address”: “338 Counts Lane”,
“city”: “West Hartford”,
“province/state”: “CT”,
“postal_code”: “06105”
}
},
{
“address_2”: {
“address_ID”: 2,
“work_address”: “977 Copperhead Rd”,
“city”: “Newington”,
“province/state”: “CT”,
“postal_code”: “06111”
}
}
]
}

2nd bucket: b_bucket

here are the 2 docs I am interested in:

p_output_ID document:
{
“p_output_ID”: 1,
“author_ID”: 2,
“overall_score”: 4.41,
“status”: {
“r_status_first”: “TRUE”,
“r_status_second”: “FALSE”,
“r_status_third”: “YES”,
“y_status_second”: “TRUE”,
“y_status_third”: “FALSE”,
“g_status_third”: “TRUE”
}
}

timing_ID document:

{
“timing_ID”: 1,
“p_output_ID”: 1,
“author_ID”: 1,
“date_and_time”: “2017-06-06/23:45:25.25/+5”,
“time_in_seconds”: 12525,
“incremental_time_in_seconds”: “time_in_seconds”,
“current_state_and_duration”: {
“state”: “RED”,
“duration_in_seconds”: 33333
}
}

my goal is to grab these informations in one query ():

prefix, first_name, middle_name_s, last_name (from author_ID document in a_bucket)
overall_score (from p_output_ID document in b_bucket)
date_and_time, state (from timing_ID document in b_bucket)

Here is my query:

select p2.current_state_and_duration.state, p1.overall_score, p2.date_and_time
from proc_data_bucket p1 USE KEYS "p_output_ID"
JOIN proc_data_bucket p2 ON KEYS “author_ID”;

The syntax is OK, but I am getting no data

Please help me with that…

2

try this

INSERT INTO a_bucket VALUES("author_1",{
"author_ID": 1,
"profil_creation_date": "2017/01/01/01:23:05/+5",
"prefix": "Mr.",
"first_name": "Dylan",
"middle_name_s": "Alfred",
"last_name": "Kerr",
"date_of_birth": "1974/01/02",
"sex": "M",
"marital_status": "Single",
"mobile_phone": "(860) 231-3336",
"address": [
{
"address_1": {
"address_ID": 1,
"home_address": "338 Counts Lane",
"city": "West Hartford",
"province/state": "CT",
"postal_code": "06105"
}
},
{
"address_2": {
"address_ID": 2,
"work_address": "977 Copperhead Rd",
"city": "Newington",
"province/state": "CT",
"postal_code": "06111"
}
}
]
} );



INSERT INTO b_bucket VALUES("p_output_1",{
"p_output_ID": 1,
"author_ID": 2,
"overall_score": 4.41,
"status": {
"r_status_first": "TRUE",
"r_status_second": "FALSE",
"r_status_third": "YES",
"y_status_second": "TRUE",
"y_status_third": "FALSE",
"g_status_third": "TRUE"
}
} );


INSERT INTO b_bucket VALUES("timing_1",{
"timing_ID": 1,
"p_output_ID": 1,
"author_ID": 1,
"date_and_time": "2017-06-06/23:45:25.25/+5",
"time_in_seconds": 12525,
"incremental_time_in_seconds": "time_in_seconds",
"current_state_and_duration": {
"state": "RED",
"duration_in_seconds": 33333
}
} );

and run the following N1QL

SELECT p1.prefix, p1.first_name, p1.middle_name_s, p1.last_name,
       p2.date_and_time,p2.state,
       p3.overall_score
  FROM b_bucket p2 USE KEYS ["timing_1"]
  JOIN a_bucket p1 ON KEYS ("author_" || TO_STRING(p2.author_ID))
  JOIN b_bucket p3 ON KEYS ("p_output_" || TO_STRING(p2.p_output_ID))
3

It works perfectly man !

Thanks so much

4

In the case of your solution, I can only retrieve results for the specific key “timing_1”, what if I would like to generalize, let’s say retrieve all informations, using “timing_ID” key, not specifically “timing_1”

5

You can do that, If you want timing_ID from 10 to 50 (For all timing_IDs change below where clause to WHERE p2.timing_ID IS NOT NULL)

CREATE INDEX ix1 ON b_bucket(timing_ID);
SELECT p1.prefix, p1.first_name, p1.middle_name_s, p1.last_name,
       p2.date_and_time,p2.state,
       p3.overall_score
  FROM b_bucket p2
  JOIN a_bucket p1 ON KEYS ("author_" || TO_STRING(p2.author_ID))
  JOIN b_bucket p3 ON KEYS ("p_output_" || TO_STRING(p2.p_output_ID))
WHERE p2.timing_ID BETWEEN 10 AND 50;

FYI: N1QL date functions works on ISO 8601 format (https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html). If data for date follows ISO 8601 format all date functions can be used and dates can be compared with simple string operations.