Query in Couchbase. N1QL: SQL for JSON

©2016 Couchbase Inc. 3
Agenda
Thinking in N1QL
Anatomy of Query

Business Application Questions &Tasks
How many new customers we
got last month?
Get the list of stores in
customer region
Search stores for the shoe
customer is looking for?
Generate a list of shipment
due today
Load the new inventory data
Update the sale prices in
outlet stores only
Merge the customer lists

NoSQL Landscape
Document
• Couchbase
• MongoDB
• DynamoDB
• DocumentDB
Graph
• OrientDB
• Neo4J
• DEX
• GraphBase
Key-Value
• Couchbase
• Riak
• BerkeleyDB
• Redis
• … Wide Column
• Hbase
• Cassandra
• Hypertable
JSON to Represent Data

Data Management Landscape
Processing
in Files
MapReduce
Generic fileformats
Rows/Columns
in files (tables)
Hive – Pig - etc
Query
Impala
Hive
NoSQL
MongoDB
Couchbase
Hbase
Cassandra
HADOOP
(Analytical)
Disk &
Storage
Highly Structured Data
SQL, R, etc
Bytes & Blocks
$100K – $200K /TB$1K/TB$10K/TB
Semi Structured & Self describingNo Structure
OLTP EDW
$10K-$20K/TB
Drill
Operational Big data
Couchbase 4

Properties of Real-World Data
 Rich structure
– Attributes, Sub-structure
 Relationships
– To other data
 Value evolution
– Data is updated
 Structure evolution
– Data is reshaped
Customer
Name
DOB
Billing
Connections
Purchases

Transform: Relational to JSON
{
"Name" : "Jane Smith",
"DOB" : "1990-01-30",
"Billing" : [
{
"type" : "visa",
"cardnum" : "5827-2842-2847-3909",
"expiry" : "2019-03"
},
{
"type" : "master",
"cardnum" : "6274-2842-2847-3909",
"expiry" : "2019-03"
}
],
"Connections" : [
{
"CustId" : "XYZ987",
"Name" : "Joe Smith"
},
{
"CustId" : "PQR823",
"Name" : "Dylan Smith"
}
{
}
],
"Purchases" : [
{ "id":12, item: "mac", "amt": 2823.52 }
{ "id":19, item: "ipad2", "amt": 623.52 }
]
}
DocumentKey: CBL2015
CustomerID Name DOB
CBL2015 Jane Smith 1990-01-30
Customer
ID
Type Cardnum Expiry
CBL2015 visa 5827… 2019-03
CBL2015 maste
r
6274… 2018-12
CustomerID ConnId Name
CBL2015 XYZ987 Joe Smith
CBL2015 SKR007 Sam Smith
CustomerID item amt
CBL2015 mac 2823.52
CBL2015 ipad2 623.52
CustomerID ConnId Name
CBL2015 XYZ987 Joe Smith
CBL2015 SKR007 Sam
Smith
Contacts
Customer
Billing
ConnectionsPurchases

JSON
{
"DOB" : "1990-01-30",
"Billing" : [
{
"type" : "visa",
"cardnum" : "5827-2842-2847-3909",
"expiry" : "2019-03"
},
{
"type" : "master",
"cardnum" : "6274-2842-2847-3909",
"expiry" : "2019-03"
}
],
"Connections" : [
{
},
{
}
{
}
],
"Purchases" : [
{ "id":12, item: "mac", "amt": 2823.52 }
{ "id":19, item: "ipad2", "amt": 623.52 }
]
}
 JSON is a means to the end and not the end itself
– JSON is the representation of the enterprise data model for applications
– JSON flexibility translates to application flexibility
• Simple flattened data can be represented
• Entities with complex data, always accessed analyzed together should belong
together
– Applications are designed to handle the flexible data model.

Models for Representing Data
Data Concern Relational Model
JSON Document Model
(NoSQL)
Rich Structure
 Multiple flat tables
 Constant assembly / disassembly
 Documents
 No assembly required!
Relationships
 Represented
 Queried (SQL)
 Represented
 Queried? Not until now…
Value Evolution  Data can be updated  Data can be updated
Structure Evolution
 Uniform and rigid
 Manual change (disruptive)
 Flexible
 Dynamic change

ResultSet

