cfcouchbase 2.0 and n1ql

CFCOUCHBASE

2.0INTRODUCING

AARON BENTON @bentonam BRAD WOOD @bdw429s@ORTUSSOLUTIONS

bit.ly/cfcouchbase2Slidedeck:

• HISTORY

• CF EXAMPLES

• N1QL EXAMPLES

• SDK CHANGES

• SDK ROADMAP

• LOAD DEMO

PROJECT INCEPTION

Started Oct. 2013

RELEASE 1.0

1.0 was released Feb. 2014

RELEASE 1.1

1.1 was released Jan. 2015

RELEASE 2.0

2.0 was released June 2016

RELEASE 2.0 BRINGS

• JAVA SDK (2.2.5) • N1QL SUPPORT W/ GSI INDEXES • REPLICA READS • DOCUMENT LOCKING • PREPARED STATEMENTS • DESIGN DOCUMENT MANAGEMENT • EXPANDED CONFIG • AND MORE…

METHOD HOUSEKEEPINGasyncCounter asyncDecr

asyncGet asyncGetAndTouch

asyncGetMulti asyncGetWithCAS

asyncIncr

asyncSaveView getDocStats

UNSUPPORTED METHODSadd decr

incr delete

newQuery deleteDesignDocument

set

setMulti setWithCAS

DEPRECATED METHODS NEW METHODS

counter

exists getAndLock

getEnvironment getFromReplica

getIndexes getUtility

insert invalidateQueryCache

n1qlQuery publishDesignDocument

remove removeDesignDocument

replaceWithCAS unlock

upset upsertMulti

viewQuery

METHOD HOUSEKEEPING1.* METHODS 2.* METHODS

add insert

incr decr

counter

delete remove

newQuery n1qlQuery viewQuery

deleteDesignDocument removeDesignDocument

set upsert replace

setMulti upsertMulti

setWithCAS replaceWithCAS

The query() method will still be implemented and is a facade for both n1ql and view queries

CONFIG HOUSEKEEPINGbucketName dataMarshaller defaultTimeout password servers useClassLoader viewTimeout

SUPPORTED OPTIONS

maxReconnectDelay obsPollInterval obsPollMax opQueueMaxBlockTime opTimeout readBufferSize shouldOptimize timeoutExceptionThreshold

UNSUPPORTED OPTIONS

autoReleaseAfter bootstrapCarrierDirectPort bootstrapCarrierEnabled bootstrapCarrierSslPort bootstrapHttpDirectPort bootstrapHttpEnabled bootstrapHttpSslPort bufferPoolingEnabled caseSensitiveKeys computationPoolSize connectTimeout dcpEnabled defaultMetricsLoggingConsumer disconnectTimeout dnsSrvEnabled eventBus ioPool ioPoolSize keepAliveInterval kvEndpoints kvTimeout managementTimeout

NEW OPTIONSmaxRequestLifetime mutationTokensEnabled networkLatencyMetricsCollectorConfig observeIntervalDelay packageNameAndVersion queryEnabled queryEndpoints queryPort queryTimeout reconnectDelay requestBufferSize responseBufferSize retryDelay retryStrategy runtimeMetricsCollectorConfig scheduler sslEnabled sslKeystoreFile sslKeystorePassword tcpNodelayEnabled userAgent viewEndpoints

NEW OPTIONS CONT'D

http://developer.couchbase.com/documentation/server/current/sdks/java-2.2/env-config.html#story-h2-2

N1QL- Non 1st Normal Form Query Language- Familiar SQL like syntax- MapReduce Views

- Steep Learning Curve- Limited Capabilities

- N1QL requires Couchbase Server 4.0+

ARITHMETIC + - * / % -val

COLLECTION ANY EVERY ARRAY FIRST EXISTS IN WITHIN

COMPARISON = == != <> > >= < <= (NOT) BETWEEN (NOT) LIKE IS (NOT) NULL IS (NOT) MISSING IS (NOT) VALUED

CONDITIONAL CASE expression WHEN value THEN expression

CONSTRUCTION Array [ value, value, ... ] Object { key:value, key:value, ... }

LOGICAL AND OR NOT

STRING ||

N1QL OPERATORS

http://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/operators.html

N1QL FUNCTIONSABS(EXP) ACOS(EXP) ASIN(EXP) ATAN(EXP) ATAN2(EXP1, EXP2) CEIL(EXP) COS(EXP) DEGREES(EXP) E() EXP(EXP) LN(EXP) LOG(EXP) FLOOR(EXP) PI() POWER(EXP1, EXP2) RADIANS(EXP) RANDOM([ EXP ]) ROUND(EXP [, DIGITS ]) SIGN(EXP) SIN(EXP) SQRT(EXP) TAN(EXP) TRUNC(EXP [, DIGITS ])

