Laravel Eloquent - Working with an array of JSON objects

Question

I have an array of JSON objects within my database (as depicted by the image below)

How do I search for a specific value within it? Say I want the record where the "ends_at" field is equal to "2018-11-24 08:00:00"

I've tried using

->where('column->starts_at', '2018-11-24 08:00:00')

or even tried

->whereJsonContains('column->starts_at', '2018-11-24 08:00:00').

Within the model I've cast the column to an array - am I missing something, or can this not be done?

May be you can try this : DB::table('your_table')->whereRaw('json_contains(column, \'["value"]\')')->get(); — Steve
– Steve, Commented Oct 26, 2018 at 12:08

Jonas Staudenmeir · Accepted Answer · 2018-10-27 00:49:15Z

10

Use this:

->whereJsonContains('column', ['starts_at' => '2018-11-24 08:00:00'])

answered Oct 27, 2018 at 0:49

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

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

6 Comments

Jonas Staudenmeir Over a year ago

@HelenChe Do you mean ->where('column->starts_at', '2018-11-24 08:00:00')?

Helen Che Over a year ago

Sorry I meant <, > and such. I took a quick look at the Builder code but I'm not seeing any options for this...

Jonas Staudenmeir Over a year ago

Do you mean ->where('foo->bar', '<', 123)?

Helen Che Over a year ago

Yes... but that is only if foo->bar is simply key-value from my understanding. Apologies if I'm not making sense but let's try the original question posted as example: how would I get results of the JSON array where availability > 9?

Helen Che Over a year ago

I've tried ->whereJsonContains('column', ['availability', '>', 9]) will not work unfortunately.

|

Collectives™ on Stack Overflow

Laravel Eloquent - Working with an array of JSON objects

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related