{
"DOB" : "1990-01-30",
"Billing" : [
{
"type" : "visa",
"cardnum" : "5827-2842-2847-3909",
"expiry" : "2019-03"
},
{
"type" : "master",
"cardnum" : "6274-2842-2847-3909",
"expiry" : "2019-03"
}
],
"Connections" : [
{
},
{
}
{
}
],
"Purchases" : [
{ "id":12, item: "mac", "amt": 2823.52 }
{ "id":19, item: "ipad2", "amt": 623.52 }
]
}
LoyaltyInfo ResultDocuments
Orders
CUSTOMER
Built Manually; Expensive

{
"DOB" : "1990-01-30",
"Billing" : [
{
"type" : "visa",
"cardnum" : "5827-2842-2847-3909",
"expiry" : "2019-03"
},
{
"type" : "master",
"cardnum" : "6274-2842-2847-3909",
"expiry" : "2019-03"
}
],
"Connections" : [
{
},
{
}
{
}
],
"Purchases" : [
{ "id":12, item: "mac", "amt": 2823.52 }
{ "id":19, item: "ipad2", "amt": 623.52 }
]
}
LoyaltyInfo ResultDocuments
Orders
CUSTOMER

N1QL Changes the Possibilities for NoSQL

Goal of N1QL: SQL for JSON
Give developers and enterprises an
expressive, powerful, and complete
language for querying, transforming,
and manipulating JSON data.

N1QL: Developers & Enterprises
Application Developers in all languages
–Couchbase SDK Support for N1QL
–Open REST API
Exchanges data with other databases using Standard
Tools
Simba provides ODBC, JDBC drivers
Tableau, INFA interoperability

N1QL: expressive
 Access to every part of JSON document
 Scalar & Aggreate functions
 Issue subquery in any expressions
 Subqueries
 Subqueries in the FROM clause

N1QL: powerful
 Access to every part of JSON document
 JOINS, Aggregations, standard scalar functions
 Aggregation on arrays
 NEST & UNNEST operations
 Covering Index

N1QL: querying
 INSERT
 UPDATE
 DELETE
 MERGE
 SELECT
 EXPLAIN

N1QL: transforming & manipulating
 FullTransformation of the data via Query.
 INSERT
– INSERT single & multiple documents
– INSERT result a SELECT statement
 DELETE documents based on complex filter
 UPDATE any part of JSON document & use complex filter.
 MERGE two sets of documents using traditional MERGE
statement

Where’s My Beer?
SELECT {"Mybeer": "My Beer is " || beer.name || "."},
ARRAY_AGG({"name":brewery.name}) brewery,
ARRAY_AGG({"name":brewery.name, "state":brewery.state,
"city":brewery.city, "location":brewery.geo}) locations,
ARRAY_COUNT(ARRAY_AGG(brewery.name)) as brewery_count
FROM `beer-sample` beer
LEFT OUTER JOIN `beer-sample` brewery
ON KEYS beer.brewery_id
WHERE beer.type = 'beer'
AND brewery.type = 'brewery'
AND brewery.state = 'California'
GROUP BY beer.name
ORDER BY ARRAY_COUNT(ARRAY_AGG(brewery.name)) desc,
beer.name asc
LIMIT 5
;

Hands-onWorkshop
 Execute the query1
 Observe & discuss the structure of the document returned by the query
 Run the EXPLAIN for the query
 Modify
– WHERE clause
– LIMIT clause
– ORDER BY clause

QUERY EXECUTION

N1QL: Query Execution Flow
Clients
1. Submit the query over RESTAPI 8. Query result
2. Parse, Analyze, create Plan 7. Evaluate: Documents to results
3. Scan Request;
index filters
6. Fetch the documents
Index
Servic
e
Query
Service
Data
Servic
e
4. Get qualified doc keys
5. Fetch Request,
doc keys
SELECT c_id,
c_first,
c_last,
c_max
FROM CUSTOMER
WHERE c_id = 49165;
{
"c_first": "Joe",
"c_id": 49165,
"c_last": "Montana",
"c_max" : 50000
}

Inside a Query Service
Client
FetchParse Plan Join Filter
Pre-Aggregate
Offset Limit ProjectSortAggregateScan
Query Service
Index
Servic
e
Data
Servic
e

Inside a Query Service
Pre-Aggregate
Query Service
Index
Servic
e
Data
Servic
e
Prepare
Phase
Projection
Phase
Run
Phase
Scan
Phase
Fetch
Phase
Sort
Phase