NUMBER FUNCTIONSIFMISSING(EXP1, EXP2, ...) IFMISSINGORNULL(EXP1, EXP2, ...) IFNULL(EXP1, EXP2, ...) MISSINGIF(EXP1, EXP2) NULLIF(EXP1, EXP2)

CONDITIONALS - UNKNOWNS

IFINF(EXP1, EXP2, ...) IFNAN(EXP1, EXP2, ...) IFNANORINF(EXP1, EXP2, ...) NANIF(EXP1, EXP2) NEGINFIF(EXP1, EXP2) POSINFIF(EXP1, EXP2)

CONDITIONALS - NUMBERS

ARRAY_AGG(EXP) ARRAY_AGG(DISTINCT EXP) AVG(EXP) AVG(DISTINCT EXP) COUNT(*) COUNT(EXP) COUNT(DISTINCT EXP) MAX(EXP) MIN(EXP) SUM(EXP) SUM(DISTINCT EXP)

AGGREGATE FUNCTIONS

OBJECT_LENGTH(EXP) OBJECT_NAMES(EXP) OBJECT_PAIRS(EXP) OBJECT_VALUES(EXP)

OBJECT FUNCTIONS

GREATEST(EXP1, EXP2) LEAST(EXP1, EXP2)

COMPARISON FUNCTIONS

DECODE_JSON(EXP) ENCODE_JSON(EXP) ENCODED_SIZE(EXP) POLY_LENGTH(EXP)

JSON FUNCTIONSBASE64(EXP) BASE64_ENCODE(EXP) BASE64_DECODE(EXP) META(EXP) UUID

META AND UUID FUNCTIONS

TYPE CHECKING FUNCTIONSISARRAY(EXP) ISATOM(EXP) ISBOOLEAN(EXP) ISNUMBER(EXP) ISOBJECT(EXP) ISSTRING(EXP) TYPE(EXP)

TYPE CONVERSION FUNCTIONSTOARRAY(EXP) TOATOM(EXP) TOBOOLEAN(EXP) TONUMBER(EXP) TOOBJECT(EXP) TOSTRING(EXP)

http://developer.couchbase.com/documentation/server/4.1/n1ql/n1ql-language-reference/functions.html

N1QL FUNCTIONSARRAY_APPEND(EXP, VAL) ARRAY_AVG(EXP) ARRAY_CONCAT(EXP1, EXP2) ARRAY_CONTAINS(EXP, VAL) ARRAY_COUNT(EXP) ARRAY_DISTINCT(EXP) ARRAY_IFNULL(EXP) ARRAY_LENGTH(EXP) ARRAY_MAX(EXP) ARRAY_MIN(EXP) ARRAY_POSITION(EXP, VAL) ARRAY_PREPEND(VAL, EXP) ARRAY_PUT(EXP, VAL) ARRAY_RANGE(START, END [,STEP]) ARRAY_REMOVE(EXP, VAL) ARRAY_REPEAT(VAL, N) ARRAY_REPLACE(EXP, VAL1, VAL2 [,N]) ARRAY_REVERSE(EXP) ARRAY_SORT(EXP) ARRAY_SUM(EXP)

ARRAY FUNCTIONSCONTAINS(EXP, SUBSTRING) INITCAP(EXP ) TITLE(EXP) LENGTH(EXP) LOWER(EXP) LTRIM(EXP [,CHARACTERS ]) POSITION(EXP, SUBSTRING) REPEAT(EXP, N) REPLACE(EXP, SBSTR, REPL [, N ]) RTRIM(EXP, [,CHARACTERS ]) SPLIT(EXP [, SEP ]) SUBSTR(EXP, POS[, LEN ]) TRIM(EXP [, CHARACTERS ]) UPPER(EXP)

STRING FUNCTIONS

CLOCK_MILLIS() CLOCK_STR ([FMT ]) DATE_ADD_MILLIS(EXP, N, PART) DATE_ADD_STR(EXP, N,PART) DATE_DIFF_MILLIS(EXP1, EXP2, PART) DATE_DIFF_STR(EXP1, EXP2, PART) DATE_PART_MILLIS(EXP, PART) DATE_PART_STR(EXP, PART) DATE_TRUNC_MILLIS(EXP, PART) DATE_TRUNC_STR(EXP, PART) MILLIS(EXP) STR_TO_MILLIS(EXP) MILLIS_TO_STR(EXP [, FMT ]) MILLIS_TO_UTC(EXP [, FMT ]) MILLIS_TO_ZONE_NAME(EX, TZ[,FMT]) NOW_MILLIS() NOW_STR([ FMT ]) STR_TO_MILLIS(EXP) MILLIS(EXP) STR_TO_UTC(EXP) STR_TO_ZONE_NAME(EXP, TZ_NAME)

DATE FUNCTIONS

REGEXP_CONTAINS(EXP, PATTERN) REGEXP_LIKE(EXP, PATTERN) REGEXP_POSITION(EXP, PATTERN) REGEXP_REPLACE(EXP, PTRN, REPL [, N ])

