Laravel 8 json colum where with array of object

Question

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

Does it matter that your example data has a space between the colon and the quotation mark, but your SQL does not? Because it's a string search, so it has to be exact, no? — Delmontee
– Delmontee, Commented Oct 12, 2021 at 8:02
What about if you remove the { and }..... JSON_CONTAINS('info',\'"sku":"B07V3SSLN11"\') — Delmontee
– Delmontee, Commented Oct 12, 2021 at 8:07
Does this answer your question: stackoverflow.com/questions/53007520/…? — Rwd
– Rwd, Commented Oct 12, 2021 at 8:48
@Rwd. No. Maybe the reason its not working because I have multiple where conditions ->whereJsonContains('info', ['asin' => 'B07V3SSLN11']) ->whereJsonContains('info', ['sku' => 'DT-LALF3-EA18']) — ira
– ira, Commented Oct 12, 2021 at 9:00

Rwd · Accepted Answer · 2021-10-12 08:09:39Z

1

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

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

JSON Where Clauses Docs

answered Oct 12, 2021 at 8:09

Rwd

35.3k7 gold badges71 silver badges85 bronze badges

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

1 Comment

ira Over a year ago

It doenst work for a json colum whose data is array of objects

Collectives™ on Stack Overflow

Laravel 8 json colum where with array of object

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related