How to select using the document key id?

Guys

How do I use N1QL to query my data using the document ID?

select * from myBucket where key?? = ‘234567’

What do I use instead of “key??” to reference the correct data element?

Thanks

1 Like

Hello:

select * from myBucket USE KEYS '234567'

1 Like

Is there a Wildcard usage for KEYS keyword like:

 SELECT meta(gdRules).id AS documentId, gdRules.*,
gPolicy AS goodPolicy,
    dPolicy AS deadPolicy
   
      FROM `Prices` AS gdRules 
      JOIN `Prices` AS gPolicy ON KEYS ["XXX_" ||gdRules.Code || "_ABC_%"]
      JOIN `Prices` AS dPolicy ON KEYS ["XXX_" ||gdRules.Code || "_EDF_%" ] 
      ...//Where clause to filter gdrules, gPolicy, dPolicy 

Note: Prices is a bucket name

Sorry for the missed formatting

Hi @nbupadhya, unfortunately, you cannot do wildcards in USE KEYS. N1QL provides equi-joins. We are looking to provide more flexibility here in future.
thanks
-cihan

Hello,

You can use wildcards on document id’s in the FROM clause as follows:

Please see the N1QL documentation for LIKE and REGEXP.

2 Likes

Thanks for the reply Cihan.

Thanks for the reply , ya this wildcard usage is good to know.

Is it more efficient to do a key based wild card search and then provide another value which has a secondary index on it?

I still can’t get this to work!

select *
from bucket b
where meta(b).id like ‘%anything%’
limit 1;

I am forever getting this error:
[
{
“code”: 4000,
“msg”: “No index available on keyspace bucket that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.”,
“query_from_user”: “select *\nfrom bucket b\nwhere meta(b).id like ‘%anything%’\nlimit 1;”
}
]

How can I query for all keys without any index?

If you need to filter out on document key only. You need primary index or secondary index on document key.
CREATE PRIMARY INDEX ON bucket;