CODEWITHSUNDEEP
laravel
ira
ira

Reputation: 624

Laravel 8 json colum where with array of object

I would like to perform a query in a table where the column is of json type and it contains array of objects, but only if a certain condition is met

This is my current code

$initial_results = DB::table('toys')->select('id','name')->where(['name' => 'sammy', 'email' => 'whateveremail']);
if($sk ==='yes') {
      $results = $initial_results->>whereRaw('JSON_CONTAINS(`info`,\'{"sku":"B07V3SSLN11"}\')')
       >whereRaw('JSON_CONTAINS(`info`,\'{"asin":"DTI-LALF3-EA18"}\')')
      ->get();
} else {
    $results = $initial_results->get();
}

But I always get 0 result if the condition is met. In database, the info I want to query indeed exist. What is the proper way to query a json column which contains array of objects? See my example data

  [
   {
    "sku": "DTI-LALF3-EA18",
    "adId": 244077676726655,
    "asin": "B07V3SSLN11",
    "cost": 0,
},
{
    "sku": "DTI-LALF3-EA18",
    "adId": 242968940906362,
    "asin": "B07V3SSLN11",
    "cost": 10,
   .........
   ................

I even tried

    $initial_results = DB::table('toys')->select('id','name')->where(['name' => 'sammy', 'email' => 'whateveremail'])->->whereIn(DB::raw("JSON_EXTRACT(info, '$[*].asin')"),['B07V3SSLN11']);

Thanks in advance

Upvotes: 1

Views: 1840

Answers (1)

Rwd
Rwd

Reputation: 35180

You can query JSON columns by using the -> operator in your clause:

->where('info->asin', 'DTI-LALF3-EA18')

JSON Where Clauses Docs

Upvotes: 1

Related Questions