I have table name details having contains field which stores array in postgres.
id | contents
1 | ["1", "2", "5"]
2 | ["4", "2", "10"]
3 | ["3", "5"]
If I query 5 it should return 2 records i.e. id 1 and 3
I actually want to implement in laravel.
Details::whereRaw("contents ->> $id")->get(); // this doesn't work :p
How to raw query ?
If you were using Mysql (with JSON field) you would have used JSON_CONTAINS() or rather JSON_SEARCH() but because you are using postgresql then Perhaps you can find a solution from this page. The one that looks quite close to an answer is this:
"SELECT * FROM details WHERE 5 = ANY (contents)"; //5 is the one of values in the array
There are also other ways such as using ALL(), WHERE IN etc. Unfortunately I have never used posgres with Laravel so I cannot really say how to construct the query, but perhaps simply doing the following would suffice:
Details::whereRaw("5 ANY (contents)")->get();
PS: This answer is open for update if you got this working.
SQLSTATE[42809]: Wrong object type: 7 ERROR: op ANY/ALL (array) requires array on right side I've defined jsonYou're looking for the @> ("contains") operator:
https://www.postgresql.org/docs/current/functions-array.html
Details::whereRaw("contents ARRAY[?]::int[]', $id)
@> is indexable if you use a GIN index https://www.postgresql.org/docs/current/indexes-types.html