PATTERN MATCHING FUNCTIONS

RETRIEVE DOCUMENTSELECT * FROM usersUSE KEYS "user_101"

SELECT BY DOCUMENT IDcouchbase.get("user_101");

STANDARD SDK OPERATION

[ { "users": { "email": "[email protected]", "first_name": "Albin", "last_name": "Price", "user_id": 101, "username": "Eudora43" } }]

RESULTS

GET MULTIPLE DOCUMENTSSELECT * FROM usersUSE KEYS ["user_101","user_454"]

SELECT BY DOCUMENT IDS

couchbase.getMulti([ "user_101", "user_454"]);


[ { "users": { "email": "[email protected]", "first_name": "Albin", "last_name": "Price", "user_id": 101, "username": "Eudora43" } }, { "users": { "email": "[email protected]", "first_name": "Donnell", "last_name": "Ortiz", "user_id": 454, "username": "Garett31" } }]

RESULTS

SELECT DIFFERENCESSELECT * FROM usersUSE KEYS "user_101"

SELECT *

[ { "users": { "email": "[email protected]", "first_name": "Albin", "last_name": "Price", "user_id": 101, "username": "Eudora43" } }]

RESULTS

SELECT users.* FROM usersUSE KEYS "user_101"

SELECT ENTITY.*

[ { "email": "[email protected]", "first_name": "Albin", "last_name": "Price", "user_id": 101, "username": "Eudora43" }]

RESULTS

Documents are projected as the bucket or alias name

?QUERY BY VALUE

SELECT u.* FROM users AS uWHERE u.username = 'Eudora43'

QUERY BY USERNAME

[ { "email": "[email protected]", "first_name": "Albin", "last_name": "Price", "user_id": 197, "username": "Eudora43" }]

RESULTS


// perform view queryresults = couchbase.query( designDocumentName="user", viewName="username", options={ key: "eudora43" });

// get the document by the id // returned from the queryuser = couchbase.get(results[1].id);

function (doc, meta) { if( doc.username ){ emit( doc.username.toLowerCase(), null ); }}

VIEW MAP FUNCTION

These queries are different. The N1QL query does not take case into consideration the View does. N1QL string operators are case sensitive, the N1QL query would need to use the LOWER() function

This can be done without N1QL, however it would require a MapReduce View

This query would require a secondary index

DML OPERATIONS

INSERTINSERT INTO users (KEY, VALUE)VALUES ("user_1021", { "user_id": 1021, "name": "John Smith", "email": "[email protected]"})

N1QL INSERTcouchbase.insert("user_1021", { "user_id": 1021, "name": "John Smith", "email": "[email protected]"});


{ "results": [], "metrics": { "elapsedTime": "50.361685ms", "executionTime": "50.322691ms", "resultCount": 0, "resultSize": 0, "mutationCount": 1 }}

RESULTS

RETURNINGINSERT INTO users (KEY, VALUE)VALUES ("user_2343", { "user_id": 2343, "name": "John Smith", "email": "[email protected]"})RETURNING *

N1QL INSERT W/ RETURNING// write the user documentcouchbase.insert("user_2343", { "user_id": 2343, "name": "John Smith", "email": "[email protected]"});

// get the user documentuser = couchbase.get("user_2343");


[ { "users": { "email": "[email protected]", "name": "John Smith", "user_id": 2343 } }]

RESULTS

UPDATE// get the user documentuser = couchbase.get("user_2343");

// change the email valueuser.email = "[email protected]";

// update the entire documentcouchbase.replace("user_2343", user);

// read the write to get just the // updated email valueuser = couchbase.get("user_2343"); email = user.email;

STANDARD SDK OPERATIONN1QL UPDATEUPDATE usersUSE KEYS "user_2343"SET email = "[email protected]" RETURNING email

SDK replace() will error if the document does not exist, N1QL won’t

[ { "email": "[email protected]" }]

RESULTS

UPSERT// write the user documentcouchbase.upsert("user_2343", { "user_id": 2343, "name": "John Smith", "email": "[email protected]"});

// get the user documentuser = couchbase.get("user_2343");

UPSERT INTO users (KEY, VALUE)VALUES ("user_2343", { "user_id": 2343, "name": "John Smith", "email": "[email protected]"})RETURNING META().id AS document_id

N1QL UPSERT STANDARD SDK OPERATION

[ { "document_id": "user2343" }]

RESULTS

DELETEDELETE FROM usersUSE KEYS "user_2343"

N1QL DELETE// write the user documentcouchbase.remove("user_2343");


{ "results": [], "metrics": { "elapsedTime": "21.591512ms", "executionTime": "21.559566ms", "resultCount": 0, "resultSize": 0 }}

RESULTSSDK remove() will error if the document does not exist, N1QL won’t

INDEXING

VIEW VS. GSI INDEXESGSI INDEXES VIEW INDEXES

