N1QL

From wikinotes
Revision as of 21:00, 11 February 2021 by Will (talk | contribs) (→‎Documentation)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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 column

Reply

{
  "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 strings

SELECT 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 END
SELECT 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