Hi I have around 160M documents stored in couchbase -
most of the queries that I used to do in a SQL system are the following:
SELECT userid FROM persons WHERE
country = {country}
AND birthdate > {start} AND birthdate < {end}
AND gender = {gender} (or no gender clause)
AND lastlogindate > {start} AND lastlogindate < {end}
AND geohash IN (some geohashes)
Now I’m a bit stuck for replacing that last predicate using couchbase, in de docs it states -
http://docs.couchbase.com/admin/admin/Views/views-translateSQL.html, replacing SQL WHERE (ingredient = ‘carrot’ OR ingredient = ‘rice’) is fixed by looping twice over doc.ingredients & emitting. .
If I want to have something more dynamic f.e. WHERE ingredient IN(…, …, … ) where it can be 1 or x elements -
this is not possible in couchbase?
To have a multiple where clause condition as you’ve defined above is possible but it would likely require emitting multiple entries for a single document in order to group by specific geohashes and parameterizing the query at runtime would be a challenge.
Alternatively CB 4.0 can easily perform this SQL query as you’ve defined it above. The developer preview is being released this month.
Thanks.
Fixed it for now by doing:
emit([doc.country,
doc.gender,
doc.birthdate],
[doc.sexual_o,
doc.geohash]
using a range query on
[“be”,1,19800101] to [“be”,1,19900101]
and doing the geohash filtering application side
Tried out the n1ql approach too as you stated but when trying out the latest release it kept running indefinitely… developer preview is not stable enough for my use case.
Underlying it also uses views… I rather have more control over what my query will use of index.