CODEWITHSUNDEEP
indexingcouchbase
Naveen s
Naveen s

Reputation: 23

Create Couchbase Index on Array field

Hi how to create index on array field my sample doc is 

   { 
"name": [     {
      "family": "Smith",
      "given": [
        "Kam"
      ],
      "prefix": [
        "Mrs."
      ],
      "use": "official"
    },
    {
      "family": "Johns",
      "given": [
        "Kam"
      ],
      "use": "maiden"
    }
  ]
}

I want to write a search query (like) on family and given fields ...How to create a index and suggest query ..Im new to couchbase

Upvotes: 1

Views: 1091

Answers (1)

Johan Larson
Johan Larson

Reputation: 1890

This query that selects the customers with family name "Smith" and given name "Kam":

select * from customer
where any n in name satisfies n.family = 'Smith' and 
          any fn in n.given satisfies fn = 'Kam' end end

Note the use of a nested ANY clause because of the use of a nested array in the data.

You can then create an index on the family name like this:

CREATE INDEX customer_name ON customer
   ( DISTINCT ARRAY n.family FOR n IN name END)

The index gets used without any hints. You can see that it is being used by adding EXPLAIN to the beginning of the query. That will get you a query plan in JSON that includes an index scan operator.

You can learn more about array indexing here: https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

Upvotes: 2

Related Questions