JSON Is Case Sensitive but You Don't Have to Be
In this article, we'll discuss dealing with data case sensitivity.
Join the DZone community and get the full member experience.Join For Free
A RoSe by any other case would smell as sweet. William Shakespeare
You must have learned capitalization rules in your grammar school, but the real-world search is not so sensitive to capitalization. Charles de Gaulle uses lower case for the middle "de," Tony La Russa uses upper case for "La," and there may be etymological reasons for it, but it's unlikely for your customer service agent to remember.
Databases have a variety of sensitivities. SQL, by default, is case insensitive to identifiers and keywords, but case sensitive to data. JSON is case sensitive to both field names and data. So is N1QL. JSON can have the following. N1QL will select-join-project each field and value as a distinct field and value.
In this article, we'll discuss dealing with data case sensitivity. Your field references are still case sensitive. If you use the wrong case for the field name, N1QL assumes this is a missing field and assigns MISSING value to that field.
Let's consider a simple predicate in N1QL to lookup all permutations of cases.
This requires seven different lookups into the index. "John" requires more index lookups and "Fitzerald" requires even more. There is a standard way to do this. Simply create an index by lowering the case of the field and the literal.
This lookup can be made faster by creating the index with the right expression.
Ensure that your query is picking up the right index and pushes the predicate to the index scan. And that's the idea. Queries that have predicates pushed to the index scan run much faster than the queries that won't. This is true for predicates and true aggregate pushdown as well.
Case insensitivity in a composite index scenario.
Case insensitivity in Array functions
We follow the same principle as before. Create an expression to lower case the values first before you process them via these functions.
Now, what you really want is to filter based on a value within the string.
This is probably the worst predicate in SQL in terms of performance.
Now, what index would you create for this? ADVISE comes in handy.
As usual, verify your explain.
If you'd like to UNNEST and have a simple WHERE clause, use this query. Always verify your explain to ensure the predicates are pushed to index scan.
TOKENS() function makes it simple to get the lower case by taking that option as an argument. See the article More Than LIKE: Efficient JSON Searching With N1QL for details and examples
How could we optimize this? Index Advisor to the rescue. Again.
Explain to confirm the plan:
Bringing in the Big Guns: Full-Text Search
As you've realized, this is a text processing and querying problem. FTS can scan, store, search text in various ways. Case insensitive search is one of them. Let's see the plan for a simple search query.
This is NOT the plan you want...This is using a primary scan!
After creating the text index on the bucket customer, things are much better:
The default standard analyzer lowers all the tokens and therefore you'll find all the "joe"s: JOE, joe, Joe, JOe, etc. You can define a custom analyzer and provide specific instructions to lowercase the tokens. Here's an example.
Here's how you add it in the UI. See fine blog 8 Ways to Customize Couchbase Full-Text Search Indexes for details on various ways to customize the FTS index.
1. Couchbase Documentation: https://docs.couchbase.com/server/6.5/introduction/intro.html
3. Couchbase FTS: https://docs.couchbase.com/server/6.5/fts/full-text-intro.html
Published at DZone with permission of Keshav Murthy, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.