Creating an index with a hash

Hi,

I am trying to create an index with a hash on a particular value.

The doc looks something like

{
“name”: “Tom”,
“profession”: “developer”,
“address” : {
“postalCd”: “10789”,
“country”: “DE”,
“address”: “Augsburger Str. 41,”,
“city”: “Berlin”,
},
“tokens”:[
“1234”,
“5678”
]
}

If I want to create the hash on a particular token(I don’t know which token it is an element that is not an object just a list of Strings) would it be like

CREATE INDEX idx_Token ON employee(name,profession,address, tokens) PARTITION BY hash(tokens)

or would the BY hash(tokens.<somethingHere>) need to be the format?

Thanks

HASH expression must be immutable . Expression can be ANY data type (including array or object). It considers expression as scalar and calculate HASH. You can use META().id.

Follow up question, how can you query on the token that matches for example:
(The below doesnt work, but the idea is wanting something like this)

SELECT *
FROM employee
WHERE token IN [“1234”]

You need to use Array Indexing and if you want Partition Index for Divide and Concur you can use META().id as partition key. The following gives overview and help understand how partition works. https://blog.couchbase.com/couchbase-gsi-index-partitioning/

CREATE INDEX `idx_Token` ON `employee` ( DISTINCT tokens ) PARTITION BY hash( META().id);
SELECT *
FROM employee
WHERE ANY  token IN tokens  SATISFIES  token IN ["1234"] END;

Thanks!

Last question on this for now in the query above we get all the data as wanted, now if we wanted the data to only be that reflected of by that of the token value how would we do that.

For example the query:

SELECT *
FROM employee
WHERE ANY token IN tokens SATISFIES token IN [“1234”] END;

gives us:

{
“name”: “Tom”,
“profession”: “developer”,
“address” : {
“postalCd”: “10789”,
“country”: “DE”,
“address”: “Augsburger Str. 41,”,
“city”: “Berlin”,
},
“tokens”:[
“1234”,
“5678”
]
}

but if we want the tokens field to look like that as below how would we modify the query.

{
“name”: “Tom”,
“profession”: “developer”,
“address” : {
“postalCd”: “10789”,
“country”: “DE”,
“address”: “Augsburger Str. 41,”,
“city”: “Berlin”,
},
“tokens”:[
“1234”
]
}

Use ARRAY construct reconstruct tokens.

SELECT e.*, ARRAY v FOR v IN e.tokens WHEN v IN ["1234"] END AS tokens
FROM employee AS e
WHERE ANY  token IN e.tokens  SATISFIES  token IN ["1234"] END;

https://blog.couchbase.com/working-json-arrays-n1ql/

1 Like