Find a specific data in all documents in bucket

SQL++
1

Hi,

I want to know if there is a way to perform a N1QL search across entire bucket (all documents) to locate a specific data. Example: I want to find all documents containing the number 123456.
The number may appear under different keys.

BTW, I’m sure it will be extremely slow and not efficient , but I don’t mind it.
Thanks a lot,
Daniel.

2 
SELECT META(d).id
FROM default AS d
WHERE  ANY v WITHIN d  SATISFIES v = 123456 END;

OR use FTS Search service

3

Hi,

Thank you for the quick response.
When I run it I’m getting error code 404 and the following error:

[
{
“code”: 4000,
“msg”: “No index available on keyspace default 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 META(d).id\nFROM default AS d\nWHERE ANY v WITHIN d SATISFIES v = 14517862 END;”
}
]

4

You need to have index on the bucket. As you want search on whole bucket. You must have primary index

5

Thank you.
Any chance you can advise me on the index creating query?
Thank you!

6

You can try this. Index size might be too big because every field is indexed.

CREATE INDEX ix11 ON default(DISTINCT ARRAY v FOR v WITHIN self END);
 SELECT META(d).id
FROM default AS d
WHERE  ANY v WITHIN d  SATISFIES v = 123456 END;
7

Thanks a lot! it was exactly what I needed