Query Mapping
The adapter maps SQL-92-compliant queries into corresponding N1QL or SQL++ queries. Although the mapping below is not complete, it should help you get a sense for the common patterns the adapter uses during this transformation.
SELECT Queries
The SELECT statements are translated to the appropriate N1QL SELECT query as shown below. Due to the similarities between SQL-92 and N1QL, many queries will simply be direct translations.
One major difference is that when the schema for a given Couchbase bucket exists in the adapter, a SELECT * query will be translated to directly select the individual fields in the bucket. The adapter will also automatically create a Document.Id column based on the primary key of each document in the bucket.
| SQL Query | N1QL Query |
| SELECT * FROM users | SELECT META(`users`).id AS `id`, ... FROM `users` |
| SELECT [Document.Id], status FROM users | SELECT META(`users`).id AS `Document.Id`, `users`.`status` FROM `users` |
| SELECT * FROM users WHERE status = 'A' OR age = 50 | SELECT META(`users`).id AS `id`, ... FROM `users` WHERE TOSTRING(`users`.`status`) = "A" OR TONUMBER(`users`.`age`) = 50 |
| SELECT * FROM users WHERE name LIKE 'A%' | SELECT META(`users`).id AS `id`, ... FROM `users` WHERE TOSTRING(`users`.`name`) LIKE "A%" |
| SELECT * FROM users WHERE status = 'A' ORDER BY [Document.Id] DESC | SELECT META(`users`).id AS `id`, ... FROM `users` WHERE TOSTRING(`users`.`status`) = "A" ORDER BY META(`users`).id DESC |
| SELECT * FROM users WHERE status IN ('A', 'B') | SELECT META(`users`).id, ... FROM `users` WHERE TOSTRING(`users`.`status`) IN ["A", "B"] |
Note that conditions can include extra type functions if the adapter detects that a type conversion may be necessary. You can disable these type conversions using the StrictComparison property. For clarity, the rest of the N1QL samples are shown without these extra conversion functions.
USE KEYS Optimizations
When a query has either equals or IN clause that targets the Document.Id column, and there is no OR clause to override it, the adapter will convert the Document.Id filter into a USE KEYS clause. This avoids the overhead of scanning an index because the document keys are already known to the N1QL engine (this optimization does not apply to the Analytics CouchbaseService).
| SQL Query | N1QL Query |
| SELECT * FROM users WHERE [Document.Id] = '1' | SELECT ... FROM `users` USE KEYS ["1"] |
| SELECT * FROM users WHERE [Document.Id] IN ('2', '3') | SELECT ... FROM `users` USE KEYS ["2", "3"] |
| SELECT * FROM users WHERE [Document.Id] = '4' OR [Document.Id] = '5' | SELECT ... FROM `users` USE KEYS ["4", "5"] |
| SELECT * FROM users WHERE [Document.Id] = '6' AND status = 'A' | SELECT ... FROM `users` USE KEYS ["6"] WHERE `status` = "A" |
In addition to being used for SELECT queries, the same optimization is performed for DML operations as shown below.
Child Tables
As long as all the child tables in a query share the same parent, and they are combined using INNER JOINs on their Document.Id columns, the adapter will combine the JOINs into a single UNNEST expression. Unlike N1QL UNNEST queries, you must explicitly JOIN with the base table if you want to access its fields.
| SQL Query | N1QL Query |
| SELECT * FROM users_posts | SELECT META(`users`).id, `users_posts`.`text`, ... FROM `users` UNNEST `users`.`posts` AS `users_posts` |
| SELECT * FROM users INNER JOIN users_posts ON users.[Document.Id] = users_posts.[Document.Id] | SELECT META(`users`).id, `users`.`name`, ..., `users_posts`.`text`, ... FROM `users` UNNEST `users`.`posts` AS `users_posts` |
| SELECT * FROM users INNER JOIN users_posts ... INNER JOIN users_comments ON ... | SELECT ... FROM `users` UNNEST `users`.`posts` AS `users_posts` UNNEST `users`.`comments` AS `users_comments` |
Flavor Tables
Flavored tables always have the appropriate condition included when you query, so that only documents from the flavor will be returned:
| SQL Query | N1QL Query |
| SELECT * FROM [users.subscriber] | SELECT ... FROM `users` WHERE `docType` = "subscriber" |
| SELECT * FROM [users.subscriber] WHERE age > 50 | SELECT ... FROM `users` WHERE `docType` = "subscriber" AND `age` > 50 |
Aggregate Queries
N1QL has several built-in aggregate functions. The adapter makes extensive use of this for various aggregate queries. See some examples below:
| SQL Query | N1QL Query |
| SELECT Count(*) As Count FROM Orders | SELECT Count(*) AS `count` FROM `Orders` |
| SELECT Sum(price) As total FROM Orders | SELECT Sum(`price`) As `total` FROM `Orders` |
| SELECT cust_id, Sum(price) As total FROM Orders GROUP BY cust_id ORDER BY total | SELECT `cust_id`, Sum(`price`) As `total` FROM `Orders` GROUP BY `cust_id` ORDER BY `total` |
| SELECT cust_id, ord_date, Sum(price) As total FROM Orders GROUP BY cust_id, ord_date Having total > 250 | SELECT `cust_id`, `ord_date`, Sum(`price`) As `total` FROM `Orders` GROUP BY `cust_id`, `ord_date` Having `total` > 250 |
Insert Statements
The SQL INSERT statement is mapped to the N1QL INSERT statement as shown below. This works the same for both top-level fields as well as fields produced by Vertical Flattening:
| SQL Query | N1QL Query |
| INSERT INTO users([Document.Id], age, status) VALUES ('bcd001', 45, 'A') | INSERT INTO `users`(KEY, VALUE) VALUES ('bcd001', { "age" : 45, "status" : "A" }) |
| INSERT INTO users([Document.Id], [metrics.posts]) VALUES ('bcd002', 0) | INSERT INTO `users`(KEY, VALUE) VALUES ('bcd002', {"metrics': {"posts": 0}}) |
Child Table Inserts
Inserts on child tables are converted internally into N1QL UPDATEs using array operations. Since that this does not create the top-level document, the Document.Id provided must refer to a document that already exists.
Another limitation of child table inserts is that multi-valued inserts must all use the same Document.Id. The provider will verify this before modifying any data and raise an error if this constraint is violated.
| SQL Query | N1QL Query |
| INSERT INTO users_ratings([Document.Id], value) VALUES ('bcd001', 4.8), ('bcd001', 3.2) | UPDATE `users` USE KEYS "bcd001" SET `ratings` = ARRAY_PUT(`ratings`, 4.8, 3.2) |
| INSERT INTO users_reviews([Document.Id], score) VALUES ('bcd002', 'Great'), ('bcd002', 'Lacking') | UPDATE `users` USE KEYS "bcd001" SET `ratings` = ARRAY_PUT(`ratings`, {"score": "Great"}, {"score": "Lacking"}) |
Bulk Insert Statements
Bulk inserts are also supported the SQL Bulk Insert is converted as shown below:
INSERT INTO users#Temp([Document.Id], KEY, VALUE) VALUES('bcd001', 45, "A")
INSERT INTO users#Temp([Document.Id], KEY, VALUE) VALUES('bcd002', 24, "B")
INSERT INTO users SELECT * FROM users#Temp
is converted to:
INSERT INTO `users` (KEY, VALUE) VALUES
('bcd001', {"age": 45, "status": "A"}),
('bcd002', {"age": 24, "status": "B"})
Like multi-valued inserts on child tables, all the rows in a bulk insert must also have the same Document.Id.
Update Statements
The SQL UPDATE statement is mapped to the N1SQL UPDATE statement as shown below:
| SQL Query | N1QL Query |
| UPDATE users SET status = 'C' WHERE [Document.Id] = 'bcd001' | UPDATE `users` USE KEYS ["bcd001"] SET `status` = "C" |
| UPDATE users SET status = 'C' WHERE age > 45 | UPDATE `users` SET `status` = "C" WHERE `age` > 45 |
Child Table Updates
When updating a child table, the SQL query is converted to an UPDATE query using either a "FOR" expression or an "ARRAY" expression:
| SQL Query | N1QL Query |
| UPDATE users_ratings SET value = 5.0 WHERE value > 5.0 | UPDATE `users` SET `ratings` = ARRAY CASE WHEN `value` > 5.0 THEN 5 ELSE `value` END FOR `value` IN `ratings` END |
| UPDATE users_reviews SET score = 'Unknown' WHERE score = '' | UPDATE `users` SET `$child`.`score` = 'Unknown' FOR `$child` IN `reviews` WHEN `$child`.`score` = "" END |
Flavor Table Updates
Like flavor table SELECTs, UPDATEs on flavor tables always include the appropriate condition, so only docments belonging to the flavor are affected:| SQL Query | N1QL Query |
| UPDATE [users.subscriber] SET status = 'C' WHERE age > 45 | UPDATE `users` SET `status` = "C" WHERE `docType` = "subscriber" AND `age` > 45 |
Delete Statements
The SQL DELETE statement is mapped to the N1QL DELETE statement as shown below:
| SQL Query | N1QL Query |
| DELETE FROM users WHERE [Document.Id] = 'bcd001' | DELETE FROM `users` USE KEYS ["bcd001"] |
| DELETE FROM users WHERE status = 'inactive' | DELETE FROM `users` WHERE `status` = "inactive" |
Child Table Deletes
When deleting from a child table, the SQL query is converted to an UPDATE query using an "ARRAY" expression:
| SQL Query | N1QL Query |
| DELETE FROM users_ratings WHERE value < 0 | UPDATE `users` SET `ratings` = ARRAY `value` FOR `value` IN `ratings` WHEN NOT (`value` < 0) END |
| DELETE FROM users_reviews WHERE score = '' | UPDATE `users` SET `reviews` = ARRAY `$child` FOR `$child` IN `reviews` WHEN NOT (`$child`.`score` = "") END |
Flavor Tables Deletes
Like flavor table SELECTs, DELETEs on flavor tables always include the appropriate condition, so only docments belonging to the flavor are affected:| SQL Query | N1QL Query |
| DELETE FROM [users.subscriber] WHERE status = 'inactive' | DELETE FROM `users` WHERE `docType` = "subscriber" AND status = "inactive" |