I have a column in my table as follows:
{
1:{type: 0},
2:{type: 1}...
}
i have tried using using whereJsonContains('table.column->type', 0) but it's not working,
whereRaw("table->column @> '{\"type\": 0}'::jsonb"); is not working either, i guess because of the nested json structure. How can i make this work?
At root your JSON is not an array, but an object (notice the index which starts at 1 and brackets which enclose the data). You can't perform whereJsonContains on objects. You should either convert your JSON root to array, or do a comprehensive lookup:
[
{type: 0},
{type: 1}...
]
I advise this code:
DB::table('table')->whereJsonContains('column->type', 0)->get();
DB::table('table')
->where('column->1->type', 0)
->orWhere('column->2->type', 1)
->get();
For further reference consult Laravel docs: https://laravel.com/docs/7.x/queries#json-where-clauses
After trying multiple suggestions i ended up using this code:
$model->whereRaw("(
SELECT
COUNT(elements.*) > 0
FROM
jsonb_each(table.colum) elements
WHERE
elements.value->>'type' = ?
)", [$validated_request['type']]);
Thanks for your help.
I have data like [{"value": "example.co"}] or [{"value": "example.co"}, {"value": "example.uk"}, {"value": "example.pk"}, ... ] in table column domains and above all mention method did not worked. I ended up using that code.
DB::table('table')->where("domains", '@>', '[{"value":"example.uk"}]')->get();
Or you can use that
$query = 'example.uk';
DB::table('table')->where("domains", '@>', '[' . json_encode(['value' => $query]) . ']')->get();
->whereJsonContains('your.column', ['type' => '0'])?