QUERY PREPARE

Prepare Phase: Parse & Semantic Check
 Analyzes the Query for syntax & grammar
 Only verifies for existence of referenced buckets
 Flexible schema means, you can refer to arbitrary attribute
names. N1QL key names are CaSe SENSitive.
 A key is NULL if it’s a known null. {“location”:null}
 A key is MISSING if not present in the document
 Use IS NULL and IS MISSING to check for each condition
 Full reference to JSON structure
– Nested reference: CUSTOMER.contact.address.state
– Array Reference: CUSTOMER.c_contact.phone_number[0]
 SQL is enhanced to access & manipulateArrays
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan

Prepare: PLAN
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan
 Each query can be executed in several ways
 Create the query execution plan
– Access path for each keyspace reference
– Decide on the filters to push down
– Determine Join order and join method
– Create the execution tree
 For each keyspace reference:
– Look at the available indices
– Match the filters in the query with index keys
– Choose one or more indices for each keyspace

PREPARE
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan
 Ever statement executed in the engine is PREPARED
 When you have same statement executed millions of
times, you can save prepare time
 Prepare statement can use simple adhoc statement
PREPARE p1 FROM SELECT * from `beer-sample` LIMIT 1;
EXECUTE p1;
 Compare the execution time
 Prepare Statement can exploit parameters to bind
PREPARE p2 FROM SELECT * from `beer-sample` WHERE abv = $1;
$ curl http://localhost:8093/query/service -H "Content-
Type: application/json" -d '{"prepared":"p2",
"args":[5.0]}’
$ curl http://localhost:8093/query/service -d 'prepared
="mp"&args=[5.0]’

PREPARE
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan
 Parameter can be named or positional.
 Named parameters:
curl http://localhost:8093/query/service
-d 'statement=prepare p3 from select
count(*) from `beer-sample` where abv >
$val’
-H "Content-Type: application/json" -d
'{"prepared":"p3", "$val":5.0}’
-d 'prepared="p3"&$val=5.0'

PREPARE: Cluster Environment
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan
-d 'statement=prepare p4 from select name
from `beer-sample` where name LIKE $val’
curl http://localhost:8093/query/service -H "Content-Type:
application/json" -d '{"prepared":"p4",
"encoded_plan":"H4sIAAAJbogA/5xSTW/qMBD8K5F5B5B4T3oSp9x6KBJ
qD0gcq4q4yZAYHNtdOxSK0t/edaDlq5dys2dnZ2fW3gmY3BYo5k5LI1Ihhs
LIGnxyIz5bB5LBkkh3one8iBleG+4EUz7ySumCwN1P56S7JlSW1HtkdaSbV
KakaknbWc7+hkKZAhtGe24PM7TC1juZR88vAP31snYahyBflQIL2ejAaOOV
KRkpvRLt8GKWJKk19LehmxyPEfLqt8YurYyVDqC4OWsKFZSNr9PvZydK2b8
sKmWDRKsVkj9rqQdXOhPDzVJPyS6Rx/wEz/PmLF77zjo2LhJ/lhbt87Uzc9
TjcmQIfo610ijh48oOodLT2On/9lJpFgiy7kYIr0ojQ0OI/YcvuPScmmsBm
xB/JMFJQuJGyYJsnXhotpBE8h44S5C8VWByV32cPNx3+xHtZwAAAP//FkDT
W/ICAAA=", "$val":"%Ale%"}'

PREPARE: Summary
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan
 Using prepare state saves time.
– Amount of saving depends on the query complexity, index
choices.
 Effective when the same statement or statement with
parameters are executed
 Encoded plan enables SDKs and applications to prepare
once and use it on any node in the cluster.
 SDKs use prepared statement behind the scenes.

Hands-onWorkshop
 Prepare the queries P1 through P4.
 Execute them in cbq and via curl
 Compare the execution timings.

QUERY PLANNING & INDEX SELECTION

PLAN:We’re not done yet!
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan
 Each query can be executed in several ways
 Create the query execution plan
– Access path for each keyspace reference
– Decide on the filters to push down
– Determine Join order and join method
– Create the execution tree
 For each keyspace reference:
– Look at the available indices
– Match the filters in the query with index keys
– Choose one or more indices for each keyspace

Index Overview: Primary Index
 Primary Index
 CREATE PRIMARY INDEX `beer-sample`;
 Document key is unique for the bucket.
 Primary index is used when no other qualifying