Partitioning Model• Part of Index Service • Each GSI Must be Unique • Can only be placed on one node • Can respond without scatter-gather

• Auto-partitioned with Data Service • Expensive scatter-gather operations

Scaling Model • Scales with Index Service • Scales with Data Service

Performance • Singleton Lookups or Range Scans can respond without scatter-gather

• Singleton Lookups or Range Scans can respond without scatter-gather

Managed Cache • Comes with Managed Cache that performs better during Index Maintenance and Scans

• Depends on File System Cache

Storage Engine • ForestDB • Couchstore

High Availability • Multiple indexes with identical definitions across multiple nodes • Built-in replicas with smart placement

INDEXES http://developer.couchbase.com/documentation/server/current/indexes/gsi-for-n1ql.html

• "USE KEYS" statements do not

require GSI Indexes • All other N1QL queries require

GSI Indexes

• PRIMARY Index contains all keys

• Ad hoc queries require a

PRIMARY Index

• Index Names must be unique

• USING GSI is the default

CREATE PRIMARY INDEX idx_users_primary ON usersUSING GSI

PRIMARY INDEX

CREATE INDEX idx_users_username ON users( username )USING GSI

SECONDARY INDEX

DROP PRIMARY INDEX ON idx_users_primary ON usersUSING GSI

DROP PRIMARY INDEX

DROP PRIMARY INDEX idx_users_primary ON usersUSING GSI

DROP SECONDARY INDEX

EXPLAINEXPLAINSELECT u.* FROM users AS uWHERE u.username = 'Eudora43'

QUERY BY USERNAME[ { "plan": { "#operator": "Sequence", "~children": [ { "#operator": "IndexScan", "index": "idx_users_username", "index_id": "240a59c3f5ced906", "keyspace": "social", "namespace": "default", ... ], "using": "gsi" }, ... ] }, ... }]

RESULTS

http://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/explain.html

INDEX SCAN TYPES

• PRIMARYSCAN

• INDEXSCAN

• KEYSCAN

• PARENTSCAN

• VALUESCAN

• DUMMYSCAN

• COUNTSCAN

• INTERSECTSCAN

Reference https://github.com/couchbase/query#plan for Scan explanations

INDEXESCREATE INDEX idx_users_username ON users( username )WHERE username IS NOT MISSINGUSING GSI

FILTERED INDEX

CREATE INDEX idx_users_username ON users( username )WHERE doc_type = 'user'USING GSI

FILTERED INDEX

• Definitions can be duplicated • Indexes can be partitioned • Randomly assigned to an Index

nodes in the cluster

INDEXESCREATE INDEX idx_users_username ON users( username )WHERE username IS NOT MISSINGUSING GSIWITH { "defer_build": true }

DEFER THE INDEX BUILD

BUILD INDEX ON users( idx_users_username, idx_users_email )USING GSI

BUILD AN INDEX

CREATE INDEX idx_users_username ON users( username )WHERE username IS NOT MISSINGUSING GSIWITH { "nodes": "node3:8091"}

ADD INDEX TO SPECIFIC NODE

• Index builds can be deferred

• Multiple Indexes can be built at a

time

• Indexes can be deployed to

specific nodes

• GSI Indexes are not rebalanced

INDEX LOAD BALANCINGCREATE INDEX idx_users_username_AtoM ON users( username )WHERE doc_type = 'user' AND username BETWEEN 'A' AND 'N'USING GSI

RANGE PARTITIONED INDEXES

CREATE INDEX idx_users_username_NtoZ ON users( username )WHERE doc_type = 'user' AND username BETWEEN 'N' AND 'Z'USING GSI

- A round robin algorithm is used to distribute index load- If a node responsible for a index goes down, a PrimaryIndexScan would be used, or the query would fail if there is no PRIMARY INDEX

CREATE INDEX idx_users_username_AtoM_node1 ON users( username )WHERE doc_type = 'user' AND username BETWEEN 'A' AND 'N'USING GSI WITH { "nodes": "node1:8091"}

REDUNDANT / REPLICATED INDEXES

CREATE INDEX idx_users_username_NtoZ_node1 ON users( username )WHERE doc_type = 'user' AND username BETWEEN 'N' AND 'Z'USING GSI WITH { "nodes": "node1:8091"}

CREATE INDEX idx_users_username_AtoM_node2 ON users( username )WHERE doc_type = 'user' AND username BETWEEN 'A' AND 'N'USING GSI WITH { "nodes": "node2:8091"}

CREATE INDEX idx_users_username_NtoZ_node2 ON users( username )WHERE doc_type = 'user' AND username BETWEEN 'N' AND 'Z'USING GSI WITH { "nodes": "node2:8091"}

ADVANCED SELECTS

MISSING ATTRIBUTESSELECT u.user_id, u.email, u.name FROM users AS uUSE KEYS "user_197"

QUERY

