SQL++ for JSON in Couchbase Masterclass Hands-On

Explore json fundamentals, including scalar types, objects, arrays, and json path expressions, then learn about null, missing, conditional operations, and json validation with Couchbase sql.

Explore scalar types in sql++ for JSON with Couchbase, including integers, strings, booleans, null, missing, arrays and objects, using select statements with or without from, aliases, and cast operations.

Explore the object data type in JSON, learn how objects and key-value pairs render in SQL++ queries, including nested objects, arrays, and the importance of proper quoting.

Explore arrays in SQL++ for JSON in Couchbase as ordered lists, contrast them with objects, and learn how arrays explode into rows in the from clause; inner arrays remain arrays.

Explore scalar and complex types by nesting objects and arrays to build JSON hierarchies, including null and missing values, and learn type and cast functions in SQL++.

Learn couchbase json path expressions to navigate json hierarchies with dot notation, array indices, ranges, case-insensitive matching, backticks for reserved keys, and wildcards.

Explore how Couchbase handles JSON null and missing values and tests for key existence. Learn about if null, if missing, Nvl, Nvl two, and coalesce with a quick cheat sheet.

Explore conditional operators in sql++ for json in couchbase, focusing on simple case and search case expressions, with when, else, and end to map values and handle nulls in select.

Validate JSON syntax with objects, arrays, and nesting through practical examples. Compare standard JSON with JSON5 relaxed syntax and note Couchbase limitations on comments and trailing commas.

Explore SQL++ scalar functions in Couchbase on JSON data, quick visuals cover numeric, string, date, and bitwise functions for basic scalar data types.

Explore built-in numeric scalar functions in SQL++ for Couchbase, including pi, e, power, sqrt, radians to degrees, rounding and truncation, least and greatest, and random with seed control.

Master string functions like lower, upper, title, trim, concatenation, left pad, and substring; explore encoding and decoding, URL encode/decode, and JSON encode/decode plus multi-byte considerations.

Explore date and time functions in Couchbase, working with dates stored as strings in JSON, formatting dates, extracting year, month, day, and calculating differences or additions across time zones.

Explore SQL++ for JSON in Couchbase bitwise functions and built-in scalar type functions for numbers, strings, and dates, with integers treated as binary for conversion to decimal results.

Explore SQL++ json functions for objects and arrays in Couchbase, including accessors and mutators, array utilities, and unions, intersects, and excepts, plus persisting updates with update set.

Master object accessors in sql++ for Couchbase, retrieving object and array data with dot notation and path functions. Learn to extract length, keys, values, and nested pairs without modification.

Discover object mutators in Couchbase that create new objects, not in-place changes. Compare object concat, add, put, replace, rename, and remove, with insert vs update behavior.

Master array accessors in sql++ for json to manipulate arrays as a single object or as elements, learning length, count, null handling, and search by contains, position, and binary search.

Explore array aggregates in Couchbase SQL++, applying array functions like array_mean, array_max, and array_agg to compute statistics directly on arrays, with distinct option and nonexploding results.

Explore array utilities in sql plus plus for couchbase: use array repeat and range, split and concat, and array sort, reverse, distinct, flatten, and unnest with array star.

Explore array combination functions in SQL++ for JSON in Couchbase, including concat, array union, array intersect, array except, and symmetric difference, performing set-like operations directly on arrays without exploding elements.

Explore array mutator functions in sql++ for json within couchbase, learning how to create new arrays with append, prepend, insert, replace, and remove operations and apply updates with set statements.

Explore filtering array data with SQL++ in Couchbase, using IN and EXISTS, unnesting, and range transformations with inline lambda functions for filter and transform.

Couchbase replaces standard in and not in with array-based tests using within to check membership. See how where clauses, array lengths, and subqueries returning arrays guide filtering.

Explore exists and not exists in SQL++ for JSON in Couchbase, using arrays and the in operator to test non-empty ratings and missing fields.

Explore range predicates in couchbase SQL++ using some, any, all and every to test array elements with inline filters, learn their meanings, and see practical examples with ratings.

Master range transformations in Couchbase SQL++ by applying inline array transformations with optional filters, preserving the array while producing transformed results or objects, using index and first for efficient results.

Learn to persist JSON documents in Couchbase using upsert for updates, create collections with UUID keys and sequences, perform insert, update, and delete, and infer JSON schema.

Master Couchbase collections with UUIDs using SQL++ to insert JSON documents into scoped collections inside a bucket. Explore using the query workbench and metadata to retrieve and analyze records.

Create and query a simple collection from scratch in Couchbase, using SQL++ for JSON with manual keys, nested objects, and basic scope and collection concepts for beginners.

