search

pocketbase / pocketbase

Open Source realtime backend in 1 file
https://pocketbase.io
MIT License
40.74k stars 1.91k forks source link

Check if array/json field contains the request query value #4028

Closed MobinAskari closed 10 months ago

MobinAskari commented 10 months ago

I have a number_ids json field in one of my collections which stores an array of numbers. I want to check whether the number which was sent with the request query is available in one of the number_ids field of my records or not. I tried so many combinations and read the docs but wasn't able to achieve my desired result.

number_ids ~ @request.query.number_id kinda works but it doesn't compare them strictly; Meaning that if the received number id is 5, and the array contains a number id of 512368, it will still pass. I specifically need this for list searches, where I want to return the record where the number_ids array contains the number_id passed with the request query.

ganigeorgiev commented 10 months ago

IN like expression over json fields are not supported at the moment.

There are plans to add :each modifier support for the next release that should cover this case too - https://github.com/orgs/pocketbase/projects/2/views/1?pane=issue&itemId=38816514.

An alternative to storing your values as json field array is to have a separate collection referenced with a relation field. This allows more control over who can access and edit the ids since they will have their own API rules. You'll be also able to define a SQLite index for the field that will hold the id and eventually helps with the performance in large datasets.

MobinAskari commented 10 months ago

IN like expression over json fields are not supported at the moment.

There are plans to add :each modifier support for the next release that should cover this case too - https://github.com/orgs/pocketbase/projects/2/views/1?pane=issue&itemId=38816514.

An alternative to storing your values as json field array is to have a separate collection referenced with a relation field. This allows more control over who can access and edit the ids since they will have their own API rules. You'll be also able to define a SQLite index for the field that will hold the id and eventually helps with the performance in large datasets.

Thank you 🙏. I originally wanted to post this on the Q&A but made a silly mistake of writing it here. Apologies 🙏