{"id":1828,"date":"2021-01-27T08:16:01","date_gmt":"2021-01-27T16:16:01","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/query-couchbase-data-structures-with-n1ql-sql-for-json\/"},"modified":"2021-01-27T08:16:01","modified_gmt":"2021-01-27T16:16:01","slug":"query-couchbase-data-structures-with-n1ql-sql-for-json","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/query-couchbase-data-structures-with-n1ql-sql-for-json\/","title":{"rendered":"Data Structures & Queries with Couchbase N1QL (SQL for JSON)"},"content":{"rendered":"\n

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

\"chart<\/a>
Summary of Couchbase Data Structure types and JSON samples.<\/figcaption><\/figure>\n\n\n\n

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.<\/span><\/p>\n\n\n\n

Search queries with Couchbase full-text search engine are also possible and will be covered in a future post.<\/span><\/p>\n\n\n\n

What is NoSQL database indexing?\u00a0<\/span><\/h2>\n\n\n\n

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.<\/span><\/p>\n\n\n\n

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.<\/span><\/p>\n\n\n\n

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, <\/span>collections, and scopes introduced in <\/span>Couchbase 7.0<\/b><\/a>.<\/span><\/p>\n\n\n\n

Querying Couchbase data structures with N1QL<\/span><\/h2>\n\n\n\n

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.<\/span><\/p>\n\n\n\n

\"data
List of data structure documents in the bucket<\/figcaption><\/figure>\n\n\n\n

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.<\/span><\/p>\n\n\n

[crayon lang=”mysql” decode=”true”]CREATE PRIMARY INDEX ON `travel-sample`;[\/crayon]<\/p>\n\n\n\n

For documents using the new collections features from <\/span>Couchbase 7.0<\/b>, they must also be indexed.<\/span><\/p>\n\n\n

[crayon lang=”mysql” decode=”true”]CREATE PRIMARY INDEX ON `travel-sample`.`scope1`.`col1`[\/crayon]<\/p>\n\n\n\n

P<\/strong>rimary indexes <\/b>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.<\/span><\/p>\n\n\n\n

Listing all data documents and IDs<\/h2>\n\n\n\n

However, more targeted indexing is not always possible as data structures may not have named keys. <\/span>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.<\/span><\/p>\n\n\n\n

A basic query for document ID can return all documents and show the field names being used.<\/span><\/p>\n\n\n

[crayon lang=”mysql” decode=”true”]SELECT META().id, * FROM `travel-sample`[\/crayon]<\/p>\n\n\n\n

\"query<\/a><\/p>\n\n\n\n

CurrentScore <\/span><\/i>is a basic counter, whereas <\/span>gameList <\/span><\/i>is a list\/collection, etc.<\/span><\/p>\n\n\n\n

Querying scopes\/collections for data structures<\/h3>\n\n\n\n

If using scopes and collections, add them to the from clause.<\/span><\/p>\n\n\n

[crayon lang=”mysql” decode=”true”]SELECT * FROM\u00a0`travel-sample`.`scope1`.`col1`[\/crayon]<\/p>\n\n\n\n

Retrieve a specific value from a named data structure object by adding it to the where clause.<\/span><\/p>\n\n\n

[crayon lang=”mysql” decode=”true”]SELECT META().id, * FROM `travel-sample` WHERE META().id = ‘currentScore'[\/crayon]<\/p>\n\n\n\n

\"JSON<\/a><\/p>\n\n\n\n

When specifying field names (and no ID) the query returns matching values across all documents.<\/span><\/p>\n\n\n

[crayon lang=”mysql” decode=”true”]SELECT name, contact FROM `travel-sample`[\/crayon]<\/p>\n\n\n\n

\"JSON<\/a><\/p>\n\n\n\n

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

To be scalable, all query applications should also use global secondary indexes (GSI) for specific fields.<\/span><\/p>\n\n\n\n

This <\/span>Database Indexing Best Practices<\/span><\/a> article covers more computer science scenarios used in big data software engineering.<\/span><\/p>\n\n\n\n

Bringing it all together<\/span><\/h2>\n\n\n\n

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.<\/span><\/p>\n\n\n\n

Couchbase dramatically simplifies system architectures, allowing developers to get started without a lot of heavy lifting. These references will help you get started quickly.<\/span><\/p>\n\n\n\n