Query Json Document

SQL++
1

Hi,

I’m using Couchbase quite long for my caching which works simply by set() and get() the id of a document and it’s data formatted in Json.

Now I wondered if I cimply can (I cannot make it clear out of the docs) if I can query for something that is in my Json data all over the documents and get back the full Json response of that document.

I cannot find something like a “howto” so I need to as becaus the docs are pretty good but I don’t get the clue out of it.

I hope someone can help me out!

Thanks,

Matt

2

You can issue SQL queries and get the results. Checkout https://www.couchbase.com/products/n1ql
https://query-tutorial.couchbase.com/tutorial/#1

1 Like
3

OK, that looks a little bit better, but what I don’t get is, do I need views for my bucket ? I try to implement this in PHP.

4

When Cluster is configured bring up these services

Data
Index
Query

Then Create appropriate index (Primary or Secondary Indexes) and issue queries.
No views required.

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

1 Like
5

Thanks, very good documents indeed. As I’m checking the webgui I cannot set mem for Queries but that might be because you never know how large the result will be. But creating an index is not possible there, is that true ?

6

You can’t set memory quotas for Query Service.
(Query Workbench) Query Tab in Web Console allows you create indexes and issue queries. Type the SQL statement and Execute.
https://blog.couchbase.com/a-tour-of-the-new-couchbase-web-console/

1 Like
7

Because of your help so far I’m a little but further. I can do a select * from bucketname; and get the result with the following index:

CREATE INDEX geoCode ON `geoPoints`(code);

Where my Document in my bucket is:

{"code":"Ok","waypoints":[{"hint":"yHYJgNN4CYAQAAAAKAAAAHcAAACaAAAAW2k1QTG_0EFDbZ9CS6huQhAAAAAoAAAAdwAAAJoAAABSBQAAPfVUAKZtGAMJ9lQAGGwYAwMAj",....

Where the following gives me an empty result:

select * from geoPoints where code = "Ok"

I let the query explain but don’t see anything strange in the output there.

What am I’m missing here ? I’m on the community 5.1.1 version.

8

It should work. Post the EXPLAIN output

9

Here you go:

{

“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “PrimaryScan”,
“index”: “geoPoints”,
“keyspace”: “geoPoint”,
“namespace”: “default”,
“using”: “gsi”
},
{
“#operator”: “Fetch”,
“keyspace”: “geoPoint”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((geoPoint.Code) = “ok”)”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “select * from geoPoints where Code=“ok”;”
}

10

Make sure the field names and values are case sensitive.

Example: Document has “code”: “Ok” i.e lower case c and Upper case O
But query has Upper case C and lower case o

Try this.

SELECT  * FROM geoPoints 
WHERE code="Ok";
11

Sorry that was a typo indeed as I was testing case sensitive, same issue there. Do you want to see that explain as well ?

12

Sure post the EXPLAIN

13

OK, here you go again:

{

“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “PrimaryScan”,
“index”: “geoPoints”,
“keyspace”: “geoPoints”,
“namespace”: “default”,
“using”: “gsi”
},
{
“#operator”: “Fetch”,
“keyspace”: “geoPoints”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((geoPoints.code) = “Ok”)”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “SELECT * FROM geoPoints WHERE code=“Ok”;”
}

14

Plan seems right can get output of
SELECT * FROM geoPoints LIMIT 1;

1 Like
15

Yes that works, I get one result back.

16

Can you post the output

17 
[
{
"geoPoints": "{\"code\":\"Ok\",\"waypoints\":[{\"hint\":\"yHYJgNN4CYAQAAAAKAAAAHcAAACaAAAAW2k1QTG_0EFDbZ9CS6huQhAAAAAoAAAAdwAAAJoAAABSBQAAPfVUAKZtGAMJ9lQAGGwYAwMAjwRk ...
}
]

Is there some depth issue ? I cut of the rest as it’s quite some data :slight_smile: The beginning/end should be needed to see.

18

Where did u get geoPoint_cache? Can u paste the query? or Paste complete highrarchy of document that includes root to code path only.

Also try this. nested fields in object needs to be referenced with path each object separated with .

SELECT  * FROM geoPoints 
WHERE geoPoint_cache.code="Ok";
19

Sorry, I was testing 2 different buckets with different indexes to see where it goes wrong. You can keep the geoPoints but I also tried nested onject with the . same issue, empty result.

20

Please Post the document where you are trying.

You can try the following simple one and see if it works.

INSERT INTO default VALUES ("k101",{"code":"Ok","waypoints":[]});
CREATE INDEX ix11 ON default(code);
SELECT * FROM default WHERE code = "Ok";
1 Like