Empty result: Laravel/PostgreSQL where Json array column contains one of these items

Question

In my Food model i have a column named countries which is a json field.

I have collected asian countries in one array and my plans is to get the records if the countries column has one of the country in the array, I did the below:

$asian = [
    "South Korea",
    "Pakistan",
    "Afghanistan",
    .
    .
    .
];

Food::whereJsonContains('countries', $asian)->get();

It returns empty collection, It seems like it looking for a record that contais all of the countries but i'm looking for a way to tell match records if "one of the countries" matched not all of them at once

rez · Accepted Answer · 2021-05-14 14:59:03Z

Yes I had the same issue with laravel 8. It returns empty array even though it's documented to be used like this:

$users = DB::table('users')
    ->whereJsonContains('options->languages', ['en', 'de'])
    ->get();

You can solve this issue like this:

Food::where(function ($query) use ($asian) {
    foreach ($asian as $country) {
        $query->orWhereJsonContains('countries', $country);
    }
})->get();

and don't forget to add countries field into $cast variable in your model. This will instruct Eloquent to deserialize it from JSON into a PHP array:

class Food extends Model 
{
    ...
   protected $casts = [
        'countries' => 'array'
    ];
    ...
}

Update

Although the above code solves the issue, I found that the whereJsonContains method works fine. I was inserting the data in a wrong way and I didn't get any error on that! Make sure you are inserting the data in a proper way as you wouldn't get an error if you try to insert to JSON data like this:

Food::factory()->create([
    'countries' => 'USA'
]);

I tested this on Laravel 8, IDK if this happens on older versions.

Your data should be like this:

{

    {
        ...
        'countries': ['USA', 'China']
        ...
    }
}

In this case this:

Food::whereJsonContains('countries', $asian)->get();

It would return Foods that their countries contained within $array.

Collectives™ on Stack Overflow

Empty result: Laravel/PostgreSQL where Json array column contains one of these items

1 Answer 1

Update

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

Comments

Your Answer

Sign up or log in

Post as a guest

Related