{"id":9023,"date":"2020-08-04T07:00:04","date_gmt":"2020-08-04T14:00:04","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=9023"},"modified":"2025-06-13T23:42:32","modified_gmt":"2025-06-14T06:42:32","slug":"arrays-in-json-modeling-querying-and-indexing","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/arrays-in-json-modeling-querying-and-indexing\/","title":{"rendered":"Arrays in JSON: Modeling, Querying and Indexing Performance"},"content":{"rendered":"

Array is THE<\/strong> difference between the relational model and the JSON model.\u00a0 \u00a0— Gerald Sangudi<\/a><\/span><\/p><\/blockquote>\n

Abstract<\/h4>\n

JSON array gives you flexibility in the type of elements,\u00a0 number of elements, size of the elements, and the depth of the elements.\u00a0 This adds to the flexibility of operational JSON databases like Couchbase and MongoDB. The performance of queries with an array predicate in operational databases depends on the array indexes. However, the array indexes in these databases come with significant limitations.<\/a> E.g. only one array key is allowed per index. The array indexes, even when created, can only process AND predicates efficiently.\u00a0 The upcoming Couchbase 6.6 release removes these JSON limitations by using a built-in inverted index to be used to index and query arrays in N1QL.\u00a0 This article explains the background and the workings of this novel implementation.<\/p>\n

Introduction<\/h4>\n

An array is a basic type built into JSON<\/a>\u00a0defined as\u00a0An\u00a0array<\/i>\u00a0is an ordered collection of values. An array begins with\u00a0[<\/tt>left bracket<\/small>\u00a0and ends with\u00a0]<\/tt>right bracket<\/small>. Values are separated by\u00a0,<\/tt>comma<\/small>.\u00a0<\/em>An array gives you flexibility because it can contain an arbitrary number of scalar, vector, and object values. A user profile can have an array of hobbies, a customer profile an array of cars, a member profile an array of friends. Couchbase N1QL provides a rich set of operators<\/a> to manipulate arrays; MongoDB has a list of operators to handle arrays<\/a> as well.<\/p>\n

Before you start querying, you need to model your data in arrays. All JSON document databases like Couchbase, MongoDB recommend you to denormalize your data model to improve your performance and appdev. What that means is, transform your 1:N relationship into a single document by embedded the N into 1.\u00a0 In JSON, you\u2019d do that by using an array.\u00a0 The example below, the document(1) contains 8 (N) likes. Instead of storing a foreign key reference to another table, in JSON, we store data inline.\u00a0<\/span><\/p>\n

            \"public_likes\": [\r\n                \"Julius Tromp I\",\r\n                \"Corrine Hilll\",\r\n                \"Jaeden McKenzie\",\r\n                \"Vallie Ryan\",\r\n                \"Brian Kilback\",\r\n                \"Lilian McLaughlin\",\r\n                \"Ms. Moses Feeney\",\r\n                \"Elnora Trantow\"\r\n            ],<\/pre>\n
Values here are arrays of strings. In JSON, each element can be any valid JSON type: scalars (numeric, string, etc), or objects or vectors (arrays).\u00a0 Each hotel document contains an array of reviews.\u00a0 This is the process of denormalization.\u00a0 Converting multiple 1:N relationships to a single hotel object that contains N public_likes and M reviews.\u00a0 With this, the hotel object embeds two arrays: public_likes and reviews.\u00a0 There can be any number of values of any type under these arrays.\u00a0 This is the key contributing factor to JSON Schema flexibility.\u00a0 When you need to add new likes or reviews, you simply add a new value or an object to this.\u00a0<\/span><\/p>\n
  \"reviews\": [\r\n                {\r\n                    \"author\": \"Ozella Sipes\",\r\n                    \"content\": \"This was our 2nd trip here...\",\r\n                    \"date\": \"2013-06-22 18:33:50 +0300\",\r\n                    \"ratings\": {\r\n                        \"Cleanliness\": 5,\r\n                        \"Location\": 4,\r\n                        \"Overall\": 4,\r\n                        \"Rooms\": 3,\r\n                        \"Service\": 5,\r\n                        \"Value\": 4\r\n                    }\r\n                },\r\n                {\r\n                    \"author\": \"Barton Marks\",\r\n                    \"content\": \"We found the hotel ...\",\r\n                    \"date\": \"2015-03-02 19:56:13 +0300\",\r\n                    \"ratings\": {\r\n                        \"Business service (e.g., internet access)\": 4,\r\n                        \"Check in \/ front desk\": 4,\r\n                        \"Cleanliness\": 4,\r\n                        \"Location\": 4,\r\n                        \"Overall\": 4,\r\n                        \"Rooms\": 3,\r\n                        \"Service\": 3,\r\n                        \"Value\": 5\r\n                    }\r\n                }\r\n            ],\r\n<\/pre>\n
Like the hotel object above, you denormalize your data model into JSON, there could be many arrays for each object. Profiles have arrays for hobbies, cars, credit cards, preferences, etc.\u00a0 each of these can be scalars (simple numeric\/string\/boolean values) or vectors (arrays of other scalars, arrays of objects, etc).\u00a0<\/span><\/p>\n
 <\/p>\n
Once you model and store the data, you need to process it — select, join, project. Couchbase <\/span>N1QL<\/span><\/a> (SQL for JSON) provides an expressive language to do these and more. \u00a0 Here are common use cases.<\/span><\/p>\n
1. Find all the documents with a simple value can be done by either of the following queries.\r\n\r\nSELECT *\r\nFROM `travel-sample`\r\nWHERE type = \"hotel\"\r\n    AND ANY p IN public_likes SATISFIES p = \"Vallie Ryan\" END\r\n\r\n\r\nSELECT t\r\nFROM `travel-sample` t\r\n      UNNEST t.public_likes AS p\r\nWHERE t.type = \"hotel\"\r\n    AND p = \"Vallie Ryan\"\r\n<\/pre>\n
2. Find all the documents that match a range.  In this case, we try to find all the documents that have atleast one rating has \u201cOverall\u201d > 4\r\n\r\nSELECT COUNT(1)\r\nFROM `travel-sample`\r\nWHERE type = \"hotel\"\r\n    AND ANY r IN reviews SATISFIES r.ratings.Overall > 4 END\r\n\r\nSELECT COUNT(1) \r\nFROM `travel-sample` t\r\nUNNEST reviews AS r\r\nWHERE t.type = \"hotel\"\r\n    AND r.ratings.Overall > 4\r\nGROUP BY t.type\r\n\r\n<\/pre>\n
3. Find all the documents where every rating for  \u201cOverall\u201d > 4\r\n\r\nSELECT * FROM `travel-sample` WHERE type = \u2018hotel\u2019 AND ANY AND EVERY r in reviews SATISFIES r.Overall > 4 END\r\n\r\nSELECT COUNT(1)\r\nFROM `travel-sample`\r\nWHERE type = \"hotel\"\r\n    AND ANY\r\n    AND EVERY r IN reviews SATISFIES r.ratings.Overall > 4 END\r\n\r\nSELECT reviews[*].ratings[*].Overall\r\nFROM `travel-sample`\r\nWHERE type = \"hotel\"\r\n    AND ANY\r\n    AND EVERY r IN reviews SATISFIES r.ratings.Overall > 4 END\r\nlimit 10;\r\n\r\n\t[\r\n    {\r\n        \"Overall\": [\r\n            5\r\n        ],\r\n        \"name\": \"The Bulls Head\"\r\n    },\r\n    {\r\n        \"Overall\": [\r\n            5,\r\n            5,\r\n            5,\r\n            5,\r\n            5\r\n        ],\r\n        \"name\": \"La Pradella\"\r\n    },\r\n    {\r\n        \"Overall\": [\r\n            5,\r\n            5,\r\n            5\r\n        ],\r\n        \"name\": \"Culloden House Hotel\"\r\n    },\r\n    {\r\n        \"Overall\": [\r\n            5\r\n        ],\r\n        \"name\": \"Auberge-Camping Bagatelle\"\r\n    },\r\n    {\r\n        \"Overall\": [\r\n            5,\r\n            5\r\n        ],\r\n        \"name\": \"Avignon Hotel Monclar\"\r\n    }\r\n    ]\r\n<\/pre>\n
Array Indexing:<\/h4>\n
Indexing arrays are a challenge for B-tree based indexes.\u00a0 However, the JSON database has to do it to meet the performance requirements: <\/span>MongoDB does it<\/span><\/a>; <\/span>Couchbase does it<\/span><\/a>. However, both come with limitations.\u00a0 You can only have one array key within an index.\u00a0 This is<\/span> true of MongoDB<\/span><\/a>; this is <\/span>true of Couchbase N1QL<\/span><\/a>.\u00a0 The core reason for this limitation is when you index elements of an array, you need separate index entries.\u00a0\u00a0<\/span><\/p>\n
Consider the array:\r\n\r\nDocument key: \u201cbob\u201d\r\n{\r\n   \u201cId\u201d: \u201cbob123\u201d\r\n   \u201cA\u201d: [1, 2, 3, 4]\r\n   \u201cB\u201d: [521, 4892, 284]\r\n}\r\n\r\nIndexing of the field \u201cid\u201d  simply requires 1 entry in the index:   \r\n         \u201cbob123\u201d:bob\r\n\r\nIndexing of the field \u201ca\u201d requires 4 entries in the index: \r\n        \u201c1\u201d:\u201dbob\u201d, 2:\u201dbob\u201d, 3:\u201dbob\u201d, 4:\u201dbob\u201d\r\n\r\nIndexing of the composite index (id, a) requires 4 entries:\r\n   \u201cbob123\u201d, 1: bob\r\n   \u201cbob123\u201d, 2: bob\r\n   \u201cbob123\u201d, 3: bob\r\n   \u201cbob123\u201d, 4: bob\r\n\r\nIndexing of the composite index (id, a, b) requires the following 12 entries:\r\n   \u201cbob123\u201d, 1, 521: bob\r\n   \u201cbob123\u201d, 1,4982: bob\r\n   \u201cbob123\u201d, 1, 284: bob\r\n   \u201cbob123\u201d, 2, 521: bob\r\n   \u201cbob123\u201d, 2,4982: bob\r\n   \u201cbob123\u201d, 2, 284: bob\r\n   \u201cbob123\u201d, 3, 521: bob\r\n   \u201cbob123\u201d, 3,4982: bob\r\n   \u201cbob123\u201d, 3, 284: bob\r\n   \u201cbob123\u201d, 4, 521: bob\r\n   \u201cbob123\u201d, 4,4982: bob\r\n   \u201cbob123\u201d, 4, 284: bob\r\n<\/pre>\n
The size of the index grows exponentially as the number of array keys in the index and the number of array elements in the index.\u00a0 \u00a0 Hence the limitation. \u00a0 Implications of this limitation are:\u00a0<\/span><\/p>\n