{ "email": "[email protected]", "first_name": "Albin", "last_name": "Price", "user_id": 197, "username": "Eudora43"}

SAMPLE DOCUMENT

[ { "email": "[email protected]" "user_id": 197 }]

RESULT

Requested attributes that are missing are not returned as part of the response

MISSING ATTRIBUTESSELECT u.user_id, u.email, IFMISSING( u.name, u.first_name || ' ' || u.last_name ) AS nameFROM users AS uUSE KEYS "user_197"

QUERY

{ "email": "[email protected]", "first_name": "Albin", "last_name": "Price", "user_id": 197, "username": "Eudora43"}

SAMPLE DOCUMENT

[ { "email": "[email protected]", "name": "Albin Price", "user_id": 197 }]

RESULT

SELECT airlines.airline_id, airlines.airline_name, IFNULL( airlines.airline_iata, airlines.airline_icao ) AS airline_codeFROM `flight-data` AS codesUSE KEYS 'airline_code_DL'INNER JOIN `flight-data` AS airlinesON KEYS 'airline_' || TOSTRING( codes.id )LIMIT 1

QUERY

[ { "airline_code": "DL", "airline_id": 2009, "airline_name": "Delta Air Lines" }]

RESULT

{ "_id": "airline_code_DL", "code": "DL", "code_type": "iata", "designation": "airline", "doc_type": "code", "id": 2009}

AIRLINE LOOKUP DOCUMENT

{ "_id": "airline_2009", "active": true, "airline_iata": "DL", "airline_icao": "DAL", "airline_id": 2009, "airline_name": "Delta Air Lines", "callsign": "DELTA", "doc_type": "airline", "iso_country": "US"}

AIRLINE DOCUMENT

JOINS

MULTIPLE JOINS{ "_id": "route_6cea35d1-37e1-5425-913f-42c29e248285", "airline_code": "DL", "destination_airport_code": "MSP", "doc_type": "route", "route_id": "6cea35d1-37e1-5425-913f-42c29e248285", "source_airport_code": "BWI"}

ROUTE DOCUMENT

{ "_id": "airline_code_DL", "code": "DL", "code_type": "iata", "designation": "airline", "doc_type": "code", "id": 2009}

AIRLINE LOOKUP DOCUMENT

{ "_id": "airline_2009", "airline_iata": "DL", "airline_icao": "DAL", "airline_id": 2009, "airline_name": "Delta Air Lines", "doc_type": "airline"}

AIRLINE DOCUMENT

{ "_id": "airport_code_BWI", "code": "BWI", "code_type": "iata", "designation": "airport", "doc_type": "code", "id": 3435}

AIRPORT LOOKUP DOCUMENT

{ "_id": "airport_3435", "airport_iata": "BWI", "airport_icao": "KBWI", "airport_id": 3435, "airport_ident": "KBWI", "airport_name": "Baltimore Washington Intl", "airport_type": "large_airport", "doc_type": "airport", "elevation": 146, "geo": { "latitude": 39.17539978, "longitude": -76.66829681 }, "iso_continent": "NA", "iso_country": "US", "iso_region": "US-MD", "municipality": "Baltimore"}

AIRPORT DOCUMENT

SELECT airlines.airline_name, IFNULL( airlines.airline_iata, airlines.airline_icao ) AS airline_code, source_airports.airport_name, source_airports.iso_country, source_airports.iso_region, IFNULL( source_airports.airport_iata, source_airports.airport_icao, source_airports.airport_ident ) AS airport_codeFROM `flight-data` AS routes

/* get airline details */INNER JOIN `flight-data` AS airline_codes ON KEYS 'airline_code_' || routes.airline_codeINNER JOIN `flight-data` AS airlines ON KEYS 'airline_' || TOSTRING( airline_codes.id )

/* get airport details */INNER JOIN `flight-data` AS airport_codes ON KEYS 'airport_code_' || routes.source_airport_codeINNER JOIN `flight-data` AS source_airports ON KEYS 'airport_' || TOSTRING( airport_codes.id )

WHERE routes.destination_airport_code = 'MSP' AND routes.source_airport_code IS NOT NULL AND routes.doc_type = 'route' AND routes.active = trueORDER BY source_airports.airport_name ASC, airlines.airline_name ASC

QUERY

[ { "airline_code": "DL", "airline_name": "Delta Air Lines", "airport_code": "ABR", "airport_name": "Aberdeen Regional Airport", "iso_country": "US", "iso_region": "US-SD" }, { "airline_code": "DL", "airline_name": "Delta Air Lines", "airport_code": "ABQ", "airport_name": "Albuquerque International Sunport", "iso_country": "US", "iso_region": "US-NM" }, { "airline_code": "DL", "airline_name": "Delta Air Lines", "airport_code": "BWI", "airport_name": "Baltimore Washington Intl", "iso_country": "US", "iso_region": "US-MD" }, ...]

RESULT

MULTIPLE JOINS

