JSON column query using laravel builder

Question

I have a json column in mysql and i am trying query that column using laravel bulider. That column has an array of json object and i want query a Value attribute in that json object.

use Illuminate\Database\Eloquent\Builder;

class SizeFilter
{
    public function filter(Builder $builder, $value): Builder{
    return $builder->whereJsonContains('sizes',[['Value' => $value]]);
}

and my json column structure is

 [{"SizeID":34,"Sku":null,"Value":"10","stock":2},{"SizeID":35,"Sku":null,"Value":"12","stock":0},{"SizeID":36,"Sku":null,"Value":"14","stock":0},{"SizeID":37,"Sku":null,"Value":"16","stock":0},{"SizeID":38,"Sku":null,"Value":"18","stock":0},{"SizeID":32,"Sku":null,"Value":"6","stock":0},{"SizeID":33,"Sku":null,"Value":"8","stock":1}]

Bill Karwin · Accepted Answer · 2021-12-12 15:53:17Z

1

What is $value? It must be a string. If it's an integer, the JSON search won't work.

I don't have Laravel, but I tested in the MySQL client:

mysql> set @j = '...'; -- your example JSON

mysql> select json_contains(@j, '{"Value":"10"}');
+-------------------------------------+
| json_contains(@j, '{"Value":"10"}') |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select json_contains(@j, '{"Value":10}');
+-----------------------------------+
| json_contains(@j, '{"Value":10}') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

So make sure it's a string:

return $builder->whereJsonContains('sizes',[['Value' => (string) $value]]);

answered Dec 12, 2021 at 15:53

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sahkashif Over a year ago

thank you for your suggestion. It worked! $value which I'm passing the filter function was an array and this is reason I was getting noting from that query

Collectives™ on Stack Overflow

JSON column query using laravel builder

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related