index is available or when no predicate is given
in the query.
 PrimaryScan is equivalent of full table scan
"beer-sample": {
"abv": 7.2,
"brewery_id":
"21st_amendment_brewery_cafe",
"category": "North American Ale",
"description": "Deep golden color...!",
"ibu": 0,
"name": "21A IPA",
"srm": 0,
"style": "American-Style India Pale Ale",
"type": "beer",
"upc": 0,
"updated": "2010-07-22 20:00:20"
}
Document key: “guiness_d1”

"beer-sample": {
"abv": 7.2,
"brewery_id":
"21st_amendment_brewery_cafe",
"category": "North American Ale",
"description": "Deep golden color...!",
"ibu": 0,
"name": "21A IPA",
"srm": 0,
"style": "American-Style India Pale Ale",
"type": "beer",
"upc": 0,
"updated": "2010-07-22 20:00:20"
}
Document key: “Pale Ale.A1”
Index Overview: Secondary Index
Secondary Index can be created on any
combination of attribute names.
create index idxbeerabv on `beer-
sample`(abv, state);
create index idxtypeabvstate on
`beer-sample`(abv, state) where type =
'beer';
 Need to have matching indices with right key-
ordering

GSI Index: Key details
 SupportedTypes
 String, Boolean, Numeric, Nil,Array, Sub-document
 Comp
 Total length of the keys
 4 KB – actual length of the key indexed
 Number of keys
 4096!

Query Execution: Plan
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan
create index idxbeerabv on
`beer-sample`(abv, state);
EXPLAIN SELECT count(*)
FROM `beer-sample`
WHERE abv > 5.0 AND state = 'California';
select *
from system:indexes
where keyspace_id = 'beer-sample';
 Explain provides the JSON representation of the
query plan
 Focus on the index selection and the predicates
pushed down

Query Execution: Plan
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan
create index idxtypeabvstate on `beer-
sample`(abv, state) where type = ‘beer’;
select * from system:indexes where name =
'idxtypeabvstate';
"indexes": {
"condition": "(`type` = "beer")",
"datastore_id": "http://127.0.0.1:8091",
"id": "611ffca1720b7868",
"index_key": [
"`abv`",
"`state`"
],
"keyspace_id": "beer-sample",
"name": "idxtypeabvstate",
"namespace_id": "default",
"state": "online",
"using": "gsi"
}

Plan: Index selection – Partial Index
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan
create index idxabvp1
on `beer-sample`(abv, state)
where type = 'beer' and abv > 5.0;
explain select count(*)
from `beer-sample`
where type = 'beer' and abv = 7.0 ;

Power Features: IntersectScan (Multi-Index Scan)
create index idxbeerabv on `beer-sample`(abv);
create index idxbeercat on `beer-sample`(category);
select *
from `beer-sample`
where abv > 15 and category like '%North%';
 IntersectScan using multiple indices:
– Multiple indices are scanned in parallel
– Provides more flexibility in using the indices for filters
– Requires less number of indexes defined on table.
• Can save on disk space and memory space as well.

IntersectScan method using multiple indices
select *
from `beer-sample`
 IntersectScan using multiple indices:
– Multiple indices are scanned in parallel
– Provides more flexibility in using the indices for filters
– Requires less number of indexes defined on table.
• Can save on disk space and memory space as well.

Plan Directives: USE INDEX
select *
from `beer-sample` USE INDEX (idxbeerabv)
select *
from `beer-sample` USE INDEX (idxbeerabv, idxbeercat)
where abv > 15 and category like '%North%’;
 USE INDEX suggests one or more appropriate indices
 Optimizer validates the indices. Primary Index is the backup index.
 Provides stability to the optimizer plan.

Query Execution: Fetch
 List of qualified document-keys are grouped into batches.
 List of the documents is obtained from the Index or specified
directly via USE KEYS clause.
 Fetch request is done in parallel.
 The join operation use the fetch operation to get the
matching document.
 Fetch results are streamed into next operators.
 For big queries, scan-fetch-join-filter-aggregation will be
executing in parallel.
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan

JOINS

Query Execution: Join
 You can join any two key spaces if one has document-key of
the other.
 You can store multiple entities within the same bucket and
join between distinct groups
 Uses Nested Loop JOIN now
 JOINs are done in the same order specified in the query
 Index selection is important for the first keyspace in the
