Querying Pipe separated fields

SQL++
, ,
1

Our use case requires, controllers and category to be queried for any of the values that can be selected and below is the structure of controllers in CB.

{
“controllers”:“str1|str2|str3|str4|str5|str6|str7|str8”,
“category”:“cat1|cat2|cat3|cat4”
}

For ex:

  1. I wan’t to view docs where controller is str1 AND ( cat1 OR cat2)
  2. I want to view docs where controller can be str1 OR str2 OR str3 OR …str1300

For now, the approach that we have followed uses below query:

 select * from default as t where (ANY v IN SPLIT(t.controllers, "|") SATISFIES LOWER(v) = "str1" END OR ANY v IN SPLIT(t.controllers, "|") SATISFIES LOWER(v) = "str2" END ) AND (ANY v IN SPLIT(t.category, "|") SATISFIES LOWER(v) = "cat1" )

And in this way, multiple OR conditions are getting appended for same field, AND condition for different fields.

Indexes -

CREATE INDEX idx_controllers ON default((distinct (array lower(v) for v in split(controllers, “|”) end))) WHERE (doc_type = “item”)

CREATE INDEX idx_category ON default((distinct (array lower(v) for v in split(category, “|”) end))) WHERE (doc_type = “item”)

Questions -

  1. Is this the right way/optimized way to do this ? Or we have better alternatives that we can look at ?
  2. Also, can’t index controllers and category in the same index bcz of Multiple expressions with ALL are found. Only one array expression is supported per index.

Note :

  1. Data is pipe separated
2

You can use IN clause, OR clause inside SATISIFIES.

LOWER(v) IN [ “str1”, “str2”,“str3” ]

Only single array index key is supported.