ANY ANY variable ( IN | WITHIN ) expression [ , variable ( IN | WITHIN ) expression ]* SATISFIES condition END

EVERYEVERY variable ( IN | WITHIN ) expression [ , variable ( IN | WITHIN ) expression ]* SATISFIES condition END

ARRAY ARRAY expression FOR variable ( IN | WITHIN ) expression [ , variable ( IN | WITHIN ) expression ]* [ ( WHEN condition) ] END

FIRSTFIRST expression FOR variable ( IN | WITHIN ) expression [ , variable ( IN | WITHIN ) expression]* [ ( WHEN condition) ] END

EXISTS EXISTS expression

IN expression [ NOT ] IN expression

WITHIN expression [NOT] WITHIN expression

COLLECTION OPERATORS

http://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/collectionops.html

ANY OPERATORSELECT u.name, u.childrenFROM users AS uWHERE u.doc_type = 'user' AND ANY child IN u.children SATISFIES child.age > 10 ENDORDER BY ARRAY_LENGTH(u.children) ASC

QUERY

[ { "children": [ { "age": 12, "first_name": "Emelia", "gender": "M" } ], "name": "Elyse Rempel" }, { "children": [ { "age": 15, "first_name": "Yvette", "gender": "F" }, { "age": 8, "first_name": "Rodolfo", "gender": "M" } ], "name": "Reynold Mohr" }, ...]

RESULT

EVERY OPERATORSELECT u.name, u.childrenFROM users AS uWHERE u.doc_type = 'user' AND EVERY child IN u.children SATISFIES child.age > 10 AND child.gender = 'M' ENDORDER BY ARRAY_LENGTH(u.children) ASC

QUERY

[ { "children": [ { "age": 16, "first_name": "Grant", "gender": "M" } ], "name": "Joseph Stiedemann" }, { "children": [ { "age": 11, "first_name": "Ezekiel", "gender": "M" }, { "age": 12, "first_name": "Brooks", "gender": "M" } ], "name": "Aiden Christiansen" }, ...]

RESULT

ARRAY INDEXING

[ { "chat_id": "61bf583b2cd0", "created_on": "2016-05-29T13:31:29.759Z" }, { "chat_id": "d63a56047e08", "created_on": "2016-05-29T10:07:18.824Z" }, ...]

RESULT

SELECT chats.chat_id, MILLIS_TO_STR(chats.created_on) AS created_onFROM social AS chatsWHERE ANY user_id IN chats.users SATISFIES user_id = 100 END AND chats.doc_type = 'chat'ORDER BY chats.created_on DESC

QUERYCREATE INDEX idx_chats_users ON social( DISTINCT ARRAY user_id FOR user_id IN users WHEN user_id IS NOT NULL END, doc_type)WHERE doc_type = 'chat'

INDEX

{ "_id": "chat_4ff8dd056225", "doc_type": "chat", "chat_id": "4ff8dd056225", "created_on": 1464518742983, "users": [ 861, 492 ]}

SAMPLE DOCUMENT

{ "_id": "user_764", "doc_type": "user", "user_id": 764, "first_name": "Geovanny", "last_name": "Parker", "phones": [ { "type": "Mobile", "phone_number": "676.825.8926", "extension": null }, { "type": "Mobile", "phone_number": "792.877.3144", "extension": "3644" }, { "type": "Home", "phone_number": "(730) 490-6734", "extension": null } ]}

SAMPLE DOCUMENT

SELECT u.phonesFROM users AS uUSE KEYS 'user_764'

QUERY

[ { "phones": [ { "type": "Mobile", "phone_number": "676.825.8926", "extension": null }, { "type": "Mobile", "phone_number": "792.877.3144", "extension": "3644" }, { "type": "Home", "phone_number": "(730) 490-6734", "extension": null } ] }]

RESULT

UNNEST

This query will only provide a single result with a nested "phones" attribute that is an array

UNNEST{ "_id": "user_764", "doc_type": "user", "user_id": 764, "first_name": "Geovanny", "last_name": "Parker", "phones": [ { "type": "Mobile", "phone_number": "676.825.8926", "extension": null }, { "type": "Mobile", "phone_number": "792.877.3144", "extension": "3644" }, { "type": "Home", "phone_number": "(730) 490-6734", "extension": null } ]}

SAMPLE DOCUMENT

SELECT phone_numbers.*FROM users AS uUSE KEYS 'user_23'UNNEST u.phones AS phone_numbers

QUERY

[ { "type": "Mobile", "phone_number": "676.825.8926", "extension": null }, { "type": "Mobile", "phone_number": "792.877.3144", "extension": "3644" }, { "type": "Home", "phone_number": "(730) 490-6734", "extension": null }]

RESULT

UNNSET performs a JOIN on a documents attribute that is an array with its parent document

