How to select where json like array of keys and values via Eloquent ORM?

Question

I have MySQL table with json field:

Schema::create('meta', function (Blueprint $table) {
       $table->json('details');
});

and array of key and values:

$filter = ['key' => 'val', 'id' => 'val'];

How to select rows where json containing keys and values from array?

This don't works:

Meta::where('details', $filter)->get();

Meta::whereJsonContains('details', $filter)->get();

Any ideas?

Maybe this post could give you some idea:medium.com/@thakuramit3/… — user10128333
– user10128333, Commented Jun 17, 2019 at 10:54

4n70wa · Accepted Answer · 2019-06-17 10:52:09Z

2

Workaround:

Meta::where(function($query) use($filter) {
    foreach($filter as $k => $v) {
        $query->orWhere("details->{$k}", $v);
    }
})->get();

But it will not work with deep nested arrays.

answered Jun 17, 2019 at 10:52

4n70wa

3574 silver badges19 bronze badges

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

Comments

Linus Juhlin · Accepted Answer · 2019-06-17 10:46:24Z

1

You need to use JSON Where Clauses

To query a JSON column, use the -> operator:

$users = DB::table('users')
                ->where('options->language', 'en')
                ->get();

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();

answered Jun 17, 2019 at 10:46

Linus Juhlin

1,28312 silver badges31 bronze badges

5 Comments

4n70wa Over a year ago

I know this, the problem is different. Re-read the question again.

Linus Juhlin Over a year ago

Is it deeply nested?

4n70wa Over a year ago

I don't know the names of the keys and their depth, I just have an array with some keys and values.

Linus Juhlin Over a year ago

I think your own answer is the best one for this solution. As for the deep nesting issue you can make it into a recursive function. Otherwise I think re-thinking your database design is the best option. :)

4n70wa Over a year ago

It is strange that such a possibility is not built into the Eloquent. For non-json-wheres such is out of the box.

Collectives™ on Stack Overflow

How to select where json like array of keys and values via Eloquent ORM?

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related