N1QL
From wikinotes
N1QL is a SQL-like query language built for couchbase.
It queries JSON objects from a NoSQL database.
NOTE:
Almost all examples copied verbatim from official tutorial
Documentation
official docs (click lesson index) https://query-tutorial.couchbase.com/tutorial/#index
Example
Query
SELECT * FROM tutorial # a couchbase db WHERE fname = 'Ian' # a couchbase columnReply
{ "results": [ { "tutorial": { "type": "contact", "title": "Mr.", "fname": "Ian", "lname": "Taylor", "age": 56, "email": "ian@gmail.com", "children": [ { "fname": "Abama", "age": 17, "gender": "m" }, { "fname": "Bebama", "age": 21, "gender": "m" } ], "hobbies": [ "golf", "surfing" ], "relation": "cousin" } } ] }
Operators
||
concatenates stringsSELECT fname || " " || lname AS full_name, age FROM users;
SQL Subset
The following features have been borrowed verbatim from SQL.
- LIKE
- AND
- ORDER BY
- LIMIT
- GROUP BY
- HAVING
- subqueries
SELECT * FROM (SELECT * from product) AS foo
Lists
Lists Indexes
SELECT children[0].fname AS child_name FROM tutorial WHERE fname='Dave'Filter lists (select where...)
SELECT fname, children FROM tutorial WHERE ANY child IN tutorial.children SATISFIES child.age > 10 END
JOINs
TODO:
confirm
A JOIN produces 1x object for each FROM-table object.
(even if there are multiple).SELECT usr.personal_details, orders FROM users_with_orders usr JOIN orders_with_users orders ON KEYS ARRAY s.order_id FOR s IN usr.shipped_order_history ENDSELECT p, c FROM purchases p INNER JOIN customer c ON (p.customerId = META(c).id) LIMIT 1# JOIN on array SELECT p.name, r.rating FROM product p INNER JOIN reviews r ON (META(r).id IN p.reviewList) WHERE META(p).id = "product320"# WHERE select on array using expression SELECT product.name, product.unitPrice FROM product WHERE ANY cat IN product.categories SATISFIES lower(cat) = "golf" END ORDER BY product.unitPrice DESC LIMIT 5# ARRAY in SELECT results SELECT fname AS parent_name, ARRAY child.fname FOR child IN tutorial.children END as child_names, FROM tutorial WHERE children IS NOT NULL
NEST
TODO:
confirm
NEST is like JOIN,
but it produces N objects for each FROM-table object.
(representing all objects, within an array).SELECT usr.personal_details, orders FROM users_with_orders usr NEST orders_with_users orders ON KEYS ARRAY s.order_id FOR s IN usr.shipped_order_history END# UNNEST joins nested-array items with their parent array SELECT * FROM tutorial AS parent UNNEST parent.children WHERE parent.fname = 'Dave'
UPDATE
Update works as you would expect, but you can also set the return type.
UPDATE tutorial USE KEYS "baldwin" SET type = "actor" RETURNING tutorial.type