Not sure if my N1ql query is correct

SQL++
1

sql query looks like this and it works "SELECT Distance FROM UT_BEACON_INFO WHERE ReceivedDate >= \'" + currentDate.AddSeconds(-5) + "\' AND ReceivedDate <= \'" + currentDate.AddSeconds(5) + "\' AND SerialNumber = \'" + serialNumber + "\' AND ReceiverId = \'" + receiverId + "\'"

I’m trying to write a N1ql query that’s the same. I have tried two ways

  1. SELECT BucketName.distance FROM B730FLS_DEV WHERE type = ‘UT_BEACON_INFO’ AND BucketName.receivedDate>= ‘" + currentDate.AddSeconds(-5) +
    "’ AND BucketName.receivedDate <= ‘" + currentDate.AddSeconds(5) + "’ AND BucketName.serialNumber = ‘" + serialNumber + "’ AND BucketName.receiverId = ‘" + receiverId + "’"

  2. SELECT BucketName.distance FROM B730FLS_DEV WHERE type = ‘UT_BEACON_INFO’ AND receivedDate>= ‘" + currentDate.AddSeconds(-5) +
    "’ AND receivedDate <= ‘" + currentDate.AddSeconds(5) + "’ AND serialNumber = ‘" + serialNumber + "’ AND receiverId = ‘" + receiverId + "’".

Any suggestions? thank you.

2

Do u have some issue? Chage BucketName.distance to Distance

3

@vsr1
Ok, I will try that. I had another question. If I have two document and wanted to all the information where the username equals each other in the two different types, what would the query be? "SELECT * FROM bucket WHERE type = ‘Type1’ AND Type1.userName= Type2.userName "? “SELECT bucket.type.Type1, bucket.type.Type2 FROM bucket WHERE type= type”?

{"bucket": {
  "userName": bob,
  "distance": 4,
  "type": "Type1"
}}


{"bucket": {
  "userName": bob,
  "height": 5,
  "type": "Type2"
}}

edit- http://query.pub.couchbase.com/tutorial/#24 I looked at that tutorial, if I want to do “inner join” like in sql, the only way I can do this is by using keys?

4

At present JOINS are supported through KEYS.
How big is data set for the Type1 or Type2.

5

@vsr1 not big at all, combined they would have 20 fields.

6

I meant number of qualified rows.

7

@vsr1 what are qualified rows? Sorry, I’m new.

8

select count(1) from bucket where type = “Type1”;
select count(1) from bucket where type = “Type2”;

9

I see what you mean, they are both 0 right now since I haven’t inserted anything right now. I’m in the process of converting a sql restful service to a couchbase one.

10

The following should work. If number of count on each type grows you need to measure performance impact.

INSERT INTO default VALUES("k001",{ "userName": "bob", "distance": 4, "type": "Type1" }), VALUES("k002",{ "userName": "bob", "height": 5, "type": "Type2" });
CREATE INDEX ix1 ON default(type);
SELECT d.*, t2.*  FROM default d
LET t2 = FIRST v FOR v IN (SELECT RAW d2 FROM default d2 WHERE d2.type = "Type2") WHEN v.userName = d.userName END
WHERE d.type = "Type1" AND t2 IS NOT MISSING;
11

Thank you. I was wondering if you could help me with another query. If I wanted to join different documents in the same bucket where a variable in each document equals each other, how would I do this? For example

Document 1

{{
  "beaconSerial": "333",
  "type": "UT_BEACON_LOCATION",
  "uldSerial": "444"
}}

document 2
{{
“type”: “UT_ULD_INFO”,
“uldIsEmpty”: “Yes”,
“serial”: “444”,
}}

I want to join those two documents with type “UT_BEACON_LOCATION” and “UT_ULD_INFO” where “uldSerial” = “serial”

12

you can only JION on primary key ,FYI
https://developer.couchbase.com/documentation/server/4.5/n1ql/n1ql-language-reference/from.html#story-h2-3

13

yea, he stated that above. I was just wondering if there was a nice workaround

14

Couchbae JOINS are supported through field in document to Document key. i.e. parent, child relation needs to be present through document key

You can query each one independently joins through arrays as follows, but this may not perform well.
Best approach will be build prarent-child relation ship through document key.

SELECT d.*, t2.*  FROM default d
LET t2 = FIRST v FOR v IN (SELECT RAW d2 FROM default d2 WHERE d2.type = "UT_ULD_INFO") WHEN v.serial = d.uldSerial END
WHERE d.type = "UT_BEACON_LOCATION" AND t2 IS NOT MISSING;

If “uldSerial” = “serial” has more than one match

SELECT t1.d.*,t2.* FROM (
SELECT d, t2 FROM default d
LET t2 = ARRAY v FOR v IN (SELECT RAW d2 FROM default d2 WHERE d2.type = "UT_ULD_INFO") WHEN v.serial = d.uldSerial END
WHERE d.type = "UT_BEACON_LOCATION" AND ARRAY_LENGTH(t2) > 0 ) AS t1 UNNEST t1.t2 AS t2;
1 Like