NESTSELECT u.first_name, u.last_name, user_phonesFROM users AS uUSE KEYS 'user_581'INNER NEST users AS user_phones ON KEYS 'user_' || TOSTRING( u.user_id ) || '_phones'

QUERY

[ { "first_name": "Geovanny", "last_name": "Parker", "user_phones": [ { "_id": "user_581_phones", "doc_type": "user-phones", "phones": [ { "extension": null, "phone_number": "872-201-8963", "phone_type": "Mobile" }, { "extension": "9324", "phone_number": "720.194.5604", "phone_type": "Other" } ], "user_id": 581 } ] }]

RESULT

{ "_id": "user_581_phones", "doc_type": "user-phones", "user_id": 581, "phones": [ { "extension": null, "phone_number": "872-201-8963", "phone_type": "Mobile" }, { "extension": "9324", "phone_number": "720.194.5604", "phone_type": "Other" } ]}

{ "_id": "user_581", "doc_type": "user", "user_id": 581, "first_name": "Geovanny", "last_name": "Parker"}

USER DOCUMENT

USER PHONES DOCUMENT

A NEST operation will take 0 or more documents and nest them as a single entry in the result

INNER NEST will omit records if the right side is missing

LEFT NEST will return records whether or not the right side was found or not

NESTSELECT u.first_name, u.last_name, user_phones[0].phonesFROM users AS uUSE KEYS 'user_581'INNER NEST users AS user_phones ON KEYS 'user_' || TOSTRING( u.user_id ) || '_phones'

QUERY

[ { "first_name": "Geovanny", "last_name": "Parker", "phones": [ { "extension": null, "phone_number": "872-201-8963", "phone_type": "Mobile" }, { "extension": "9324", "phone_number": "720.194.5604", "phone_type": "Other" } ] }]

RESULT

{ "_id": "user_581_phones", "doc_type": "user-phones", "user_id": 581, "phones": [ { "extension": null, "phone_number": "872-201-8963", "phone_type": "Mobile" }, { "extension": "9324", "phone_number": "720.194.5604", "phone_type": "Other" } ]}


USER DOCUMENT


NESTSELECT u.user_id, u.first_name, u.last_name, ARRAY { "phone_number": phone.phone_number, "phone_type": phone.phone_type, "extension": phone.extension } FOR phone IN IFMISSING(phones, []) END AS phonesFROM users AS uUSE KEYS 'user_581'LEFT NEST users AS phones ON KEYS ( ARRAY phone.phone_id FOR phone IN ( SELECT 'phone_' || phone_id AS phone_id FROM users AS phone_lookup USE KEYS 'user_' || TOSTRING(u.user_id) || '_phones' UNNEST phone_lookup.phones AS phone_id ) END)

QUERY

{ "_id": "user_581_phones", "doc_type": "user-phones", "user_id": 581, "phones": [ "886350f7-3f97-5405-b6d0-294ecf469f05", "8ee1bd2e-7b88-5d2c-a211-05a61ac367f8" ]}


USER DOCUMENT


{ "_id": "phone_886350f7-3f97-5405-b6d0-294ecf469f05", "doc_type": "phone", "phone_id": "886350f7-3f97-5405-b6d0-294ecf469f05", "user_id": 581, "phone_type": "Other", "phone_number": "872-201-8963", "extension": null}


generates the same exact results as the previous slide

AGGREGATESSELECT COUNT(*) AS total_usersFROM social AS usersWHERE users.doc_type = 'user'

QUERY

[ { "total_users": 1000 }]

RESULT

SELECT ARRAY_COUNT(users.children) AS children FROM social AS usersUSE KEYS 'user_132'

QUERY

[ { "children": 3 }]

RESULT

SELECT MIN(children.age) AS min_age, MAX(children.age) AS max_age, AVG(children.age) AS avg_ageFROM social AS usersUNNEST users.children AS childrenWHERE users.doc_type = 'user'

QUERY

[ { "avg_age": 8.94, "max_age": 17, "min_age": 1 }]

RESULT

http://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/aggregatefun.html

CF EXAMPLES

POSITIONAL PARAMS

couchbase.n1qlQuery( statement=" SELECT airport_id, airport_name, airport_type, municipality, geo, timezone, airport_iata, airport_icao, FROM `flight-data` WHERE iso_country = $1 AND iso_region = $2 AND doc_type = $3 ORDER BY airport_name ASC LIMIT 2 ", parameters=[ "US", "US-ME", "airport" ]);

SDK QUERY RESULT

NAMED PARAMS

couchbase.n1qlQuery( statement=" SELECT airport_id, airport_name, airport_type, municipality, geo, timezone, airport_iata, airport_icao, FROM `flight-data` WHERE iso_country = $country AND iso_region = $region AND doc_type = $doc_type ORDER BY airport_name ASC LIMIT 2 ", parameters={ 'country': "US", 'region': "US-VT", 'doc_type': "airport" });

SDK QUERY RESULT

