Search in array within Json column with Laravel

Question

In my Laravel project, I have a products table with a json column calle data. In the json column I have the following structure:

{
  "storage" : [
    {
      "stowage" : 1,
      "quantity" : 100
    },
    {
      "stowage" : 5,
      "quantity" : 500
    }
  ]
}

I want to search products with stowages that have less than 500 items. I know that I can do this:

SELECT * FROM products p WHERE 
JSON_EXTRACT( p.`data`, '$.storage[0].quantity') < 500;

First I would like to convert that to Laravel eloquent, something like:

$products = Product::where( 'data->storage[0].quantity, '<', 500 )->get();

I tried that one and does not work, but I would also want to search in all the stowages:

$products = Product::where( 'data->storage->>quantity', '<', 500 )->get();

Is it possible?

I'm not sure of the syntax for searching all storage objects' .quantity values, but Laravel does have support for JSON where clauses: laravel.com/docs/8.x/queries#json-where-clauses; might find something useful there 🙂 — Tim Lewis
– Tim Lewis, Commented Jan 20, 2022 at 16:04
I went through Laravel's JSON documentation, and it does not help with my problem. I used some of that information for other parts of my project, but the one I'm asking about here is not in any way covered by that documentation. But thanks anyway. — Superveci
– Superveci, Commented Jan 20, 2022 at 16:47
No problem! I was hopeful, but alas. Hopefully someone else will be able to help. Cheers! — Tim Lewis
– Tim Lewis, Commented Jan 20, 2022 at 16:59

PHP Geek · Accepted Answer · 2022-01-21 04:45:03Z

1

We have whereJsonContains method for eloquent to search from json data in column..

answered Jan 21, 2022 at 4:45

PHP Geek

4,0632 gold badges19 silver badges32 bronze badges

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

1 Answer 1