How to query only specific keys as list of map from large list of map couchbase

SQL++
1

I want to query couchbase data using plain SQL query from the couchbase console.

The oop schema will be like this.

Original:

class travel-sample{
//all properties
List schedule;
}

class Schedule{
//all properties
}
Need to project travel-sample like this in a separate class:

class travel-sample-another{
//some properties
List schedule;
}

class Schedule-another{
//some properties
}
Following are documents:

Document:

[
{
“travel-sample”: {
“airline”: “AF”,
“airlineid”: “airline_137”,
“destinationairport”: “MRS”,
“distance”: 2881.617376098415,
“equipment”: “320”,
“id”: 10000,
“schedule”: [
{
“day”: 4,
“flight”: “AF818”,
“utc”: “08:02:00”
},
{
“day”: 5,
“flight”: “AF967”,
“utc”: “11:33:00”
},
{
“day”: 5,
“flight”: “AF730”,
“utc”: “19:42:00”
}
],
“sourceairport”: “TLV”,
“stops”: 0,
“type”: “route”
}
}
]
How to query to get results like this:

[
{
“travel-sample”: {
“airline”: “AF”,
“airlineid”: “airline_137”,
“destinationairport”: “MRS”,
“schedule”: [
{
“flight”: “AF818”,
“utc”: “08:02:00”
},
{
“flight”: “AF967”,
“utc”: “11:33:00”
},
{
“flight”: “AF730”,
“utc”: “19:42:00”
}
],
“sourceairport”: “TLV”,
“type”: “route”
}
}
]
We could see day property is not in the array of the map for schedule and so on. I want exact this format efficiently. I am new to the couchbase. Maybe UNNEST, NEST or Iteration can help, however, I couldn’t get the way to retrieve exactly this result. Just plain sql query will be good for me. I will take care of other stuff. Thank you in advance.

2 
SELECT t.*, ARRAY {v.flight, v.utc}  FOR v  IN t.schedule END AS schedule 
FROM `travel-sample` AS t WHERE type = "route";

https://blog.couchbase.com/working-json-arrays-n1ql/

3

@vsr1 Thank you very much! I need another help regarding jion on same bucket over array which contains id field referencing to some outer doucment.

Details are follows:

k001
{"sn":"087871","type":"airline"}
k002
{"sn":"898908","type":"bus"}
k003
{"company":"test","date":216257651,"reg":"8767867","phone":"98","result":[{"des":"This is test","name":"unknown"},{"id":"k002","name":"express244"},{"id":"k001","name":"jet167"}]}

I want exact like this:

[
{
“company”: “test”,
“listresut”: [
{
“des”:“This is test”,
“name”: “unknown”
},
{
“id”: “k002”,
“name”: “express244”,
“sn”: “898908”,
“type”: “bus”
},
{
“id”: “k001”,
“name”: “jet167”,
“sn”: “087871”,
“type”: “airline”
}
],
“phone”: “98”,
“reg”: “8767867”
}
]

I am doing something like this:

Step 1:
select * from (select result from testunnest test.result) as allResult left jointest t on keys allResult.result.id
My result:

[
{
“allResult”: {
“result”: {
“des”: “This is test”,
“name”: “unknown”
}
}
},
{
“allResult”: {
“result”: {
“id”: “k002”,
“name”: “express244”
}
},
“t”: {
“sn”: “898908”,
“type”: “bus”
}
},
{
“allResult”: {
“result”: {
“id”: “k001”,
“name”: “jet167”
}
},
“t”: {
“sn”: “087871”,
“type”: “airline”
}
}
]

Steap 2:
select allResult.company, allResult.result.id, allResult.result.name, t.sn, t.type from (select * from (select result, test.company from testunnest test.result) as allResult left jointest t on keys allResult.result.id where allResult.company like "%test%") as listResult

Result:

[
{
“company”: “test”,
“name”: “unknown”
},
{
“company”: “test”,
“id”: “k002”,
“name”: “express244”,
“sn”: “898908”,
“type”: “bus”
},
{
“company”: “test”,
“id”: “k001”,
“name”: “jet167”,
“sn”: “087871”,
“type”: “airline”
}
]

Step 3:
select company, phone, reg, listresut from testlet listresut = (select allResult.company, allResult.result.id, allResult.result.name, t.sn, t.type from (select * from (select result, test.company fromtestunnest test.result) as allResult left jointest t on keys allResult.result.id where allResult.company like "%test%") as listResult) where company like "%te%"

Result:

[
{
“company”: “test”,
“listresut”: [
{
“company”: “test”,
“name”: “unknown”
},
{
“company”: “test”,
“id”: “k002”,
“name”: “express244”,
“sn”: “898908”,
“type”: “bus”
},
{
“company”: “test”,
“id”: “k001”,
“name”: “jet167”,
“sn”: “087871”,
“type”: “airline”
}
],
“phone”: “98”,
“reg”: “8767867”
}
]

4 
 INSERT INTO default VALUES("k001", {"sn":"087871","type":"airline"}),
         ("k002", {"sn":"898908","type":"bus"}), 
         ("k003", {"company":"test","date":216257651,"reg":"8767867","phone":"98","result":[{"des":"This is test","name":"unknown"},{"id":"k002","name":"express244"},{"id":"k001","name":"jet167"}]});


SELECT  t.company, t.date, t.reg, t.phone,
  (ARRAY OBJECT_CONCAT(v, 
          IFMISSING((SELECT RAW t1 FROM default AS t1 USE KEYS v.id)[0],{})) 
 FOR v IN t.result END)  AS result 
FROM default AS t  USE KEYS "k003";

OR

SELECT  t.*, 
  (SELECT a.*, t1.* FROM t.result AS a LEFT JOIN default AS t1 ON KEYS a.id)  AS result
FROM default AS t  USE KEYS "k003";
1 Like
5

AWESOME!!! Many many thanks for wonderful answers and quick reply.

6

@vsr1 which one has better performance? I see 2nd is more consistent & running fast because there are only 3 project while first has 6. Thank you.

7

Use Second the query easy to understand.

8

Thank you. I understood both. What is about performance wise? Could you please give me some reference to tune the performance regretless of infrastructure resources? Project is taking 50% of time? Is that because of sub query? Overall query is taking 1.10s in average for limit 50. How if I index my list?

9

It is correlated subquery each row it needs to re-execute it

10

Does Indexing my list help me? 2nd has better performance right? Can we do this without sub query? I know we need to corelate each key of list with outer docid. I just started reading about couchbase so don’t have much idea.

11

The problem is structure of output you want.

The following gives right side where id’s exist as newresult array

SELECT  t.*, newresult
FROM default AS t  USE KEYS "k003" NEST default AS newresult ON KEYS t.result[*].id ;

The checkout profiling/monitoring/index optimizations in

https://blog.couchbase.com/a-guide-to-n1ql-features-in-couchbase-5-5-special-edition/

12

Thank you. I just started working on existing schema. :slight_smile: I only know what output needed at this point. Once I knew business rule completely then I could ask to restructure the association between class of system. I understand simple association & denormalized schema will have better performance.

13

Hi @vsr1

Both queries cause following error 1/3 of time.

[{
“Code”:12008,
“msg”:”Error perfoming bulk get operation - cause:{1:errors, starting with read tcp ip1:61220 -> ip1:11210: wsarecv: An established connection was aborted by software in your host machine.}”
}]

Enterprise Edition: 5.0.0 build 2873

Thank you.

14

You need to find out why connection is aborted. Try increase timeout.