In the Data Structures for NoSQL Applications post, we used simplified JSON data access through native collections, maps, and more. This post demonstrates querying that data using higher-level N1QL queries, the SQL-based language for JSON.

chart of couchbase data structures and examples

Summary of Couchbase Data Structure types and JSON samples.

Developers can focus on managing basic data structures and data types using programming languages. Meanwhile, DBAs and analysts can access the same data using a query language. Couchbase does this using NoSQL data indexing methods and N1QL querying services.

Search queries with Couchbase full-text search engine are also possible and will be covered in a future post.

What is NoSQL database indexing? 

Indexing examines the data and determines how to find those elements again. Indexing can be applied to JSON documents, keys/fields, or values in fields. Keys in JSON documents act like column names in tabular database indexing systems.

The database manages these collections of elements and values as indexes that point back to the source. The database backend updates indexing as documents change while storing data. Meanwhile, DBAs can optimize indexing for specific use cases such as high-volume writes or queries.

Using indexes, N1QL queries can quickly find field values that match without scanning through the original data. This method also applies to the common data structures, collections, and scopes introduced in Couchbase 7.0.

Querying Couchbase data structures with N1QL

The Couchbase web console is an easy way to view data structure documents in the database. Notice how data structure documents are often much simpler than more complex JSON documents.

data structure documents listed in the bucket preview screen

List of data structure documents in the bucket

To start writing N1QL queries, indexing your data structure data is required. At a minimum, a bucket-wide primary index is needed for basic operations.

For documents using the new collections features from Couchbase 7.0, they must also be indexed.

Primary indexes struggle with large datasets but are excellent for exploring small amounts of data quickly. Use Global Secondary Indexes (GSI) when moving toward production in a big data project.

Listing all data documents and IDs

However, more targeted indexing is not always possible as data structures may not have named keys. For example, a counter is just an ID and an integer value with no field name. But, if you have a map, you can create an index that targets an internal key.

A basic query for document ID can return all documents and show the field names being used.

query results showing data structure documents

CurrentScore is a basic counter, whereas gameList is a list/collection, etc.

Querying scopes/collections for data structures

If using scopes and collections, add them to the from clause.

Retrieve a specific value from a named data structure object by adding it to the where clause.

JSON query results showing a document ID and counter value

When specifying field names (and no ID) the query returns matching values across all documents.

JSON N1QL query showing records containing specific field names

The query returns objects with the specific fields and their values. In the above results, only one of the data structures had the contact field. Two others had a name field, but several blank objects show there were no matching fields.

To be scalable, all query applications should also use global secondary indexes (GSI) for specific fields.

This Database Indexing Best Practices article covers more computer science scenarios used in big data software engineering.

Bringing it all together

As you can see, querying documents and related subcomponents is very simple using Couchbase. Strategic use of sophisticated indexing methods provides even more ways to access the data your applications are creating.

Couchbase dramatically simplifies system architectures, allowing developers to get started without a lot of heavy lifting. These references will help you get started quickly.

Author

Author

Posted by Tyler Mitchell

A guest blogger, advisor, consultant, and writer on database topics with a focus on product marketing ideas and a specialty in geospatial topics. Tyler worked as a Couchbase Product Manager (SDK, Full-Text Search) and as a Product Marketing Manager. See LinkedIn for more details on books he's written and other roles in the database ecosystem.

Leave a reply