FROM clause.
 Qualified documents from that scan is joined with the other
Keyspace using the DOCUMENT KEYS
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan

"CUSTOMER": {
"C_D_ID": 10,
"C_ID": 1938,
"C_W_ID": 1,
"C_BALANCE": -10,
"C_CITY": ”San Jose",
"C_CREDIT": "GC”,
"C_DELIVERY_CNT": 0,
"C_DISCOUNT": 0.3866,
"C_FIRST": ”Jay",
"C_LAST": ”Smith",
"C_MIDDLE": "OE",
"C_PAYMENT_CNT": 1,
"C_PHONE": ”555-123-1234",
"C_SINCE": "2015-03-22 00:50:42.822518",
"C_STATE": ”CA",
"C_STREET_1": ”555, Tideway Drive",
"C_STREET_2": ”Alameda",
"C_YTD_PAYMENT": 10,
"C_ZIP": ”94501"
}
Document key: “1.10.1938” Document key: “1.10.143”
“ORDERS”: {
“O_CUSTOMER_KEY”: “1.10.1938):
"O_D_ID": 10,
"O_ID": 1,
"O_ALL_LOCAL": 1,
"O_CARRIER_ID": 2,
"O_C_ID": 1938,
"O_ENTRY_D": "2015-05-19 16:22:08.544472",
"O_ID": 143,
"O_OL_CNT": 10,
"O_W_ID": 1
}x
“ORDERS”: {
“O_CUSTOMER_KEY”: “1.10.1938”):
"O_ALL_LOCAL": 1,
"O_CARRIER_ID": 2,
"O_C_ID": 1938,
"O_D_ID": 10,
"O_ENTRY_D": "2015-05-19 16:22:08.544472",
"O_ID": 1355,
"O_OL_CNT": 10,
"O_W_ID": 3
}
Document key: “1.10.1355”

SELECT beer.NAME beername,
brewery.NAME brewery
FROM `beer-sample` beer
LEFT OUTER JOIN `beer-sample` brewery
ON KEYS beer.brewery_id
WHERE beer.type = 'beer'
AND brewery.type = 'brewery';
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan

N1QL: Join
SELECT *
FROM ORDERS o INNER JOIN CUSTOMER c
ON KEYS (o.O_C_ID)
LEFT JOIN PREMIUM p
ON KEYS (c.C_PR_ID)
INNER JOIN demographics d
ON KEYS (c.c_DEMO_ID)
Fetch
Parse
Plan
Join
Filter
Offset
Limit
Project
Sort
Aggre
gate
Scan
 Support INNER and LEFT OUTER joins
 Join order follows the order in the FROM clause.
 N1QL supports the nested loop joins now.
 Join is always from a key of one document(outer
table) to the document key of the second
document (inner table)

Covering Index

N1QL: Query Execution Flow
Clients
3. Scan Request;
index filters
6. Fetch the documents
Index
Servic
e
Query
Service
Data
Servic
e
& index keys
5. Fetch Request,
doc keys
SELECT c_id,
c_first,
c_last,
c_max
FROM CUSTOMER
WHERE c_id = 49165;
{
"c_first": "Joe",
"c_id": 49165,
"c_max" : 50000
}

N1QL: covering index & a covered query
Clients
3. Scan Request;
index filters
Index
Servic
e
Query
Service
& index keys
Create index i1 on CUSTOMER(c_id, c_max, c_first, c_last)
SELECT c_id,
c_first,
c_last,
c_max
FROM CUSTOMER
WHERE c_id = 49165;
{
"c_first": "Joe",
"c_id": 49165,
"c_max" : 50000
}

Inside a Query Service (without covering index)
Client
Pre-Aggregate
Query Service
Index
Servic
e
Data
Servic
e

Inside a Query Service (with Covering index)
Client
Parse Plan Join Filter
Pre-Aggregate
Query Service
Index
Servic
e
Data
Servic
e

Covering indexes
 Reason for covering indexes is performance.
 Queries should get the benefit automatically.
 Index selection is still based on filters inWHERE clause only.
– Projected expressions are not considered for index selection.
– When there are multiple qualifying indices for the query, use USE INDEX hint to favor
covering index path.
 The covering index & expressions are noted in the explain.
 Only applies to Secondary Index scans
 Does not exploit the index ordering for ORDER BY… yet!
 Used in SELECT and INSERT INTO…SELECT only
 No path UPDATE, DELETE, MERGE (subqueries need USE KEYS)

