N1QL features are coming to more and more SDKs– now including Ruby. I'd like to give a quick intro of how structured queries in ruby work. At the moment library does not provide an ODM or some other high-level abstraction atop of it, but pretty much everything is possible otherwise.
Experimental support for N1QL has been added in recent minor release 1.3.12 though it is possible the API will change in final release. In the examples below I will show you how to work with it using new travel dataset, which is in the 4.0 developer preview. In this blog, I am assuming here that you have installed the most recent version of the ruby client (if not, please run “gem install -v 1.3.12“) and have a local Couchbase Server with the query service enabled.
First, lets establish a connection to the bucket:
|
1 |
cb = Couchbase.connect(bucket: 'travel-sample') |
The entry point for N1QL queries is Couchbase::Bucket#query method, which accepts a string in N1QL, and returns a hash with :rows and :meta keys, where the response from the server delivered. If there is an issue, the library will raise Couchbase::Error::Query exception with attached metadata to analyze the error.
For example, lets create primary index for whole dataset:
|
1 |
cb.query("CREATE PRIMARY INDEX on `travel-sample`") |
With the primary index defined we can fetch the documents by their keys:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
cb.query('SELECT * FROM `travel-sample` USE KEYS ["airline_24"]') # => {:rows=> # [{"travel-sample"=> # {"callsign"=>"AMERICAN", # "country"=>"United States", # "iata"=>"AA", # "icao"=>"AAL", # "id"=>24, # "name"=>"American Airlines", # "type"=>"airline"}}], # :meta=> # {"requestID"=>"7cc10c30-ecb7-4e65-b582-fe9852e85d5b", # "signature"=>{"*"=>"*"}, # "results"=>[], # "status"=>"success", # "metrics"=> # {"elapsedTime"=>"8.308061ms", # "executionTime"=>"8.206911ms", # "resultCount"=>1, # "resultSize"=>306}}} |
To make the examples simpler, we can use the following function to strip :meta field and extract just values.
|
1 2 3 |
def strip(doc) doc[:rows] end |
Lets answer some questions about this sample bucket. As you might remember, previous sample beer data contained about 7k documents, lets see how many this one has:
|
1 2 |
strip cb.query('SELECT COUNT(*) AS total FROM `travel-sample`') # => [{"total"=>31621}] |
That's a great sandbox for our experiments. What kind of documents does it contain?
|
1 2 3 4 5 |
strip cb.query('SELECT type, COUNT(*) AS total FROM `travel-sample` GROUP BY type') # => [{"total"=>1969, "type"=>"airport"}, # {"total"=>5389, "type"=>"landmark"}, # {"total"=>187, "type"=>"airline"}, # {"total"=>24076, "type"=>"route"}] |
As homework assignment, you can explore the structure of the sample database further. For now let us answer some more practical questions. What routes are available for someone in Los Angeles, who wants to see the Eiffel Tower in Paris? A straightforward implementation might look like this (assuming you know what LAX and CDG mean).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
require 'date' routes = strip cb.query(' SELECT airline.name, route.equipment, route.schedule FROM `travel-sample` route JOIN `travel-sample` airline ON keys route.airlineid WHERE route.sourceairport = "LAX" AND route.destinationairport = "CDG" ') routes.each do |route| puts("--------------------------") puts("#{route['name']} (#{route['equipment']})") puts("--------------------------") route['schedule'].each do |flight| day = Date::ABBR_DAYNAMES[flight['day']] puts("#{flight['flight']}: #{flight['utc']}, #{day}") end end |
If you run this code, you will get a nicely formatted time table of all routes to see the famous tower as shown below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-------------------------- Delta Air Lines (388 772) -------------------------- DL024: 03:23:00, Sun DL856: 04:38:00, Sun DL124: 06:01:00, Sun DL249: 06:00:00, Sun DL142: 10:00:00, Mon DL757: 10:44:00, Tue ... -------------------------- Air Tahiti Nui (343) -------------------------- TN070: 06:03:00, Sun TN040: 08:39:00, Mon TN832: 11:42:00, Tue TN752: 08:32:00, Tue TN519: 23:33:00, Tue TN278: 05:27:00, Tue TN325: 18:07:00, Wed ... |
That is it for the intro at the moment, please leave your feedback about the new query. In particular, please let us know what kind of Ruby support you'd love to see!
English
looks nice, looking forward for a node.js sample :)