PREPARED STATEMENTScouchbase.n1qlQuery(" PREPARE SELECT COUNT(1) AS total_airports FROM `flight-data` WHERE iso_country = 'US' AND iso_region IS NOT NULL AND doc_type = 'airport'");

PREPARE QUERY PREPARE RESULT

couchbase.n1qlQuery(" EXECUTE '9e4e1d3d-f1c3-4292-81cb-824af1fae87e'");

EXECUTE QUERY

EXECUTE RESULT

Prepared Statements are stored in memory until a server restart

NAMED PREPARED STATEMENTScouchbase.n1qlQuery(" PREPARE us_airports FROM SELECT COUNT(1) AS total_airports FROM `flight-data` WHERE iso_country = 'US' AND iso_region IS NOT NULL AND doc_type = 'airport'");

PREPARE QUERY RESULT

couchbase.n1qlQuery(" EXECUTE 'us_airports'");

EXECUTE QUERY RESULT

DOCUMENT LOCKINGuser = couchbase.getAndLock("user_101");

PREPARE QUERY

RESULT

• Default lock time is 5 seconds

• Max lock time is 30 seconds

• Prevents retrieval while Locked • Prevents updates while Locked • An update requires CAS

UNLOCKING A DOCUMENT// replace the document using CAScouchbase.replaceWithCAS( id="user_101", value=doc, cas=user.cas);

REPLACING THE DOCUMENT W/ CAS

RESULT

// update the documentcouchbase.upsert("user_101", doc);

// replace the documentcouchbase.replace("user_101", doc);

WHILE LOCKED UPSERT AND REPLACE WILL FAIL

ERROR

// unlock the document, returns true or// false if the unlock was successfulcouchbase.unlock( id="user_101", cas=user.cas);

USING UNLOCK

Documents will automatically unlock after the "lock time" has been exceeded

REPLICA READStry { // retrieve and lock the document result = couchbase.get("user_100");} catch(any e){ // attempt to get the document from a replica result = couchbase.getFromReplica("user_100");}// replica reads return an array for each // configured replica in the bucket if (isArray(result)) { result = result[0];}

GET DOCUMENT FROM REPLICA(S)

COUNTERS

// increment the user counternext_id = couchbase.counter( id="user_counter", value=1, defaultValue=0);

couchbase.insert("user_" & next_id, { "user_id": next_id, "name": "John Smith", "email": "[email protected]"});

INCREMENT VALUE

// decrement the tickets counteravailable_tix = couchbase.counter( id="tickets_counter", value=-1, defaultValue=1000);// if there are no more tickets // available redirect the userif (available_tix < 1) { cflocation(url="sold-out.cfm", addtoken=false);}...

DECREMENT VALUE

Counter operations are Atomic

CF TRAVEL SAMPLE

bit.ly/cfcouchbase-travel

• AIRLINES • AIRPORTS • HOTELS • LANDMARKS • ROUTES

N1QL EXAMPLES

FLIGHT DATA

bit.ly/flight-data-n1ql

• AIRLINE REVIEWS • AIRLINES • AIRPORT AIRLINES • AIRPORT FREQUENCIES • AIRPORT NAVAIDS • AIRPORT REVIEWS • AIRPORT RUNWAYS • AIRPORTS • CODES

• CONTINENTS • COUNTRIES • FREQUENCIES • NAVAIDS • REGIONS • ROUTES • RUNWAYS • USERS

SOCIAL DATA

bit.ly/social-data-n1ql

• ADDRESSES • AUTH • CHAT MESSAGES • CHATS • EMAILS • FRIENDS • LIKES

• PHONES • POSTS • USER ADDRESSES • USER EMAILS • USER PHONES • USERS

ROADMAP

• FULL TEXT SEARCHING (COMING IN CB 4.5) • SUBDOC SUPPORT • REMOVAL OF DEPRECATED METHODS • SDK ALIGNMENT • REGULAR RELEASE CYCLE • MORE EXAMPLES

DOWNLOAD NOWwww.ortussolutions.com/products/cfcouchbase

bit.ly/cfcouchbase

OR

box install cfcouchbase

OR

CONTRIBUTE

github.com/Ortus-Solutions/cfcouchbase-sdk

bit.ly/cfcouchbasesdk

OR

DRAWING

SELECT drawing.attendees[ FLOOR( RANDOM() * ARRAY_LENGTH(drawing.attendees) ) ] AS winnerFROM `default` AS drawingUSE KEYS 'drawing'

QUESTIONS?

RESOURCES

bit.ly/n1ql-41-language-reference

bit.ly/node-fakeitbit.ly/cfcouchbase-travel bit.ly/cfcouchbase-samples

blog.couchbase.com

developer.couchbase.com query.pub.couchbase.com

thepolyglotdeveloper.com

ortussolutions.com/products/cfcouchbase

ortussolutions.com/blog/category/cfcouchbase

cfcouchbase 2.0 and n1ql

Technology