create index idxstatecity on `beer-
sample`(state,city) using gsi;
EXPLAIN
SELECT city
FROM`beer-sample`
WHERE state = 'California';
N1QL: Example
{
"requestID": "eef73760-d09e-48e0-a43a-c8da1e0be998
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"covers": [
"cover((meta(`beer-sample`).`id`))",
"cover((`beer-sample`.`state`))",
"cover((`beer-sample`.`city`))"
],
"index": "idxstatecity",
"keyspace": "beer-sample",
"namespace": "default",
"spans": [
{
]

create index idxstate on `beer-sample’(state) using gsi;
Create index idxstatecountry on `beer-sample(state, country) using gsi;
-- covered by idxstatecountry
select country from `beer-sample` where state = 'CA';
-- use directives to choose the optimal index…
select country from `beer-sample` use index (idxstatecountry) where state = 'CA';
-- Queries with expressions and aggregates can still use the covering index
explain select max(country) from `beer-sample` where state = 'CA';
explain select country || state
from `beer-sample` use index (idxstatecountry) where state = 'CA';
-- Even the expressions in order by will be covered
explain select country || state
from `beer-sample` use index (idxstatecountry)
where state = 'CA’
order by state;
N1QL: Examples

select country, max(state)
from `beer-sample` use index (idxstatecountry)
where state like '%' group by country;
-- UNION/ALL/INTERSECT/EXCEPT queries are supported
select country from `beer-sample` where state = 'CA'
UNION ALL
select country from `beer-sample` where state = 'Texas';
-- derived tables/subqueries
Select *
From (
select country from `beer-sample` where state = 'CA'
UNION ALL
select country from `beer-sample` where state = 'Texas’;
) as newtab;
N1QL: Examples

-- select in insert statement
insert into `travel-sample`(KEY k, value state)
Select country as k, state from `beer-sample b where stat = ‘ca’;
-- Supports Arrays
Create index idxarray on `beer-sample`(a, b);
Select b from `beer-sample` where a = [1, 2, 3, 4];
N1QL: Examples

-- Cannot use any index. Uses Primary scan.
select state, brewery from `beer-sample` where country = 'United States’;
— Uses index when appropriate.
Select state from `beer-sample` where state = 'CA'
UNION ALL
Select state from `travel-sample` where state = 'CA';
-- Uses index
select b.state, t.beer
from `beer-sample` as b inner join `travel-sample` as t
on keys (b.state)
where b.state = ‘ca’;
N1QL: Examples

FAQ
 Supports both GSI andVIEW indexes
 Supports partial indexes as well (create index withWHERE clause)
 Supports prepared statements
 Supports using key-only scan in any "query block" within a complex
statement.
– E.g. UNION/UNION-ALL/INTERSECT/EXCEPT and derived table queries
– Essentially, whenever a decision is made on the access path,
– covering-index-path is one of the options.
 Push the LIMIT+OFFSET to Index scan & data fetch
– SELECT state, country from `beer-sample` where state like ‘%’ order by state,
country LIMIT 10 OFFSET 20;

Summary: SQL & N1QL
Query Features SQL N1QL
Statements
 SELECT, INSERT, UPDATE, DELETE,
MERGE
 SELECT, INSERT, UPDATE, DELETE,
MERGE
Query Operations
 Select, Join, Project, Subqueries
 Strict Schema
 StrictType checking
 Select, Join, Project, Subqueries
 Nest & Unnest
 Look Ma! NoType Mismatch Errors!
 JSON keys act as columns
Schema  Predetermined Columns
 Fully addressable JSON
 Flexible document structure
DataTypes
 SQL Data types
 Conversion Functions
 JSON Data types
 Conversion Functions
Query Processing
 INPUT: Sets ofTuples
 OUPUT: Set ofTuples
 INPUT: Sets of JSON
 OUTPUT: Set of JSON

Summary

Query Changes the Possibilities for NoSQL

Query in Couchbase. N1QL: SQL for JSON

Recommended

More Related Content

What's hot (20)

Viewers also liked (13)

Similar to Query in Couchbase. N1QL: SQL for JSON (20)

More from Keshav Murthy (17)

Recently uploaded (20)

Query in Couchbase. N1QL: SQL for JSON

Editor's Notes