Travel Sample Bucket<\/span><\/h4>\nIn this article, we\u2019ll be referencing the <\/span>Travel Sample dataset available to install<\/span><\/a> in any Couchbase Server instance. The travel-sample bucket has several distinct document types: airline, route, airport, landmark, and hotel.\u00a0 The document model for each kind of document contains:<\/span><\/p>\n\n- A key that acts as a primary key<\/span><\/li>\n
- An id field that identifies the document<\/span><\/li>\n
- A type field that identifies the kind of document<\/span><\/li>\n<\/ul>\n
<\/p>\n
The examples in this article will be using the hotel documents. The sample document below gives you an idea of the structure of a hotel document:\u00a0<\/span><\/p>\n![\"\"](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/03\/BKane-FTS-Array-Figure-1-sample-doc.png\")
<\/p>\n
Figure 1 – Sample Hotel Document<\/span><\/i><\/p>\nThe Problem<\/span><\/h2>\nOur example is a use case where a user can search for hotels which have been reviewed or liked by a person with a particular name. This requires querying hotel documents on both public likes and reviews, which are arrays within the hotel document model:\u00a0<\/span><\/p>\n![\"\"](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/03\/BKane-FTS-Array-Figure-2-arrays.png\")
<\/p>\n
Figure 2 – The \u201cpublic_likes\u201d and \u201creviews\u201d arrays in the sample hotel document<\/span><\/i><\/p>\n <\/p>\n
First let\u2019s look at implementing this use case with N1QL and GSI (Global Secondary Index). To find hotels that anyone named Ozella has either liked or reviewed, the query could look like this:\u00a0<\/span><\/p>\nSELECT name, address, city, country,\r\n phone, public_likes, reviews\r\nFROM `travel-sample`\r\nWHERE type=\"hotel\"\r\n AND (ANY l IN public_likes SATISFIES l LIKE \"%Ozella%\" END\r\n OR ANY r IN reviews SATISFIES r.author LIKE \"%Ozella%\" END);<\/pre>\n <\/p>\n
We need to create an appropriate index for this query.\u00a0 Maybe something like this that indexes both arrays of interest for hotel documents:\u00a0<\/span><\/p>\nCREATE INDEX idx_hotel_public_likes_review_author ON `travel-sample`\r\n(DISTINCT ARRAY `l` FOR l IN `public_likes` END, \r\nDISTINCT ARRAY `r`.`author` FOR r IN `reviews` END)\r\nWHERE `type` = 'hotel';<\/pre>\n <\/p>\n
This doesn\u2019t work, and we get the error shown in Figure 3:<\/span><\/p>\n![\"\"](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/03\/BKane-FTS-Array-Figure-3-error-creating-index.png\")
<\/span>Figure 3 – Error creating index with multiple arrays<\/span><\/i><\/p>\n <\/p>\n
As Keshav Murthy wrote in his blog post <\/span>Search and Rescue: 7 Reasons for N1QL (SQL) developers to use Search<\/span><\/a> (problem #6), with N1QL in Couchbase, \u201c<\/span>to get the best performance while searching inside arrays, you need to <\/span><\/i>create indexes with array keys<\/span><\/i><\/a>. The array index comes with a limitation: each array index can only have one array key per index. So, when you have a customer object with multiple array fields, you can\u2019t search all of them using a single index…causing expensive queries.<\/span><\/i>\u201d As Keshav notes in that article, this is a limitation with b-tree indexes in databases generally.\u00a0<\/span><\/p>\n <\/p>\n
So now let\u2019s try two separate array indexes.\u00a0 The indexes to support this query could look like these, which were created using the <\/span>Couchbase N1QL Index Advisor<\/span><\/a>, a new (DP) feature in Couchbase 6.5:<\/span><\/p>\n\u00a0<\/span><\/p>\nCREATE INDEX adv_DISTINCT_public_likes_type \r\nON `travel-sample`(DISTINCT ARRAY `l` FOR l in `public_likes` END) \r\nWHERE `type` = 'hotel';\r\n\r\n\r\nCREATE INDEX adv_DISTINCT_reviews_author_type \r\nON `travel-sample`(DISTINCT ARRAY `r`.`author` FOR r in `reviews` END) \r\nWHERE `type` = 'hotel';<\/pre>\n <\/p>\n
With those 2 indexes in place, our query runs successfully with 5 results (hotel_26020, hotel_10025, hotel_5081, hotel_20425, hotel_25327) and the following execution plan:\u00a0<\/span><\/p>\n![\"\"](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/03\/BKane-FTS-Array-Figure-4-IndexScan-plan.png\")
<\/p>\n
Figure 4 – Execution plan using multiple indexes (GSI)<\/span><\/i><\/p>\nSame plan in JSON:<\/span><\/p>\n{\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"1.321\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"Authorize\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 3,\r\n \"execTime\": \"3.034\u00b5s\",\r\n \"servTime\": \"1.037859ms\"\r\n },\r\n \"privileges\": {\r\n \"List\": [\r\n {\r\n \"Target\": \"default:travel-sample\",\r\n \"Priv\": 7\r\n }\r\n ]\r\n },\r\n \"~child\": {\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"2.235\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"UnionScan\",\r\n \"#stats\": {\r\n \"#itemsIn\": 1646,\r\n \"#itemsOut\": 904,\r\n \"#phaseSwitches\": 5107,\r\n \"execTime\": \"1.32474ms\",\r\n \"kernTime\": \"113.495553ms\"\r\n },\r\n \"scans\": [\r\n {\r\n \"#operator\": \"DistinctScan\",\r\n \"#stats\": {\r\n \"#itemsIn\": 4004,\r\n \"#itemsOut\": 813,\r\n \"#phaseSwitches\": 9641,\r\n \"execTime\": \"1.381997ms\",\r\n \"kernTime\": \"69.065425ms\"\r\n },\r\n \"scan\": {\r\n \"#operator\": \"IndexScan3\",\r\n \"#stats\": {\r\n \"#itemsOut\": 4004,\r\n \"#phaseSwitches\": 16021,\r\n \"execTime\": \"19.678094ms\",\r\n \"kernTime\": \"30.973177ms\",\r\n \"servTime\": \"17.461885ms\"\r\n },\r\n \"index\": \"adv_DISTINCT_public_likes_type\",\r\n \"index_id\": \"288083a758973630\",\r\n \"index_projection\": {\r\n \"primary_key\": true\r\n },\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"spans\": [\r\n {\r\n \"range\": [\r\n {\r\n \"high\": \"[]\",\r\n \"inclusion\": 1,\r\n \"low\": \"\\\"\\\"\"\r\n }\r\n ]\r\n }\r\n ],\r\n \"using\": \"gsi\",\r\n \"#time_normal\": \"00:00.037\",\r\n \"#time_absolute\": 0.037139979000000004\r\n },\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.0013819969999999998\r\n },\r\n {\r\n \"#operator\": \"DistinctScan\",\r\n \"#stats\": {\r\n \"#itemsIn\": 4104,\r\n \"#itemsOut\": 833,\r\n \"#phaseSwitches\": 9881,\r\n \"execTime\": \"2.475034ms\",\r\n \"kernTime\": \"80.914158ms\"\r\n },\r\n \"scan\": {\r\n \"#operator\": \"IndexScan3\",\r\n \"#stats\": {\r\n \"#itemsOut\": 4104,\r\n \"#phaseSwitches\": 16421,\r\n \"execTime\": \"8.610445ms\",\r\n \"kernTime\": \"52.02497ms\",\r\n \"servTime\": \"22.586149ms\"\r\n },\r\n \"index\": \"adv_DISTINCT_reviews_author_type\",\r\n \"index_id\": \"cca7f912cab1a4c6\",\r\n \"index_projection\": {\r\n \"primary_key\": true\r\n },\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"spans\": [\r\n {\r\n \"range\": [\r\n {\r\n \"high\": \"[]\",\r\n \"inclusion\": 1,\r\n \"low\": \"\\\"\\\"\"\r\n }\r\n ]\r\n }\r\n ],\r\n \"using\": \"gsi\",\r\n \"#time_normal\": \"00:00.031\",\r\n \"#time_absolute\": 0.031196594\r\n },\r\n \"#time_normal\": \"00:00.002\",\r\n \"#time_absolute\": 0.002475034\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.00132474\r\n },\r\n {\r\n \"#operator\": \"Fetch\",\r\n \"#stats\": {\r\n \"#itemsIn\": 904,\r\n \"#itemsOut\": 904,\r\n \"#phaseSwitches\": 3733,\r\n \"execTime\": \"2.887995ms\",\r\n \"kernTime\": \"8.826606ms\",\r\n \"servTime\": \"170.010321ms\"\r\n },\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"#time_normal\": \"00:00.172\",\r\n \"#time_absolute\": 0.172898316\r\n },\r\n {\r\n \"#operator\": \"Parallel\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"6.134\u00b5s\"\r\n },\r\n \"copies\": 2,\r\n \"~child\": {\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 2,\r\n \"execTime\": \"3.621\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"Filter\",\r\n \"#stats\": {\r\n \"#itemsIn\": 904,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 1824,\r\n \"execTime\": \"279.461548ms\",\r\n \"kernTime\": \"85.245883ms\"\r\n },\r\n \"condition\": \"(((`travel-sample`.`type`) = \\\"hotel\\\") and (any `l` in (`travel-sample`.`public_likes`) satisfies (`l` like \\\"%Ozella%\\\") end or any `r` in (`travel-sample`.`reviews`) satisfies ((`r`.`author`) like \\\"%Ozella%\\\") end))\",\r\n \"#time_normal\": \"00:00.279\",\r\n \"#time_absolute\": 0.279461548\r\n },\r\n {\r\n \"#operator\": \"InitialProject\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 25,\r\n \"execTime\": \"7.156613ms\",\r\n \"kernTime\": \"357.453351ms\"\r\n },\r\n \"result_terms\": [\r\n {\r\n \"expr\": \"(`travel-sample`.`name`)\"\r\n },\r\n {\r\n \"expr\": \"(`travel-sample`.`address`)\"\r\n },\r\n {\r\n \"expr\": \"(`travel-sample`.`city`)\"\r\n },\r\n {\r\n \"expr\": \"(`travel-sample`.`country`)\"\r\n },\r\n {\r\n \"expr\": \"(`travel-sample`.`phone`)\"\r\n },\r\n {\r\n \"expr\": \"(`travel-sample`.`public_likes`)\"\r\n },\r\n {\r\n \"expr\": \"(`travel-sample`.`reviews`)\"\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.007\",\r\n \"#time_absolute\": 0.007156613\r\n },\r\n {\r\n \"#operator\": \"FinalProject\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 17,\r\n \"execTime\": \"12.167\u00b5s\",\r\n \"kernTime\": \"98.849\u00b5s\"\r\n },\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.000012167\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.000003621\r\n },\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.000006134\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000022349999999999998\r\n },\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.0010408930000000002\r\n },\r\n {\r\n \"#operator\": \"Stream\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 13,\r\n \"execTime\": \"939.145\u00b5s\",\r\n \"kernTime\": \"182.523171ms\"\r\n },\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.000939145\r\n }\r\n ],\r\n \"~versions\": [\r\n \"2.0.0-N1QL\",\r\n \"6.5.0-4960-enterprise\"\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.000001321\r\n}<\/pre>\n <\/p>\n
In the single node cluster being used for these examples, the query elapsed time is around 190-200 milliseconds to return the 5 resulting documents.\u00a0 As you can see in the plan, there are two IndexScan3 operators which use each of the two array indexes we created, followed by a DistinctScan for the results of each index scan, and then a UnionScan.\u00a0 The UnionScan shows an #itemsIn value of 1646 documents and an #itemsOut value of 904 documents, the Fetch operator also gets 904 documents, and finally with the Filter operator we get an #ItemsOut value of 5.\u00a0 The fetch of 904 documents is a waste considering that we ended up with 5 documents returned by the query, and in fact about 170 milliseconds of the overall elapsed time is spent fetching the 905 documents when only 5 are needed.\u00a0\u00a0<\/span><\/p>\nThe Solution<\/span><\/h2>\nBy contrast, an FTS inverted index can easily be created for multiple arrays and is well-suited for cases where you need to search for fields in multiple arrays. We\u2019ll create a FTS index on hotel documents for both the public_likes array and the author field within the reviews array.\u00a0<\/span><\/p>\n <\/p>\n
Index creation steps:\u00a0<\/span><\/h4>\n\n- On the Full Text Search UI, click \u201cAdd Index\u201d.<\/span><\/li>\n
- Specify an index name, e.g. \u201chotel_mult_arrays\u201d, and select the travel-sample bucket.\u00a0<\/span><\/li>\n
- Since each document in the travel-sample bucket has a \u201ctype\u201d field indicating the type of document, leave \u201cJSON type field\u201d set to \u201ctype\u201d.<\/span><\/li>\n
- Under type mappings:\u00a0\u00a0<\/span>\n
\n- Click \u201c+ Add Type Mapping\u201d, and specify \u201chotel\u201d as the type name, since the requirement is to search all hotel documents.\u00a0\u00a0<\/span><\/li>\n
- A list of available analyzers can be accessed by means of the pull-down menu to the right of the type name field.\u00a0 For this use case, leave \u201cinherit\u201d selected so that the type mapping inherits the default analyzer from the index.<\/span><\/li>\n
- Since the requirement is to search the hotel public likes and review author fields, check \u201conly index specified fields\u201d.\u00a0 With this checked, only user-specified fields from the document are included in the index for the hotel type mapping (the mapping will not be dynamic, meaning that all fields are considered available for indexing).\u00a0<\/span><\/li>\n
- Click OK.\u00a0\u00a0<\/span><\/li>\n
- Mouse over the row with the hotel type mapping, click the + button, and then click \u201c<\/span>insert child field<\/b>\u201d.\u00a0 This will allow the public_likes array to be individually included in the index.\u00a0 Specify the following:\u00a0<\/span>\n
\n- field: Enter the name of the field to be indexed, \u201cpublic_likes\u201d.<\/span><\/li>\n
- type: Leave this set to text for the public_likes array.<\/span><\/li>\n
- searchable as: Leave this the same as the field name for the current use case.\u00a0 It can be used to indicate an alternate field name.\u00a0<\/span><\/li>\n
- analyzer: As was done for the type mapping, <\/span>for this use case, <\/span>leave \u201cinherit\u201d selected so <\/span>that the type mapping inherits the default analyzer.<\/span><\/li>\n
- index checkbox: Leave this checked, so that the field is included in the index.\u00a0 Unchecking the box would explicitly remove the field from the index.<\/span><\/li>\n
- store checkbox: Check this setting to include the field content in the search results which permits highlighting of matched expressions in the results.\u00a0 This is useful for testing the index, but not recommended in Prod if highlighting isn\u2019t required since it increases index size.<\/span><\/li>\n
- \u201cinclude in _all field\u201d checkbox: Leave this checked since the use case requirement is to search multiple fields.\u00a0<\/span><\/li>\n
- \u201cinclude term vectors\u201d checkbox: Leave this checked, too, during development and testing of our index to allow highlighting of results.\u00a0<\/span><\/li>\n
- docvalues checkbox: Uncheck this setting.\u00a0 This setting stores the field values in the index which provides support for Search Facets, and for the sorting of search results based on field values, neither of which we need in this use case.\u00a0<\/span><\/li>\n
- Click OK.<\/span><\/li>\n<\/ol>\n<\/li>\n
- Mouse over the row with the hotel type mapping, click the + button, and then click \u201c<\/span>insert child mapping<\/b>\u201d.\u00a0 This will allow the array of review sub-documents to be included in the index.\u00a0 Enter the property name \u201creviews\u201d, leave \u201cinherit\u201d selected in the analyzer drop-down, check \u201conly index specified fields\u201d, and click OK.\u00a0<\/span><\/li>\n
- Mouse over the row with the reviews child mapping, click the + button, and then click \u201c<\/span>insert child field<\/b>\u201d.\u00a0 This will allow the author field from the array of review sub-documents to be included in the index.\u00a0 Specify the following:\u00a0<\/span>\n
\n- field: Enter the name of the field to be indexed, \u201cauthor\u201d.<\/span><\/li>\n
- type: Leave this set to text for the author field.<\/span><\/li>\n
- searchable as: Leave this the same as the field name for the current use case.\u00a0 It can be used to indicate an alternate field name.\u00a0<\/span><\/li>\n
- analyzer: As was done for the type mapping, <\/span>for this use case, <\/span>leave \u201cinherit\u201d selected so <\/span>that the type mapping inherits the default analyzer.<\/span><\/li>\n
- index checkbox: Leave this checked, so that the field is included in the index.\u00a0 Unchecking the box would explicitly remove the field from the index.<\/span><\/li>\n
- store checkbox: Check this setting to include the field content in the search results which permits highlighting of matched expressions in the results.\u00a0 This is useful for testing the index, but not recommended in Prod if highlighting isn\u2019t required since it increases index size.<\/span><\/li>\n
- \u201cinclude in _all field\u201d checkbox: Leave this checked since the use case requirement is to search multiple fields.\u00a0<\/span><\/li>\n
- \u201cinclude term vectors\u201d checkbox: Leave this checked, too, during development and testing of our index to allow highlighting of results.\u00a0\u00a0<\/span><\/li>\n
- docvalues checkbox: Uncheck this setting.\u00a0 This setting stores the field values in the index which provides support for Search Facets, and for the sorting of search results based on field values, neither of which we need in this use case.\u00a0<\/span><\/li>\n
- Click OK.<\/span><\/li>\n<\/ol>\n<\/li>\n
- Finally, uncheck the checkbox next to the \u201cdefault\u201d type mapping.\u00a0 If the default mapping is left enabled, all documents in the bucket are included in the index, regardless of whether the user actively specifies type mappings. Only the hotel documents are required, and they are included by the hotel type mapping added previously.\u00a0<\/span><\/li>\n<\/ol>\n<\/li>\n
- The default values suffice for the remaining collapsed panels (Analyzers, Custom Filters, Date\/Time Parsers, and Advanced).\u00a0<\/span><\/li>\n
- Index Replicas can be set to 1, 2 or 3, provided that the cluster is running the Search service on n+1 nodes. With a single node development environment, maintain the default value of 0.\u00a0<\/span><\/li>\n
- For Index Type, the default value of \u201cVersion 6.0 (Scorch)\u201d is appropriate for any newly created indexes. Scorch reduces the size of the index on disk and provides enhanced MongoDB in operator performance for indexing and mutation-handling.<\/span><\/li>\n
- Index Partitions can be left to the default value of 6.\u00a0<\/span><\/li>\n
- At this point, the create index page should look like <\/span>the last frame captured in<\/span> Figure 5.\u00a0 Click \u201cCreate Index\u201d to complete the process.\u00a0<\/span><\/li>\n<\/ol>\n
![\"\"](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/03\/BKane-FTS-Array-Figure-5-creating-FTS-index-capture.gif\")
\u00a0<\/span><\/p>\nFigure 5 – Creating FTS index with multiple arrays<\/span><\/i><\/p>\n <\/p>\n
Note: See Appendix for the JSON payload used to create this index through the REST API.<\/span><\/p>\n <\/p>\n
Testing queries against the index:\u00a0<\/span><\/p>\n\n- On the Full Text Search UI, wait for indexing progress to show 100%, then click on the index name \u201chotel_mult_arrays\u201d.\u00a0<\/span><\/li>\n
- To search for any hotels with likes or reviews by someone named \u201cOzella\u201d, in the \u201csearch this index\u2026\u201d text box, enter \u201cOzella\u201d and click Search.\u00a0 Field-scoping of the search is not required because both indexed fields are included in the default field \u201c<\/span>_all\u201d.<\/span><\/li>\n
- The results are shown (similar to Figure 6) with the key of each matching document and highlighted matching fields.\u00a0 The document IDs returned are the same as those from our earlier N1QL query.\u00a0\u00a0<\/span><\/li>\n<\/ol>\n
![\"\"](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/03\/BKane-FTS-Array-Figure-6-testing-FTS-index-.png\")
<\/p>\n
Figure 6 – Index \u201chotel_mult_arrays\u201d search results for \u201cOzella\u201d<\/span><\/i><\/p>\n <\/p>\n
This is a single index on 2 array keys, which, as mentioned earlier, is something you could never do in a b-tree based index.\u00a0 So now let\u2019s take advantage of this FTS index in a N1QL query by using the SEARCH() function. Our query could look like this:\u00a0<\/span><\/p>\nSELECT name, address, city, country,\r\n phone, public_likes, reviews\r\nFROM `travel-sample` AS t USE INDEX(hotel_mult_arrays USING FTS)\r\nWHERE t.type=\"hotel\"\r\nAND SEARCH(t, {\"query\": {\"match\":\"Ozella\"}}, {\"index\":\"hotel_mult_arrays\"});<\/pre>\n <\/p>\n
A few things to note about the query:\u00a0<\/span><\/p>\n\n- The USE INDEX…USING FTS clause specifies that the FTS index should be used rather than a GSI index, so this query doesn\u2019t use the index service. (<\/span>Documentation<\/span><\/a>)<\/span><\/li>\n
- Because our FTS index uses a custom type mapping, the query needs to have the matching type specified in the WHERE clause (t.type=”hotel”).\u00a0\u00a0<\/span><\/li>\n
- The FTS index name is specified in the \u201cindex\u201d field in the SEARCH() function as a hint, but that is optional since the USE INDEX clause takes precedence over a hint provided in the \u201cindex\u201d field.\u00a0 (<\/span>Documentation<\/span><\/a>)\u00a0\u00a0<\/span><\/li>\n<\/ul>\n
Using the FTS index we created, our N1QL query runs successfully and returns 5 results (hotel_5081, hotel_26020, hotel_10025, hotel_20425, hotel_25327) and the following execution plan:\u00a0<\/span><\/p>\n![\"\"](\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/03\/BKane-FTS-Array-Figure-7-IndexFtsSearch-plan.png\")
<\/p>\n
Figure 7 – Execution plan using multiple indexes (FTS)<\/span><\/i><\/p>\nSame plan in JSON:<\/span><\/p>\n{\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"18.8\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"Authorize\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 3,\r\n \"execTime\": \"32.1\u00b5s\",\r\n \"servTime\": \"3.421ms\"\r\n },\r\n \"privileges\": {\r\n \"List\": [\r\n {\r\n \"Target\": \"default:travel-sample\",\r\n \"Priv\": 7\r\n }\r\n ]\r\n },\r\n \"~child\": {\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"122.8\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"IndexFtsSearch\",\r\n \"#stats\": {\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 23,\r\n \"execTime\": \"239.3\u00b5s\",\r\n \"kernTime\": \"84.5\u00b5s\",\r\n \"servTime\": \"3.9146ms\"\r\n },\r\n \"as\": \"t\",\r\n \"index\": \"hotel_mult_arrays\",\r\n \"index_id\": \"7a28a8346fad6118\",\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"search_info\": {\r\n \"field\": \"\\\"\\\"\",\r\n \"options\": \"{\\\"index\\\": \\\"hotel_mult_arrays\\\"}\",\r\n \"outname\": \"out\",\r\n \"query\": \"{\\\"query\\\": {\\\"match\\\": \\\"Ozella\\\"}}\"\r\n },\r\n \"using\": \"fts\",\r\n \"#time_normal\": \"00:00.004\",\r\n \"#time_absolute\": 0.0041539\r\n },\r\n {\r\n \"#operator\": \"Fetch\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 25,\r\n \"execTime\": \"334.8\u00b5s\",\r\n \"kernTime\": \"4.4328ms\",\r\n \"servTime\": \"1.5272ms\"\r\n },\r\n \"as\": \"t\",\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.001862\r\n },\r\n {\r\n \"#operator\": \"Parallel\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"21.1\u00b5s\"\r\n },\r\n \"copies\": 2,\r\n \"~child\": {\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 2,\r\n \"execTime\": \"49.1\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"Filter\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 26,\r\n \"execTime\": \"6.8953ms\",\r\n \"kernTime\": \"14.8149ms\"\r\n },\r\n \"condition\": \"(((`t`.`type`) = \\\"hotel\\\") and search(`t`, {\\\"query\\\": {\\\"match\\\": \\\"Ozella\\\"}}, {\\\"index\\\": \\\"hotel_mult_arrays\\\"}))\",\r\n \"#time_normal\": \"00:00.006\",\r\n \"#time_absolute\": 0.0068953\r\n },\r\n {\r\n \"#operator\": \"InitialProject\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 25,\r\n \"execTime\": \"2.3597ms\",\r\n \"kernTime\": \"20.7458ms\"\r\n },\r\n \"result_terms\": [\r\n {\r\n \"expr\": \"(`t`.`name`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`address`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`city`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`country`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`phone`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`public_likes`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`reviews`)\"\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.002\",\r\n \"#time_absolute\": 0.0023597\r\n },\r\n {\r\n \"#operator\": \"FinalProject\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 17,\r\n \"execTime\": \"300\u00b5s\",\r\n \"kernTime\": \"375.9\u00b5s\"\r\n },\r\n \"#time_normal\": \"00:00\",\r\n \"#time_absolute\": 0\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000491\r\n },\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000211\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0001228\r\n },\r\n \"#time_normal\": \"00:00.003\",\r\n \"#time_absolute\": 0.0034531\r\n },\r\n {\r\n \"#operator\": \"Stream\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 13,\r\n \"execTime\": \"1.3409ms\",\r\n \"kernTime\": \"14.8586ms\"\r\n },\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.0013409\r\n }\r\n ],\r\n \"~versions\": [\r\n \"2.0.0-N1QL\",\r\n \"6.5.0-4960-enterprise\"\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000188\r\n}\r\n<\/pre>\n <\/p>\n
In the single node cluster being used for these examples, the query elapsed time is around 20 milliseconds to return the same 5 documents.\u00a0 As you can see in the plan, there is an IndexFtsSearch operator but there are no IndexScan3, DistinctScan, UnionScan, or IntersectScan operators.\u00a0 The overall query is much more efficient without these expensive GSI operators. The IndexFtsSearch operator sends the 5 matching documents from the FTS index to the Fetch operator which gets only those 5 documents.\u00a0 The fetch is much more efficient here than in the previous query since it\u2019s only fetching 5 vs 904 documents, and this can also be observed in the comparison of overall elapsed times (and the servTime for the fetch operators: 170ms in query 1 and 1.5ms in query 2) between the queries.\u00a0\u00a0<\/span><\/p>\n <\/p>\n
Conclusion<\/span><\/h2>\nWith GSI you can mix and match multiple array indexes in a single query, but with FTS you can mix and match multiple arrays in a single FTS index (and with FTS there is no leading key problem as in GSI regarding the order of the fields in the index).\u00a0 As we\u2019ve shown in this simple example of querying 2 arrays in the hotel documents, utilizing the new SEARCH() function in N1QL can result in simpler and more performant array queries. The same concept could be applied to queries utilizing several arrays, which would have even more favorable results over N1QL queries utilizing multiple GSI array indexes.\u00a0 This approach uses fewer system resources and provides higher throughput, which results in an increase in overall system efficiency.\u00a0\u00a0<\/span><\/p>\nThis is just one example of the benefits of the integration between N1QL and FTS, and other benefits are documented in the blog posts in the references section below.\u00a0<\/span><\/p>\n <\/p>\n
References<\/span><\/h2>\n\n- Couchbase Search Resources: <\/span>https:\/\/www.couchbase.com\/products\/full-text-search<\/span><\/a><\/li>\n
- Couchbase FTS Documentation: <\/span>https:\/\/docs.couchbase.com\/server\/current\/fts\/full-text-intro.html<\/span><\/a><\/li>\n
- Couchbase N1QL Search Documentation: <\/span>https:\/\/docs.couchbase.com\/server\/current\/n1ql\/n1ql-language-reference\/searchfun.html<\/span><\/a><\/li>\n
- Couchbase FTS Blog Posts: <\/span>https:\/\/www.couchbase.com\/blog\/category\/full-text-search\/<\/span><\/a><\/li>\n
- Couchbase FTS Online Training: <\/span>https:\/\/learn.couchbase.com\/store\/509465-cb121-intro-to-couchbase-full-text-search-fts<\/span><\/a><\/li>\n<\/ul>\n
<\/p>\n
Appendix<\/span><\/h2>\nIndex Definition JSON: hotel_mult_arrays<\/span><\/h3>\n <\/p>\n
{\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"18.8\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"Authorize\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 3,\r\n \"execTime\": \"32.1\u00b5s\",\r\n \"servTime\": \"3.421ms\"\r\n },\r\n \"privileges\": {\r\n \"List\": [\r\n {\r\n \"Target\": \"default:travel-sample\",\r\n \"Priv\": 7\r\n }\r\n ]\r\n },\r\n \"~child\": {\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"122.8\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"IndexFtsSearch\",\r\n \"#stats\": {\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 23,\r\n \"execTime\": \"239.3\u00b5s\",\r\n \"kernTime\": \"84.5\u00b5s\",\r\n \"servTime\": \"3.9146ms\"\r\n },\r\n \"as\": \"t\",\r\n \"index\": \"hotel_mult_arrays\",\r\n \"index_id\": \"7a28a8346fad6118\",\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"search_info\": {\r\n \"field\": \"\\\"\\\"\",\r\n \"options\": \"{\\\"index\\\": \\\"hotel_mult_arrays\\\"}\",\r\n \"outname\": \"out\",\r\n \"query\": \"{\\\"query\\\": {\\\"match\\\": \\\"Ozella\\\"}}\"\r\n },\r\n \"using\": \"fts\",\r\n \"#time_normal\": \"00:00.004\",\r\n \"#time_absolute\": 0.0041539\r\n },\r\n {\r\n \"#operator\": \"Fetch\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 25,\r\n \"execTime\": \"334.8\u00b5s\",\r\n \"kernTime\": \"4.4328ms\",\r\n \"servTime\": \"1.5272ms\"\r\n },\r\n \"as\": \"t\",\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.001862\r\n },\r\n {\r\n \"#operator\": \"Parallel\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"21.1\u00b5s\"\r\n },\r\n \"copies\": 2,\r\n \"~child\": {\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 2,\r\n \"execTime\": \"49.1\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"Filter\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 26,\r\n \"execTime\": \"6.8953ms\",\r\n \"kernTime\": \"14.8149ms\"\r\n },\r\n \"condition\": \"(((`t`.`type`) = \\\"hotel\\\") and search(`t`, {\\\"query\\\": {\\\"match\\\": \\\"Ozella\\\"}}, {\\\"index\\\": \\\"hotel_mult_arrays\\\"}))\",\r\n \"#time_normal\": \"00:00.006\",\r\n \"#time_absolute\": 0.0068953\r\n },\r\n {\r\n \"#operator\": \"InitialProject\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 25,\r\n \"execTime\": \"2.3597ms\",\r\n \"kernTime\": \"20.7458ms\"\r\n },\r\n \"result_terms\": [\r\n {\r\n \"expr\": \"(`t`.`name`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`address`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`city`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`country`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`phone`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`public_likes`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`reviews`)\"\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.002\",\r\n \"#time_absolute\": 0.0023597\r\n },\r\n {\r\n \"#operator\": \"FinalProject\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 17,\r\n \"execTime\": \"300\u00b5s\",\r\n \"kernTime\": \"375.9\u00b5s\"\r\n },\r\n \"#time_normal\": \"00:00\",\r\n \"#time_absolute\": 0\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000491\r\n },\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000211\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0001228\r\n },\r\n \"#time_normal\": \"00:00.003\",\r\n \"#time_absolute\": 0.0034531\r\n },\r\n {\r\n \"#operator\": \"Stream\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 13,\r\n \"execTime\": \"1.3409ms\",\r\n \"kernTime\": \"14.8586ms\"\r\n },\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.0013409\r\n }\r\n ],\r\n \"~versions\": [\r\n \"2.0.0-N1QL\",\r\n \"6.5.0-4960-enterprise\"\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000188\r\n}\r\n<\/pre>\n <\/p>\n","protected":false},"excerpt":{"rendered":"
Introduction Couchbase Full Text Search (FTS) is a great fit for indexing multiple arrays and executing queries with multiple filter predicates in arrays.\u00a0 In this article, I\u2019ll demonstrate the advantages of using FTS over GSI (Global Secondary Index) for array […]<\/p>\n","protected":false},"author":16362,"featured_media":8371,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[1815,2165,2453,1812],"tags":[1505],"ppma_author":[9100],"class_list":["post-8362","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-best-practices-and-tutorials","category-full-text-search","category-global-secondary-index","category-n1ql-query","tag-index"],"yoast_head":"\n
MongoDB in Operator Performance: Querying Multiple Arrays<\/title>\n<meta name=\"description\" content=\"Learn the advantage of using FTS over GSI for array indexing with an example that requires querying multiple arrays with MongoDB in operator performance.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"FTS and N1QL: Better MongoDB in Operator Performance Querying Multiple Arrays\" \/>\n<meta property=\"og:description\" content=\"Learn the advantage of using FTS over GSI for array indexing with an example that requires querying multiple arrays with MongoDB in operator performance.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/\" \/>\n<meta property=\"og:site_name\" content=\"The Couchbase Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-03-27T01:08:28+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-06-14T03:59:06+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/03\/BKane-FTS-Array-scrabble.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1279\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Brian Kane, Solutions Engineer, Couchbase\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Brian Kane, Solutions Engineer, Couchbase\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"16 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/\"},\"author\":{\"name\":\"Brian Kane, Solutions Engineer, Couchbase\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#\\\/schema\\\/person\\\/69569e5446e8ee83e32f8e5ed50a250e\"},\"headline\":\"FTS and N1QL: Better MongoDB in Operator Performance Querying Multiple Arrays\",\"datePublished\":\"2020-03-27T01:08:28+00:00\",\"dateModified\":\"2025-06-14T03:59:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/\"},\"wordCount\":2277,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/1\\\/2020\\\/03\\\/BKane-FTS-Array-scrabble.jpg\",\"keywords\":[\"Index\"],\"articleSection\":[\"Best Practices and Tutorials\",\"Full-Text Search\",\"Global Secondary Index\",\"SQL++ \\\/ N1QL Query\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/\",\"url\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/\",\"name\":\"MongoDB in Operator Performance: Querying Multiple Arrays\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/1\\\/2020\\\/03\\\/BKane-FTS-Array-scrabble.jpg\",\"datePublished\":\"2020-03-27T01:08:28+00:00\",\"dateModified\":\"2025-06-14T03:59:06+00:00\",\"description\":\"Learn the advantage of using FTS over GSI for array indexing with an example that requires querying multiple arrays with MongoDB in operator performance.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/1\\\/2020\\\/03\\\/BKane-FTS-Array-scrabble.jpg\",\"contentUrl\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/1\\\/2020\\\/03\\\/BKane-FTS-Array-scrabble.jpg\",\"width\":1920,\"height\":1279},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"FTS and N1QL: Better MongoDB in Operator Performance Querying Multiple Arrays\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/\",\"name\":\"The Couchbase Blog\",\"description\":\"Couchbase, the NoSQL Database\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#organization\",\"name\":\"The Couchbase Blog\",\"url\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/wp-content\\\/uploads\\\/2023\\\/04\\\/admin-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/wp-content\\\/uploads\\\/2023\\\/04\\\/admin-logo.png\",\"width\":218,\"height\":34,\"caption\":\"The Couchbase Blog\"},\"image\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#\\\/schema\\\/person\\\/69569e5446e8ee83e32f8e5ed50a250e\",\"name\":\"Brian Kane, Solutions Engineer, Couchbase\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=gd6ceb7493ed6dce23e12c34a4d31643e\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=g\",\"caption\":\"Brian Kane, Solutions Engineer, Couchbase\"},\"description\":\"Brian Kane is a Solutions Engineer at Couchbase and has been working in application development and with database technologies since 1996. He is based in the Houston, Texas area.\",\"url\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/author\\\/brian-kanecouchbase-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"MongoDB in Operator Performance: Querying Multiple Arrays","description":"Learn the advantage of using FTS over GSI for array indexing with an example that requires querying multiple arrays with MongoDB in operator performance.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/","og_locale":"en_US","og_type":"article","og_title":"FTS and N1QL: Better MongoDB in Operator Performance Querying Multiple Arrays","og_description":"Learn the advantage of using FTS over GSI for array indexing with an example that requires querying multiple arrays with MongoDB in operator performance.","og_url":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/","og_site_name":"The Couchbase Blog","article_published_time":"2020-03-27T01:08:28+00:00","article_modified_time":"2025-06-14T03:59:06+00:00","og_image":[{"width":1920,"height":1279,"url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/03\/BKane-FTS-Array-scrabble.jpg","type":"image\/jpeg"}],"author":"Brian Kane, Solutions Engineer, Couchbase","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Brian Kane, Solutions Engineer, Couchbase","Est. reading time":"16 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#article","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/"},"author":{"name":"Brian Kane, Solutions Engineer, Couchbase","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/69569e5446e8ee83e32f8e5ed50a250e"},"headline":"FTS and N1QL: Better MongoDB in Operator Performance Querying Multiple Arrays","datePublished":"2020-03-27T01:08:28+00:00","dateModified":"2025-06-14T03:59:06+00:00","mainEntityOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/"},"wordCount":2277,"commentCount":0,"publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/03\/BKane-FTS-Array-scrabble.jpg","keywords":["Index"],"articleSection":["Best Practices and Tutorials","Full-Text Search","Global Secondary Index","SQL++ \/ N1QL Query"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/","url":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/","name":"MongoDB in Operator Performance: Querying Multiple Arrays","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#primaryimage"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/03\/BKane-FTS-Array-scrabble.jpg","datePublished":"2020-03-27T01:08:28+00:00","dateModified":"2025-06-14T03:59:06+00:00","description":"Learn the advantage of using FTS over GSI for array indexing with an example that requires querying multiple arrays with MongoDB in operator performance.","breadcrumb":{"@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#primaryimage","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/03\/BKane-FTS-Array-scrabble.jpg","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/03\/BKane-FTS-Array-scrabble.jpg","width":1920,"height":1279},{"@type":"BreadcrumbList","@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.couchbase.com\/blog\/"},{"@type":"ListItem","position":2,"name":"FTS and N1QL: Better MongoDB in Operator Performance Querying Multiple Arrays"}]},{"@type":"WebSite","@id":"https:\/\/www.couchbase.com\/blog\/#website","url":"https:\/\/www.couchbase.com\/blog\/","name":"The Couchbase Blog","description":"Couchbase, the NoSQL Database","publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.couchbase.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.couchbase.com\/blog\/#organization","name":"The Couchbase Blog","url":"https:\/\/www.couchbase.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png","width":218,"height":34,"caption":"The Couchbase Blog"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/69569e5446e8ee83e32f8e5ed50a250e","name":"Brian Kane, Solutions Engineer, Couchbase","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=gd6ceb7493ed6dce23e12c34a4d31643e","url":"https:\/\/secure.gravatar.com\/avatar\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=g","caption":"Brian Kane, Solutions Engineer, Couchbase"},"description":"Brian Kane is a Solutions Engineer at Couchbase and has been working in application development and with database technologies since 1996. He is based in the Houston, Texas area.","url":"https:\/\/www.couchbase.com\/blog\/author\/brian-kanecouchbase-com\/"}]}},"acf":[],"authors":[{"term_id":9100,"user_id":16362,"is_guest":0,"slug":"brian-kanecouchbase-com","display_name":"Brian Kane, Solutions Engineer, Couchbase","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=g","0":null,"1":"","2":"","3":"","4":"","5":"","6":"","7":"","8":""}],"_links":{"self":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/8362","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/users\/16362"}],"replies":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/comments?post=8362"}],"version-history":[{"count":0,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/8362\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media\/8371"}],"wp:attachment":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media?parent=8362"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/categories?post=8362"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/tags?post=8362"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=8362"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- \n
- A key that acts as a primary key<\/span><\/li>\n
- An id field that identifies the document<\/span><\/li>\n
- A type field that identifies the kind of document<\/span><\/li>\n<\/ul>\n
<\/p>\n
The examples in this article will be using the hotel documents. The sample document below gives you an idea of the structure of a hotel document:\u00a0<\/span><\/p>\n
<\/p>\nFigure 1 – Sample Hotel Document<\/span><\/i><\/p>\n
The Problem<\/span><\/h2>\n
Our example is a use case where a user can search for hotels which have been reviewed or liked by a person with a particular name. This requires querying hotel documents on both public likes and reviews, which are arrays within the hotel document model:\u00a0<\/span><\/p>\n
<\/p>\nFigure 2 – The \u201cpublic_likes\u201d and \u201creviews\u201d arrays in the sample hotel document<\/span><\/i><\/p>\n
<\/p>\n
First let\u2019s look at implementing this use case with N1QL and GSI (Global Secondary Index). To find hotels that anyone named Ozella has either liked or reviewed, the query could look like this:\u00a0<\/span><\/p>\n
SELECT name, address, city, country,\r\n phone, public_likes, reviews\r\nFROM `travel-sample`\r\nWHERE type=\"hotel\"\r\n AND (ANY l IN public_likes SATISFIES l LIKE \"%Ozella%\" END\r\n OR ANY r IN reviews SATISFIES r.author LIKE \"%Ozella%\" END);<\/pre>\n
<\/p>\n
We need to create an appropriate index for this query.\u00a0 Maybe something like this that indexes both arrays of interest for hotel documents:\u00a0<\/span><\/p>\n
CREATE INDEX idx_hotel_public_likes_review_author ON `travel-sample`\r\n(DISTINCT ARRAY `l` FOR l IN `public_likes` END, \r\nDISTINCT ARRAY `r`.`author` FOR r IN `reviews` END)\r\nWHERE `type` = 'hotel';<\/pre>\n
<\/p>\n
This doesn\u2019t work, and we get the error shown in Figure 3:<\/span><\/p>\n
<\/span>Figure 3 – Error creating index with multiple arrays<\/span><\/i><\/p>\n<\/p>\n
As Keshav Murthy wrote in his blog post <\/span>Search and Rescue: 7 Reasons for N1QL (SQL) developers to use Search<\/span><\/a> (problem #6), with N1QL in Couchbase, \u201c<\/span>to get the best performance while searching inside arrays, you need to <\/span><\/i>create indexes with array keys<\/span><\/i><\/a>. The array index comes with a limitation: each array index can only have one array key per index. So, when you have a customer object with multiple array fields, you can\u2019t search all of them using a single index…causing expensive queries.<\/span><\/i>\u201d As Keshav notes in that article, this is a limitation with b-tree indexes in databases generally.\u00a0<\/span><\/p>\n
<\/p>\n
So now let\u2019s try two separate array indexes.\u00a0 The indexes to support this query could look like these, which were created using the <\/span>Couchbase N1QL Index Advisor<\/span><\/a>, a new (DP) feature in Couchbase 6.5:<\/span><\/p>\n
\u00a0<\/span><\/p>\n
CREATE INDEX adv_DISTINCT_public_likes_type \r\nON `travel-sample`(DISTINCT ARRAY `l` FOR l in `public_likes` END) \r\nWHERE `type` = 'hotel';\r\n\r\n\r\nCREATE INDEX adv_DISTINCT_reviews_author_type \r\nON `travel-sample`(DISTINCT ARRAY `r`.`author` FOR r in `reviews` END) \r\nWHERE `type` = 'hotel';<\/pre>\n
<\/p>\n
With those 2 indexes in place, our query runs successfully with 5 results (hotel_26020, hotel_10025, hotel_5081, hotel_20425, hotel_25327) and the following execution plan:\u00a0<\/span><\/p>\n
<\/p>\nFigure 4 – Execution plan using multiple indexes (GSI)<\/span><\/i><\/p>\n
Same plan in JSON:<\/span><\/p>\n
{\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"1.321\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"Authorize\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 3,\r\n \"execTime\": \"3.034\u00b5s\",\r\n \"servTime\": \"1.037859ms\"\r\n },\r\n \"privileges\": {\r\n \"List\": [\r\n {\r\n \"Target\": \"default:travel-sample\",\r\n \"Priv\": 7\r\n }\r\n ]\r\n },\r\n \"~child\": {\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"2.235\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"UnionScan\",\r\n \"#stats\": {\r\n \"#itemsIn\": 1646,\r\n \"#itemsOut\": 904,\r\n \"#phaseSwitches\": 5107,\r\n \"execTime\": \"1.32474ms\",\r\n \"kernTime\": \"113.495553ms\"\r\n },\r\n \"scans\": [\r\n {\r\n \"#operator\": \"DistinctScan\",\r\n \"#stats\": {\r\n \"#itemsIn\": 4004,\r\n \"#itemsOut\": 813,\r\n \"#phaseSwitches\": 9641,\r\n \"execTime\": \"1.381997ms\",\r\n \"kernTime\": \"69.065425ms\"\r\n },\r\n \"scan\": {\r\n \"#operator\": \"IndexScan3\",\r\n \"#stats\": {\r\n \"#itemsOut\": 4004,\r\n \"#phaseSwitches\": 16021,\r\n \"execTime\": \"19.678094ms\",\r\n \"kernTime\": \"30.973177ms\",\r\n \"servTime\": \"17.461885ms\"\r\n },\r\n \"index\": \"adv_DISTINCT_public_likes_type\",\r\n \"index_id\": \"288083a758973630\",\r\n \"index_projection\": {\r\n \"primary_key\": true\r\n },\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"spans\": [\r\n {\r\n \"range\": [\r\n {\r\n \"high\": \"[]\",\r\n \"inclusion\": 1,\r\n \"low\": \"\\\"\\\"\"\r\n }\r\n ]\r\n }\r\n ],\r\n \"using\": \"gsi\",\r\n \"#time_normal\": \"00:00.037\",\r\n \"#time_absolute\": 0.037139979000000004\r\n },\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.0013819969999999998\r\n },\r\n {\r\n \"#operator\": \"DistinctScan\",\r\n \"#stats\": {\r\n \"#itemsIn\": 4104,\r\n \"#itemsOut\": 833,\r\n \"#phaseSwitches\": 9881,\r\n \"execTime\": \"2.475034ms\",\r\n \"kernTime\": \"80.914158ms\"\r\n },\r\n \"scan\": {\r\n \"#operator\": \"IndexScan3\",\r\n \"#stats\": {\r\n \"#itemsOut\": 4104,\r\n \"#phaseSwitches\": 16421,\r\n \"execTime\": \"8.610445ms\",\r\n \"kernTime\": \"52.02497ms\",\r\n \"servTime\": \"22.586149ms\"\r\n },\r\n \"index\": \"adv_DISTINCT_reviews_author_type\",\r\n \"index_id\": \"cca7f912cab1a4c6\",\r\n \"index_projection\": {\r\n \"primary_key\": true\r\n },\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"spans\": [\r\n {\r\n \"range\": [\r\n {\r\n \"high\": \"[]\",\r\n \"inclusion\": 1,\r\n \"low\": \"\\\"\\\"\"\r\n }\r\n ]\r\n }\r\n ],\r\n \"using\": \"gsi\",\r\n \"#time_normal\": \"00:00.031\",\r\n \"#time_absolute\": 0.031196594\r\n },\r\n \"#time_normal\": \"00:00.002\",\r\n \"#time_absolute\": 0.002475034\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.00132474\r\n },\r\n {\r\n \"#operator\": \"Fetch\",\r\n \"#stats\": {\r\n \"#itemsIn\": 904,\r\n \"#itemsOut\": 904,\r\n \"#phaseSwitches\": 3733,\r\n \"execTime\": \"2.887995ms\",\r\n \"kernTime\": \"8.826606ms\",\r\n \"servTime\": \"170.010321ms\"\r\n },\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"#time_normal\": \"00:00.172\",\r\n \"#time_absolute\": 0.172898316\r\n },\r\n {\r\n \"#operator\": \"Parallel\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"6.134\u00b5s\"\r\n },\r\n \"copies\": 2,\r\n \"~child\": {\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 2,\r\n \"execTime\": \"3.621\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"Filter\",\r\n \"#stats\": {\r\n \"#itemsIn\": 904,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 1824,\r\n \"execTime\": \"279.461548ms\",\r\n \"kernTime\": \"85.245883ms\"\r\n },\r\n \"condition\": \"(((`travel-sample`.`type`) = \\\"hotel\\\") and (any `l` in (`travel-sample`.`public_likes`) satisfies (`l` like \\\"%Ozella%\\\") end or any `r` in (`travel-sample`.`reviews`) satisfies ((`r`.`author`) like \\\"%Ozella%\\\") end))\",\r\n \"#time_normal\": \"00:00.279\",\r\n \"#time_absolute\": 0.279461548\r\n },\r\n {\r\n \"#operator\": \"InitialProject\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 25,\r\n \"execTime\": \"7.156613ms\",\r\n \"kernTime\": \"357.453351ms\"\r\n },\r\n \"result_terms\": [\r\n {\r\n \"expr\": \"(`travel-sample`.`name`)\"\r\n },\r\n {\r\n \"expr\": \"(`travel-sample`.`address`)\"\r\n },\r\n {\r\n \"expr\": \"(`travel-sample`.`city`)\"\r\n },\r\n {\r\n \"expr\": \"(`travel-sample`.`country`)\"\r\n },\r\n {\r\n \"expr\": \"(`travel-sample`.`phone`)\"\r\n },\r\n {\r\n \"expr\": \"(`travel-sample`.`public_likes`)\"\r\n },\r\n {\r\n \"expr\": \"(`travel-sample`.`reviews`)\"\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.007\",\r\n \"#time_absolute\": 0.007156613\r\n },\r\n {\r\n \"#operator\": \"FinalProject\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 17,\r\n \"execTime\": \"12.167\u00b5s\",\r\n \"kernTime\": \"98.849\u00b5s\"\r\n },\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.000012167\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.000003621\r\n },\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.000006134\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000022349999999999998\r\n },\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.0010408930000000002\r\n },\r\n {\r\n \"#operator\": \"Stream\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 13,\r\n \"execTime\": \"939.145\u00b5s\",\r\n \"kernTime\": \"182.523171ms\"\r\n },\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.000939145\r\n }\r\n ],\r\n \"~versions\": [\r\n \"2.0.0-N1QL\",\r\n \"6.5.0-4960-enterprise\"\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.000001321\r\n}<\/pre>\n<\/p>\n
In the single node cluster being used for these examples, the query elapsed time is around 190-200 milliseconds to return the 5 resulting documents.\u00a0 As you can see in the plan, there are two IndexScan3 operators which use each of the two array indexes we created, followed by a DistinctScan for the results of each index scan, and then a UnionScan.\u00a0 The UnionScan shows an #itemsIn value of 1646 documents and an #itemsOut value of 904 documents, the Fetch operator also gets 904 documents, and finally with the Filter operator we get an #ItemsOut value of 5.\u00a0 The fetch of 904 documents is a waste considering that we ended up with 5 documents returned by the query, and in fact about 170 milliseconds of the overall elapsed time is spent fetching the 905 documents when only 5 are needed.\u00a0\u00a0<\/span><\/p>\n
The Solution<\/span><\/h2>\n
By contrast, an FTS inverted index can easily be created for multiple arrays and is well-suited for cases where you need to search for fields in multiple arrays. We\u2019ll create a FTS index on hotel documents for both the public_likes array and the author field within the reviews array.\u00a0<\/span><\/p>\n
<\/p>\n
Index creation steps:\u00a0<\/span><\/h4>\n
- \n
- On the Full Text Search UI, click \u201cAdd Index\u201d.<\/span><\/li>\n
- Specify an index name, e.g. \u201chotel_mult_arrays\u201d, and select the travel-sample bucket.\u00a0<\/span><\/li>\n
- Since each document in the travel-sample bucket has a \u201ctype\u201d field indicating the type of document, leave \u201cJSON type field\u201d set to \u201ctype\u201d.<\/span><\/li>\n
- Under type mappings:\u00a0\u00a0<\/span>\n
- \n
- Click \u201c+ Add Type Mapping\u201d, and specify \u201chotel\u201d as the type name, since the requirement is to search all hotel documents.\u00a0\u00a0<\/span><\/li>\n
- A list of available analyzers can be accessed by means of the pull-down menu to the right of the type name field.\u00a0 For this use case, leave \u201cinherit\u201d selected so that the type mapping inherits the default analyzer from the index.<\/span><\/li>\n
- Since the requirement is to search the hotel public likes and review author fields, check \u201conly index specified fields\u201d.\u00a0 With this checked, only user-specified fields from the document are included in the index for the hotel type mapping (the mapping will not be dynamic, meaning that all fields are considered available for indexing).\u00a0<\/span><\/li>\n
- Click OK.\u00a0\u00a0<\/span><\/li>\n
- Mouse over the row with the hotel type mapping, click the + button, and then click \u201c<\/span>insert child field<\/b>\u201d.\u00a0 This will allow the public_likes array to be individually included in the index.\u00a0 Specify the following:\u00a0<\/span>\n
- \n
- field: Enter the name of the field to be indexed, \u201cpublic_likes\u201d.<\/span><\/li>\n
- type: Leave this set to text for the public_likes array.<\/span><\/li>\n
- searchable as: Leave this the same as the field name for the current use case.\u00a0 It can be used to indicate an alternate field name.\u00a0<\/span><\/li>\n
- analyzer: As was done for the type mapping, <\/span>for this use case, <\/span>leave \u201cinherit\u201d selected so <\/span>that the type mapping inherits the default analyzer.<\/span><\/li>\n
- index checkbox: Leave this checked, so that the field is included in the index.\u00a0 Unchecking the box would explicitly remove the field from the index.<\/span><\/li>\n
- store checkbox: Check this setting to include the field content in the search results which permits highlighting of matched expressions in the results.\u00a0 This is useful for testing the index, but not recommended in Prod if highlighting isn\u2019t required since it increases index size.<\/span><\/li>\n
- \u201cinclude in _all field\u201d checkbox: Leave this checked since the use case requirement is to search multiple fields.\u00a0<\/span><\/li>\n
- \u201cinclude term vectors\u201d checkbox: Leave this checked, too, during development and testing of our index to allow highlighting of results.\u00a0<\/span><\/li>\n
- docvalues checkbox: Uncheck this setting.\u00a0 This setting stores the field values in the index which provides support for Search Facets, and for the sorting of search results based on field values, neither of which we need in this use case.\u00a0<\/span><\/li>\n
- Click OK.<\/span><\/li>\n<\/ol>\n<\/li>\n
- Mouse over the row with the hotel type mapping, click the + button, and then click \u201c<\/span>insert child mapping<\/b>\u201d.\u00a0 This will allow the array of review sub-documents to be included in the index.\u00a0 Enter the property name \u201creviews\u201d, leave \u201cinherit\u201d selected in the analyzer drop-down, check \u201conly index specified fields\u201d, and click OK.\u00a0<\/span><\/li>\n
- Mouse over the row with the reviews child mapping, click the + button, and then click \u201c<\/span>insert child field<\/b>\u201d.\u00a0 This will allow the author field from the array of review sub-documents to be included in the index.\u00a0 Specify the following:\u00a0<\/span>\n
- \n
- field: Enter the name of the field to be indexed, \u201cauthor\u201d.<\/span><\/li>\n
- type: Leave this set to text for the author field.<\/span><\/li>\n
- searchable as: Leave this the same as the field name for the current use case.\u00a0 It can be used to indicate an alternate field name.\u00a0<\/span><\/li>\n
- analyzer: As was done for the type mapping, <\/span>for this use case, <\/span>leave \u201cinherit\u201d selected so <\/span>that the type mapping inherits the default analyzer.<\/span><\/li>\n
- index checkbox: Leave this checked, so that the field is included in the index.\u00a0 Unchecking the box would explicitly remove the field from the index.<\/span><\/li>\n
- store checkbox: Check this setting to include the field content in the search results which permits highlighting of matched expressions in the results.\u00a0 This is useful for testing the index, but not recommended in Prod if highlighting isn\u2019t required since it increases index size.<\/span><\/li>\n
- \u201cinclude in _all field\u201d checkbox: Leave this checked since the use case requirement is to search multiple fields.\u00a0<\/span><\/li>\n
- \u201cinclude term vectors\u201d checkbox: Leave this checked, too, during development and testing of our index to allow highlighting of results.\u00a0\u00a0<\/span><\/li>\n
- docvalues checkbox: Uncheck this setting.\u00a0 This setting stores the field values in the index which provides support for Search Facets, and for the sorting of search results based on field values, neither of which we need in this use case.\u00a0<\/span><\/li>\n
- Click OK.<\/span><\/li>\n<\/ol>\n<\/li>\n
- Finally, uncheck the checkbox next to the \u201cdefault\u201d type mapping.\u00a0 If the default mapping is left enabled, all documents in the bucket are included in the index, regardless of whether the user actively specifies type mappings. Only the hotel documents are required, and they are included by the hotel type mapping added previously.\u00a0<\/span><\/li>\n<\/ol>\n<\/li>\n
- The default values suffice for the remaining collapsed panels (Analyzers, Custom Filters, Date\/Time Parsers, and Advanced).\u00a0<\/span><\/li>\n
- Index Replicas can be set to 1, 2 or 3, provided that the cluster is running the Search service on n+1 nodes. With a single node development environment, maintain the default value of 0.\u00a0<\/span><\/li>\n
- For Index Type, the default value of \u201cVersion 6.0 (Scorch)\u201d is appropriate for any newly created indexes. Scorch reduces the size of the index on disk and provides enhanced MongoDB in operator performance for indexing and mutation-handling.<\/span><\/li>\n
- Index Partitions can be left to the default value of 6.\u00a0<\/span><\/li>\n
- At this point, the create index page should look like <\/span>the last frame captured in<\/span> Figure 5.\u00a0 Click \u201cCreate Index\u201d to complete the process.\u00a0<\/span><\/li>\n<\/ol>\n
\u00a0<\/span><\/p>\nFigure 5 – Creating FTS index with multiple arrays<\/span><\/i><\/p>\n
<\/p>\n
Note: See Appendix for the JSON payload used to create this index through the REST API.<\/span><\/p>\n
<\/p>\n
Testing queries against the index:\u00a0<\/span><\/p>\n
- \n
- On the Full Text Search UI, wait for indexing progress to show 100%, then click on the index name \u201chotel_mult_arrays\u201d.\u00a0<\/span><\/li>\n
- To search for any hotels with likes or reviews by someone named \u201cOzella\u201d, in the \u201csearch this index\u2026\u201d text box, enter \u201cOzella\u201d and click Search.\u00a0 Field-scoping of the search is not required because both indexed fields are included in the default field \u201c<\/span>_all\u201d.<\/span><\/li>\n
- The results are shown (similar to Figure 6) with the key of each matching document and highlighted matching fields.\u00a0 The document IDs returned are the same as those from our earlier N1QL query.\u00a0\u00a0<\/span><\/li>\n<\/ol>\n
<\/p>\nFigure 6 – Index \u201chotel_mult_arrays\u201d search results for \u201cOzella\u201d<\/span><\/i><\/p>\n
<\/p>\n
This is a single index on 2 array keys, which, as mentioned earlier, is something you could never do in a b-tree based index.\u00a0 So now let\u2019s take advantage of this FTS index in a N1QL query by using the SEARCH() function. Our query could look like this:\u00a0<\/span><\/p>\n
SELECT name, address, city, country,\r\n phone, public_likes, reviews\r\nFROM `travel-sample` AS t USE INDEX(hotel_mult_arrays USING FTS)\r\nWHERE t.type=\"hotel\"\r\nAND SEARCH(t, {\"query\": {\"match\":\"Ozella\"}}, {\"index\":\"hotel_mult_arrays\"});<\/pre>\n<\/p>\n
A few things to note about the query:\u00a0<\/span><\/p>\n
- \n
- The USE INDEX…USING FTS clause specifies that the FTS index should be used rather than a GSI index, so this query doesn\u2019t use the index service. (<\/span>Documentation<\/span><\/a>)<\/span><\/li>\n
- Because our FTS index uses a custom type mapping, the query needs to have the matching type specified in the WHERE clause (t.type=”hotel”).\u00a0\u00a0<\/span><\/li>\n
- The FTS index name is specified in the \u201cindex\u201d field in the SEARCH() function as a hint, but that is optional since the USE INDEX clause takes precedence over a hint provided in the \u201cindex\u201d field.\u00a0 (<\/span>Documentation<\/span><\/a>)\u00a0\u00a0<\/span><\/li>\n<\/ul>\n
Using the FTS index we created, our N1QL query runs successfully and returns 5 results (hotel_5081, hotel_26020, hotel_10025, hotel_20425, hotel_25327) and the following execution plan:\u00a0<\/span><\/p>\n
<\/p>\nFigure 7 – Execution plan using multiple indexes (FTS)<\/span><\/i><\/p>\n
Same plan in JSON:<\/span><\/p>\n
{\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"18.8\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"Authorize\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 3,\r\n \"execTime\": \"32.1\u00b5s\",\r\n \"servTime\": \"3.421ms\"\r\n },\r\n \"privileges\": {\r\n \"List\": [\r\n {\r\n \"Target\": \"default:travel-sample\",\r\n \"Priv\": 7\r\n }\r\n ]\r\n },\r\n \"~child\": {\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"122.8\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"IndexFtsSearch\",\r\n \"#stats\": {\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 23,\r\n \"execTime\": \"239.3\u00b5s\",\r\n \"kernTime\": \"84.5\u00b5s\",\r\n \"servTime\": \"3.9146ms\"\r\n },\r\n \"as\": \"t\",\r\n \"index\": \"hotel_mult_arrays\",\r\n \"index_id\": \"7a28a8346fad6118\",\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"search_info\": {\r\n \"field\": \"\\\"\\\"\",\r\n \"options\": \"{\\\"index\\\": \\\"hotel_mult_arrays\\\"}\",\r\n \"outname\": \"out\",\r\n \"query\": \"{\\\"query\\\": {\\\"match\\\": \\\"Ozella\\\"}}\"\r\n },\r\n \"using\": \"fts\",\r\n \"#time_normal\": \"00:00.004\",\r\n \"#time_absolute\": 0.0041539\r\n },\r\n {\r\n \"#operator\": \"Fetch\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 25,\r\n \"execTime\": \"334.8\u00b5s\",\r\n \"kernTime\": \"4.4328ms\",\r\n \"servTime\": \"1.5272ms\"\r\n },\r\n \"as\": \"t\",\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.001862\r\n },\r\n {\r\n \"#operator\": \"Parallel\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"21.1\u00b5s\"\r\n },\r\n \"copies\": 2,\r\n \"~child\": {\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 2,\r\n \"execTime\": \"49.1\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"Filter\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 26,\r\n \"execTime\": \"6.8953ms\",\r\n \"kernTime\": \"14.8149ms\"\r\n },\r\n \"condition\": \"(((`t`.`type`) = \\\"hotel\\\") and search(`t`, {\\\"query\\\": {\\\"match\\\": \\\"Ozella\\\"}}, {\\\"index\\\": \\\"hotel_mult_arrays\\\"}))\",\r\n \"#time_normal\": \"00:00.006\",\r\n \"#time_absolute\": 0.0068953\r\n },\r\n {\r\n \"#operator\": \"InitialProject\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 25,\r\n \"execTime\": \"2.3597ms\",\r\n \"kernTime\": \"20.7458ms\"\r\n },\r\n \"result_terms\": [\r\n {\r\n \"expr\": \"(`t`.`name`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`address`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`city`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`country`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`phone`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`public_likes`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`reviews`)\"\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.002\",\r\n \"#time_absolute\": 0.0023597\r\n },\r\n {\r\n \"#operator\": \"FinalProject\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 17,\r\n \"execTime\": \"300\u00b5s\",\r\n \"kernTime\": \"375.9\u00b5s\"\r\n },\r\n \"#time_normal\": \"00:00\",\r\n \"#time_absolute\": 0\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000491\r\n },\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000211\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0001228\r\n },\r\n \"#time_normal\": \"00:00.003\",\r\n \"#time_absolute\": 0.0034531\r\n },\r\n {\r\n \"#operator\": \"Stream\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 13,\r\n \"execTime\": \"1.3409ms\",\r\n \"kernTime\": \"14.8586ms\"\r\n },\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.0013409\r\n }\r\n ],\r\n \"~versions\": [\r\n \"2.0.0-N1QL\",\r\n \"6.5.0-4960-enterprise\"\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000188\r\n}\r\n<\/pre>\n<\/p>\n
In the single node cluster being used for these examples, the query elapsed time is around 20 milliseconds to return the same 5 documents.\u00a0 As you can see in the plan, there is an IndexFtsSearch operator but there are no IndexScan3, DistinctScan, UnionScan, or IntersectScan operators.\u00a0 The overall query is much more efficient without these expensive GSI operators. The IndexFtsSearch operator sends the 5 matching documents from the FTS index to the Fetch operator which gets only those 5 documents.\u00a0 The fetch is much more efficient here than in the previous query since it\u2019s only fetching 5 vs 904 documents, and this can also be observed in the comparison of overall elapsed times (and the servTime for the fetch operators: 170ms in query 1 and 1.5ms in query 2) between the queries.\u00a0\u00a0<\/span><\/p>\n
<\/p>\n
Conclusion<\/span><\/h2>\n
With GSI you can mix and match multiple array indexes in a single query, but with FTS you can mix and match multiple arrays in a single FTS index (and with FTS there is no leading key problem as in GSI regarding the order of the fields in the index).\u00a0 As we\u2019ve shown in this simple example of querying 2 arrays in the hotel documents, utilizing the new SEARCH() function in N1QL can result in simpler and more performant array queries. The same concept could be applied to queries utilizing several arrays, which would have even more favorable results over N1QL queries utilizing multiple GSI array indexes.\u00a0 This approach uses fewer system resources and provides higher throughput, which results in an increase in overall system efficiency.\u00a0\u00a0<\/span><\/p>\n
This is just one example of the benefits of the integration between N1QL and FTS, and other benefits are documented in the blog posts in the references section below.\u00a0<\/span><\/p>\n
<\/p>\n
References<\/span><\/h2>\n
- \n
- Couchbase Search Resources: <\/span>https:\/\/www.couchbase.com\/products\/full-text-search<\/span><\/a><\/li>\n
- Couchbase FTS Documentation: <\/span>https:\/\/docs.couchbase.com\/server\/current\/fts\/full-text-intro.html<\/span><\/a><\/li>\n
- Couchbase N1QL Search Documentation: <\/span>https:\/\/docs.couchbase.com\/server\/current\/n1ql\/n1ql-language-reference\/searchfun.html<\/span><\/a><\/li>\n
- Couchbase FTS Blog Posts: <\/span>https:\/\/www.couchbase.com\/blog\/category\/full-text-search\/<\/span><\/a><\/li>\n
- Couchbase FTS Online Training: <\/span>https:\/\/learn.couchbase.com\/store\/509465-cb121-intro-to-couchbase-full-text-search-fts<\/span><\/a><\/li>\n<\/ul>\n
<\/p>\n
Appendix<\/span><\/h2>\n
Index Definition JSON: hotel_mult_arrays<\/span><\/h3>\n
<\/p>\n
{\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"18.8\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"Authorize\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 3,\r\n \"execTime\": \"32.1\u00b5s\",\r\n \"servTime\": \"3.421ms\"\r\n },\r\n \"privileges\": {\r\n \"List\": [\r\n {\r\n \"Target\": \"default:travel-sample\",\r\n \"Priv\": 7\r\n }\r\n ]\r\n },\r\n \"~child\": {\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"122.8\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"IndexFtsSearch\",\r\n \"#stats\": {\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 23,\r\n \"execTime\": \"239.3\u00b5s\",\r\n \"kernTime\": \"84.5\u00b5s\",\r\n \"servTime\": \"3.9146ms\"\r\n },\r\n \"as\": \"t\",\r\n \"index\": \"hotel_mult_arrays\",\r\n \"index_id\": \"7a28a8346fad6118\",\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"search_info\": {\r\n \"field\": \"\\\"\\\"\",\r\n \"options\": \"{\\\"index\\\": \\\"hotel_mult_arrays\\\"}\",\r\n \"outname\": \"out\",\r\n \"query\": \"{\\\"query\\\": {\\\"match\\\": \\\"Ozella\\\"}}\"\r\n },\r\n \"using\": \"fts\",\r\n \"#time_normal\": \"00:00.004\",\r\n \"#time_absolute\": 0.0041539\r\n },\r\n {\r\n \"#operator\": \"Fetch\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 25,\r\n \"execTime\": \"334.8\u00b5s\",\r\n \"kernTime\": \"4.4328ms\",\r\n \"servTime\": \"1.5272ms\"\r\n },\r\n \"as\": \"t\",\r\n \"keyspace\": \"travel-sample\",\r\n \"namespace\": \"default\",\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.001862\r\n },\r\n {\r\n \"#operator\": \"Parallel\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 1,\r\n \"execTime\": \"21.1\u00b5s\"\r\n },\r\n \"copies\": 2,\r\n \"~child\": {\r\n \"#operator\": \"Sequence\",\r\n \"#stats\": {\r\n \"#phaseSwitches\": 2,\r\n \"execTime\": \"49.1\u00b5s\"\r\n },\r\n \"~children\": [\r\n {\r\n \"#operator\": \"Filter\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 26,\r\n \"execTime\": \"6.8953ms\",\r\n \"kernTime\": \"14.8149ms\"\r\n },\r\n \"condition\": \"(((`t`.`type`) = \\\"hotel\\\") and search(`t`, {\\\"query\\\": {\\\"match\\\": \\\"Ozella\\\"}}, {\\\"index\\\": \\\"hotel_mult_arrays\\\"}))\",\r\n \"#time_normal\": \"00:00.006\",\r\n \"#time_absolute\": 0.0068953\r\n },\r\n {\r\n \"#operator\": \"InitialProject\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 25,\r\n \"execTime\": \"2.3597ms\",\r\n \"kernTime\": \"20.7458ms\"\r\n },\r\n \"result_terms\": [\r\n {\r\n \"expr\": \"(`t`.`name`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`address`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`city`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`country`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`phone`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`public_likes`)\"\r\n },\r\n {\r\n \"expr\": \"(`t`.`reviews`)\"\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.002\",\r\n \"#time_absolute\": 0.0023597\r\n },\r\n {\r\n \"#operator\": \"FinalProject\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 17,\r\n \"execTime\": \"300\u00b5s\",\r\n \"kernTime\": \"375.9\u00b5s\"\r\n },\r\n \"#time_normal\": \"00:00\",\r\n \"#time_absolute\": 0\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000491\r\n },\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000211\r\n }\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0001228\r\n },\r\n \"#time_normal\": \"00:00.003\",\r\n \"#time_absolute\": 0.0034531\r\n },\r\n {\r\n \"#operator\": \"Stream\",\r\n \"#stats\": {\r\n \"#itemsIn\": 5,\r\n \"#itemsOut\": 5,\r\n \"#phaseSwitches\": 13,\r\n \"execTime\": \"1.3409ms\",\r\n \"kernTime\": \"14.8586ms\"\r\n },\r\n \"#time_normal\": \"00:00.001\",\r\n \"#time_absolute\": 0.0013409\r\n }\r\n ],\r\n \"~versions\": [\r\n \"2.0.0-N1QL\",\r\n \"6.5.0-4960-enterprise\"\r\n ],\r\n \"#time_normal\": \"00:00.000\",\r\n \"#time_absolute\": 0.0000188\r\n}\r\n<\/pre>\n<\/p>\n","protected":false},"excerpt":{"rendered":"
Introduction Couchbase Full Text Search (FTS) is a great fit for indexing multiple arrays and executing queries with multiple filter predicates in arrays.\u00a0 In this article, I\u2019ll demonstrate the advantages of using FTS over GSI (Global Secondary Index) for array […]<\/p>\n","protected":false},"author":16362,"featured_media":8371,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[1815,2165,2453,1812],"tags":[1505],"ppma_author":[9100],"class_list":["post-8362","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-best-practices-and-tutorials","category-full-text-search","category-global-secondary-index","category-n1ql-query","tag-index"],"yoast_head":"\n
MongoDB in Operator Performance: Querying Multiple Arrays<\/title>\n<meta name=\"description\" content=\"Learn the advantage of using FTS over GSI for array indexing with an example that requires querying multiple arrays with MongoDB in operator performance.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"FTS and N1QL: Better MongoDB in Operator Performance Querying Multiple Arrays\" \/>\n<meta property=\"og:description\" content=\"Learn the advantage of using FTS over GSI for array indexing with an example that requires querying multiple arrays with MongoDB in operator performance.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/\" \/>\n<meta property=\"og:site_name\" content=\"The Couchbase Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-03-27T01:08:28+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-06-14T03:59:06+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/03\/BKane-FTS-Array-scrabble.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1279\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Brian Kane, Solutions Engineer, Couchbase\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Brian Kane, Solutions Engineer, Couchbase\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"16 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/\"},\"author\":{\"name\":\"Brian Kane, Solutions Engineer, Couchbase\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#\\\/schema\\\/person\\\/69569e5446e8ee83e32f8e5ed50a250e\"},\"headline\":\"FTS and N1QL: Better MongoDB in Operator Performance Querying Multiple Arrays\",\"datePublished\":\"2020-03-27T01:08:28+00:00\",\"dateModified\":\"2025-06-14T03:59:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/\"},\"wordCount\":2277,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/1\\\/2020\\\/03\\\/BKane-FTS-Array-scrabble.jpg\",\"keywords\":[\"Index\"],\"articleSection\":[\"Best Practices and Tutorials\",\"Full-Text Search\",\"Global Secondary Index\",\"SQL++ \\\/ N1QL Query\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/\",\"url\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/\",\"name\":\"MongoDB in Operator Performance: Querying Multiple Arrays\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/1\\\/2020\\\/03\\\/BKane-FTS-Array-scrabble.jpg\",\"datePublished\":\"2020-03-27T01:08:28+00:00\",\"dateModified\":\"2025-06-14T03:59:06+00:00\",\"description\":\"Learn the advantage of using FTS over GSI for array indexing with an example that requires querying multiple arrays with MongoDB in operator performance.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/1\\\/2020\\\/03\\\/BKane-FTS-Array-scrabble.jpg\",\"contentUrl\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/1\\\/2020\\\/03\\\/BKane-FTS-Array-scrabble.jpg\",\"width\":1920,\"height\":1279},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"FTS and N1QL: Better MongoDB in Operator Performance Querying Multiple Arrays\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/\",\"name\":\"The Couchbase Blog\",\"description\":\"Couchbase, the NoSQL Database\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#organization\",\"name\":\"The Couchbase Blog\",\"url\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/wp-content\\\/uploads\\\/2023\\\/04\\\/admin-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/wp-content\\\/uploads\\\/2023\\\/04\\\/admin-logo.png\",\"width\":218,\"height\":34,\"caption\":\"The Couchbase Blog\"},\"image\":{\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/#\\\/schema\\\/person\\\/69569e5446e8ee83e32f8e5ed50a250e\",\"name\":\"Brian Kane, Solutions Engineer, Couchbase\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=gd6ceb7493ed6dce23e12c34a4d31643e\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=g\",\"caption\":\"Brian Kane, Solutions Engineer, Couchbase\"},\"description\":\"Brian Kane is a Solutions Engineer at Couchbase and has been working in application development and with database technologies since 1996. He is based in the Houston, Texas area.\",\"url\":\"https:\\\/\\\/www.couchbase.com\\\/blog\\\/author\\\/brian-kanecouchbase-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"MongoDB in Operator Performance: Querying Multiple Arrays","description":"Learn the advantage of using FTS over GSI for array indexing with an example that requires querying multiple arrays with MongoDB in operator performance.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/","og_locale":"en_US","og_type":"article","og_title":"FTS and N1QL: Better MongoDB in Operator Performance Querying Multiple Arrays","og_description":"Learn the advantage of using FTS over GSI for array indexing with an example that requires querying multiple arrays with MongoDB in operator performance.","og_url":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/","og_site_name":"The Couchbase Blog","article_published_time":"2020-03-27T01:08:28+00:00","article_modified_time":"2025-06-14T03:59:06+00:00","og_image":[{"width":1920,"height":1279,"url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/03\/BKane-FTS-Array-scrabble.jpg","type":"image\/jpeg"}],"author":"Brian Kane, Solutions Engineer, Couchbase","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Brian Kane, Solutions Engineer, Couchbase","Est. reading time":"16 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#article","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/"},"author":{"name":"Brian Kane, Solutions Engineer, Couchbase","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/69569e5446e8ee83e32f8e5ed50a250e"},"headline":"FTS and N1QL: Better MongoDB in Operator Performance Querying Multiple Arrays","datePublished":"2020-03-27T01:08:28+00:00","dateModified":"2025-06-14T03:59:06+00:00","mainEntityOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/"},"wordCount":2277,"commentCount":0,"publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/03\/BKane-FTS-Array-scrabble.jpg","keywords":["Index"],"articleSection":["Best Practices and Tutorials","Full-Text Search","Global Secondary Index","SQL++ \/ N1QL Query"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/","url":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/","name":"MongoDB in Operator Performance: Querying Multiple Arrays","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#primaryimage"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/03\/BKane-FTS-Array-scrabble.jpg","datePublished":"2020-03-27T01:08:28+00:00","dateModified":"2025-06-14T03:59:06+00:00","description":"Learn the advantage of using FTS over GSI for array indexing with an example that requires querying multiple arrays with MongoDB in operator performance.","breadcrumb":{"@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#primaryimage","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/03\/BKane-FTS-Array-scrabble.jpg","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/03\/BKane-FTS-Array-scrabble.jpg","width":1920,"height":1279},{"@type":"BreadcrumbList","@id":"https:\/\/www.couchbase.com\/blog\/fts-and-n1ql-improving-performance-when-querying-multiple-arrays\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.couchbase.com\/blog\/"},{"@type":"ListItem","position":2,"name":"FTS and N1QL: Better MongoDB in Operator Performance Querying Multiple Arrays"}]},{"@type":"WebSite","@id":"https:\/\/www.couchbase.com\/blog\/#website","url":"https:\/\/www.couchbase.com\/blog\/","name":"The Couchbase Blog","description":"Couchbase, the NoSQL Database","publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.couchbase.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.couchbase.com\/blog\/#organization","name":"The Couchbase Blog","url":"https:\/\/www.couchbase.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png","width":218,"height":34,"caption":"The Couchbase Blog"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/69569e5446e8ee83e32f8e5ed50a250e","name":"Brian Kane, Solutions Engineer, Couchbase","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=gd6ceb7493ed6dce23e12c34a4d31643e","url":"https:\/\/secure.gravatar.com\/avatar\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=g","caption":"Brian Kane, Solutions Engineer, Couchbase"},"description":"Brian Kane is a Solutions Engineer at Couchbase and has been working in application development and with database technologies since 1996. He is based in the Houston, Texas area.","url":"https:\/\/www.couchbase.com\/blog\/author\/brian-kanecouchbase-com\/"}]}},"acf":[],"authors":[{"term_id":9100,"user_id":16362,"is_guest":0,"slug":"brian-kanecouchbase-com","display_name":"Brian Kane, Solutions Engineer, Couchbase","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/366c828464bd7bc90174aca4473a71c317f4578e440915acf85a1de7e170c1e0?s=96&d=mm&r=g","0":null,"1":"","2":"","3":"","4":"","5":"","6":"","7":"","8":""}],"_links":{"self":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/8362","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/users\/16362"}],"replies":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/comments?post=8362"}],"version-history":[{"count":0,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/8362\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media\/8371"}],"wp:attachment":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media?parent=8362"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/categories?post=8362"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/tags?post=8362"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=8362"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} - Couchbase FTS Documentation: <\/span>https:\/\/docs.couchbase.com\/server\/current\/fts\/full-text-intro.html<\/span><\/a><\/li>\n
- Because our FTS index uses a custom type mapping, the query needs to have the matching type specified in the WHERE clause (t.type=”hotel”).\u00a0\u00a0<\/span><\/li>\n
- To search for any hotels with likes or reviews by someone named \u201cOzella\u201d, in the \u201csearch this index\u2026\u201d text box, enter \u201cOzella\u201d and click Search.\u00a0 Field-scoping of the search is not required because both indexed fields are included in the default field \u201c<\/span>_all\u201d.<\/span><\/li>\n
- type: Leave this set to text for the author field.<\/span><\/li>\n
- type: Leave this set to text for the public_likes array.<\/span><\/li>\n
- A list of available analyzers can be accessed by means of the pull-down menu to the right of the type name field.\u00a0 For this use case, leave \u201cinherit\u201d selected so that the type mapping inherits the default analyzer from the index.<\/span><\/li>\n
- Specify an index name, e.g. \u201chotel_mult_arrays\u201d, and select the travel-sample bucket.\u00a0<\/span><\/li>\n
- An id field that identifies the document<\/span><\/li>\n