query builder search a json column

Are you sure , in your database column names are camel case?

What is 'Colors->"$.id"' ?

Save yourself a lot of headache and use a one to many relationship on your colors.

You can try multiple ways, one could be $products->where('colors->id', $color->ColorID) -> make sure you use the right cases, like Saikishore suggested. There is also whereJsonContains which you would use like this: $products->whereJsonContains('colors', $color->ColorID)

yes @saikishore the column from the product is "Colors"

@tray2 if can`t find any solution i will :)

@nezar i have tried both and not working:

 $products = $products->where('Colors->id', 2);

$products = $products->whereJsonContains('Colors', 2);

Could be because the Colors colum is varchar??? But the data inside is json

What about json_decode'ing the Colors column before comparing? Like:

class Products extends Model
{
    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'Colors' => 'array',
    ];
}

From the docs: https://laravel.com/docs/5.8/eloquent-mutators#array-and-json-casting

The array cast type is particularly useful when working with columns that are stored as serialized JSON. For example, if your database has a JSON or TEXT field type that contains serialized JSON, adding the array cast to that attribute will automatically deserialize the attribute to a PHP array when you access it on your Eloquent model:

Then you should have more success with your query as the json data will have automatically been transformed into a regular PHP array:

// notice the **whereIn**

$products = Product::query();
if(request()->has('color')){
    $color = ProductColor::where('Slug', request('color'))->first();
    $products = $products->whereIn('Colors', $color->ColorID);
}

@mstrauss i have tried casting the Colors in the model Products and

$products = $products->whereIn('Colors', [2, 3]);

but nothing.

Can you please show us your Colors model? Also if you die and dump $color = ProductColor::where('Slug', request('color'))->first(); what does that look like?

@mstrauss sure

class ProductColor extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */

    protected $table = 'products_colors';
    protected $primaryKey = 'ColorID';
}

this seems to work:

$products = $products->whereRaw('JSON_CONTAINS(Colors, \'{"id": "2"}\')');

if using "2" not 2 will work!

Sorry, I meant to ask for the Product model, not the ProductColor.

@mstrauss the product model

class Product extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */

    protected $table = 'products';
    protected $primaryKey = 'ProductID';

    protected $casts = [
        'Colors' => 'array',
    ];
}

Seems like you got the answer, good job! Now you just have to transform the $color variable request yielded from the user input to match the regex in the query. Good luck with the project! Just beware that this particular query may be a bit unwieldy to maintain.