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


#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 fromtestunnest test.result) as allResult left jointestt 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 fromtestunnest test.result) as allResult left jointestt 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 fromtestlet 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 jointestt 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";

#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.