Putting the SQL back in NoSQL

demo-notebook.n1qlnb

{"cells":[{"kind":1,"language":"markdown","value":"## Putting the SQL back into NoSQL\r\n\r\nThis notebook contains interesting and (hopefully) useful samples of SQL++ queries for Couchbase.\r\n\r\nThe goal of this demo is to demonstrate the power and flexibility of using SQL for JSON data."},{"kind":1,"language":"markdown","value":"### 1. Using `LIKE` to filter by text\r\n\r\nThe `LIKE` keyword supports `%` as wildcards.\r\n\r\nFor anything more complex, try the [Full Text Search](https://www.couchbase.com/products/full-text-search/) service instead."},{"kind":2,"language":"SQL++","value":"SELECT l.*\r\nFROM `travel-sample`.inventory.landmark l\r\nWHERE l.content LIKE '%breakfast%'\r\nLIMIT 10;"},{"kind":1,"language":"markdown","value":"### 2. `ANY ... IN ... SATISFIES` for arrays\r\n\r\nJSON data can contain arrays. To match data in an array, you can use the `ANY...IN...SATISFIES` syntax.\r\n\r\nThis query will match any route that has a flight AF547 in the schedule."},{"kind":2,"language":"SQL++","value":"SELECT r.*\r\nFROM `travel-sample`.inventory.route r\r\nWHERE ANY s IN r.schedule SATISFIES s.flight = \"AF547\" END\r\nLIMIT 10;"},{"kind":1,"language":"markdown","value":"### 3. Advice for Indexes\r\n\r\nThe `ADVICE` keyword can recommend better indexes for performance."},{"kind":2,"language":"SQL++","value":"ADVISE\r\nSELECT r.*\r\nFROM `travel-sample`.inventory.route r\r\nWHERE ANY s IN r.schedule SATISFIES s.flight = \"AF547\" END\r\nLIMIT 10;"},{"kind":1,"language":"markdown","value":"### 4. Query plan\r\n\r\nThe query plan can be shown using `EXPLAIN`."},{"kind":2,"language":"SQL++","value":"EXPLAIN\r\nSELECT r.*\r\nFROM `travel-sample`.inventory.route r\r\nWHERE ANY s IN r.schedule SATISFIES s.flight = \"AF547\" END\r\nLIMIT 10;"},{"kind":1,"language":"markdown","value":"The visualization of the plan can also be helpful, and is available in the Query workbench.\r\n\r\n<video width=\"1024\" height=\"\" controls>\r\n  <source src=\".\\demo-notebook\\query-plan.mp4\" type=\"video/mp4\">\r\n</video>"},{"kind":1,"language":"markdown","value":"### 5. Pagination with `OFFSET...LIMIT`\r\n\r\nPagination is useful for showing \"pages\" of data: lists of content, customers, invoices, any situation where you don't want to show ALL the data at once.\r\n\r\nSQL syntax for paging is very concise and readable."},{"kind":2,"language":"SQL++","value":"SELECT h.*\r\nFROM `travel-sample`.inventory.hotel h\r\nORDER BY h.name\r\nOFFSET 10\r\nLIMIT 10"},{"kind":1,"language":"markdown","value":"### 6. Subqueries\r\n\r\nSometimes subqueries are necessary: a query within the predicate of another query. "},{"kind":2,"language":"SQL++","value":"SELECT l.city\r\nFROM `travel-sample`.inventory.landmark l\r\nWHERE l.city IN (\r\n\tSELECT RAW city FROM `travel-sample`.inventory.airport\r\n);\r\n"},{"kind":1,"language":"markdown","value":"This query will find all the landmarks that are in the cities with airports.\r\n\r\nNotice \"RAW\". This will unwrap a single value from a JSON object, so that it can be used to compare to a value.\r\n\r\nAlso notice that there are duplicate results (cities that have more than one landmark).\r\n\r\n`DISTINCT` can be used to de-dupe."},{"kind":2,"language":"SQL++","value":"SELECT DISTINCT l.city\r\nFROM `travel-sample`.inventory.landmark l\r\nWHERE l.city IN (\r\n\tSELECT RAW city FROM `travel-sample`.inventory.airport\r\n);"},{"kind":1,"language":"markdown","value":"Run `ADVISE` on this query to see if there are any index suggestions."},{"kind":2,"language":"SQL++","value":"ADVISE\r\nSELECT DISTINCT l.city\r\nFROM `travel-sample`.inventory.landmark l\r\nWHERE l.city IN (\r\n\tSELECT RAW city FROM `travel-sample`.inventory.airport\r\n);"},{"kind":1,"language":"markdown","value":"Notice: `\"THIS IS AN OPTIMAL COVERING INDEX.\"`\r\n\r\nAnd `\"recommended_indexes\": \"No index recommendation at this time.\"`\r\n\r\nA \"covering index\" means that all the fields the query needs are stored in the index. There is no need to engage the underlying data service.\r\n\r\nCovering indexes provide excellent performance when working with a small number of fields.\r\n\r\nExample:\r\n\r\n`SELECT foo, bar . . . ` could possibly use a covering index.\r\n\r\n `SELECT *` would likely not be able to use a covering index, and must engage the data service every time."},{"kind":1,"language":"markdown","value":"### 7. Complex doesn't mean complicated\r\n\r\nSQL is powerful AND readable AND is a skill that many people already have.\r\n\r\nCouchbase's SQL++ implementation is not \"looks like SQL\" or \"SQL like\", it's [full SQL](https://docs.couchbase.com/cloud/n1ql/n1ql-language-reference/index.html).\r\n\r\nThat includes:\r\n* Aggregation (`GROUP BY`)\r\n* Common Table Expressions aka CTEs (`WITH`)\r\n* Window functions (`OVER`, `PARTITION BY`, `CUME_DIST()`, etc)\r\n* CRUD (`INSERT`, `UPDATE`, `DELETE`, `MERGE`, `SELECT`)\r\n* `JOIN`\r\n\r\nHere's an example of using a CTE to get a count of the total number of airports by country."},{"kind":2,"language":"SQL++","value":"WITH country_airport_count AS (\r\n  SELECT airport.country, COUNT(*) AS num_airports\r\n  FROM `travel-sample`.inventory.airport AS airport\r\n  GROUP BY airport.country\r\n)\r\nSELECT cte.country, cte.num_airports\r\nFROM country_airport_count cte\r\nORDER BY cte.num_airports DESC\r\nLIMIT 5;\r\n"},{"kind":1,"language":"markdown","value":"### 8. Next steps\r\n\r\n* [Couchbase Playground](https://couchbase.live/): Check out more example of SQL++ (previously known as \"N1QL\"). Totally free, browser based, no install required.\r\n* [Couchbase Capella free trial](https://www.couchbase.com/products/capella/): No credit card needed."}]}

This is a demo SQL++ Notebook used with the Couchbase Extension for VSCode.