Learn how to replace a top-level document in Couchbase using upsert, which inserts a new key or updates an existing one by replacing its value.

Learn to create a books collection from a store using unnest, and generate string keys with a sequence for each book during insert.

Master upsert to insert new documents or update existing ones in a Couchbase collection. Compare full document replacement with inserts and deletes by key or criteria.

Delete documents in a Couchbase collection using sql++, by id or surrogate key like title, with returning to verify, and restore the collection by dropping and recreating.

Explore json schema inference with infer in SQL++ to auto-detect document structure, including nested arrays and objects, using sample sizes and similarity metrics.

Learn how to update json content in couchbase using update, set, unset, and merge to modify nested objects, arrays, and properties, with hands-on practice.

Learn to update object properties in JSON documents using upsert and pass expressions, including adding new keys, replacing objects and arrays, and using unset to remove properties.

Demonstrate object mutators and the object put function to update a bicycle's properties by returning a new object that replaces keys such as color with yellow.

Learn to update a specific array element with a pass expression by its zero-based index, changing the price of the Lord of the Rings in the books collection.

Update arrays in Couchbase by locating elements in a books or store collection, changing properties, or replacing the whole array with a new one using array mutators and update set.

Update nested arrays in a books collection using an inline cursor to filter by category or title and set new prices for selected books.

Learn to unset properties and remove objects or array elements in Couchbase JSON using SQL++. Distinguish document fragments from top JSON documents and leverage array mutator functions.

Explore using the merge statement to synchronize a target collection with a source of inserts, updates, and deletes in a data pipeline, using when matched and when not matched logic.

Master grouping data with sql++ using group by, having, and aggregates; explore window functions, unnested and nested array handling, including the group as clause in Couchbase.

Group and aggregate json data using group by and functions such as count, average, max, min, and median in sql++ for json in couchbase, with distinct and having clauses.

Learn how window functions work in Couchbase, using lead and lag, partition by, and order by within the over clause, with inline aggregates, reusable window definitions, and frame concepts.

Explore unnesting and flattening arrays in Couchbase to explode array elements and related objects into separate rows, enabling per-name repetition and powerful array_agg and group by aggregations.

Use built-in array functions like array_average and array_sum to aggregate nested arrays without unnesting, and apply inline aggregates and group by alongside window functions for data aggregation.

Explore how group by and the group as clause in Couchbase json compute averages over nested arrays, return ratings, and produce a grouped object with key values.

Explore how the having clause filters grouped data after aggregation in SQL++ for JSON, comparing it to the where clause and using array length and array average on ratings.

Explore couchbase joins from legacy key joins using meta().id to ANSI sql joins, including inner and outer joins, lateral, nest/unnest, and array-based set operations.

Explore key-based joins in Couchbase, using on keys and on key-for with use keys filters, inner and lookup joins, and ansi joins recommended over legacy types.

Master inner joins in sql++ for json by linking person and orders, compare with where clauses, and explore subqueries and common table expressions.

Explore outer joins in sql, including left and right outer joins, why the left join is often preferred, and how on clauses tie matches and left-side data.

Explain that lateral is a prefix for correlated subqueries in the from clause, not a join, and illustrate inner, left, and cross joins with on and where clauses in couchbase.

Learn how nest joins in Couchbase create a left nest that aggregates one-to-many relationships into a nested orders array, compare with left join, and apply totals.

Apply set operations in Couchbase SQL++ to combine JSON objects with union, union all, intersect, and except, including array-based versions for arrays.

Explore reusing functionality in SQL++ with let and letting, covering subqueries, CTEs, recursive CTEs, and prepared queries. Learn to create and call user-defined functions in JavaScript or external libraries.

Use the let clause to declare intermediate variables and reuse expressions like upper(person.name) and encode JSON across select, where, and subqueries to fix alias conflicts.

Learn how to reuse expressions in Couchbase group by queries with let and letting, defining intermediate variables after grouping to apply across select, having, and order by.

Explore uncorrelated and correlated subqueries in Couchbase SQL++, applying them in select, where, and from clauses to compute per-category averages on a books collection.

Transform subqueries into common table expressions to simplify couchbase queries by defining uncorrelated CTEs in a cascade, then address correlated cases with group by and outer filters.

Explore recursive CTEs in sql++ for couchbase, building hierarchical data like employee management trees with top select, union or union all, and recursive joins to generate full paths.

Learn to reuse queries in Couchbase by preparing statements, using named or positional parameters, and executing prepared queries from the cache with flexible parameter passing.

Explore user defined functions in Couchbase, including global and scoped definitions, inline and external variants, and how to create, replace, and drop them.