Laravel 5.4 query on a JSON field containing a JSON array

Question

Inside users table I have a json column named "agencies" that stores data as a simple array like this:

[
"0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12",
"f7c748d4-8718-441e-aa69-91b890ead5ed"
],

the above is valid json. When I try to select all users that contain 0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12 I get null

Is my query correct?

$users = User::whereRaw('JSON_CONTAINS(agencies->"$[*]", "0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12")')->get();

is the below correct way to do write JSON select query conbsidering how I store uuids as an array inside agencies column which is defined as json?

'JSON_CONTAINS(agencies->"$[*]", "0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12"

I got the idea for above select from reading: Making a Laravel 5.4 query on a JSON field containing a JSON array

but the solution in that post is different then what I am trying to do and my modification to it does not give me back any users, but instead I allways get null back.

DevK · Accepted Answer · 2018-06-04 23:44:35Z

0

I believe that's what you want:

$users = User::whereRaw('JSON_CONTAINS(agencies, ?)', ['0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12'])->get();

It will also soon be supported with the query builder. See PR

edited Jun 4, 2018 at 23:44

answered Jun 4, 2018 at 23:38

DevK

10k2 gold badges33 silver badges51 bronze badges

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

1 Comment

niko craft Over a year ago

that looked like it would be the right syntax, but I still got null back. Wonder why it fails.

Jonas Staudenmeir · Accepted Answer · 2018-06-27 01:21:58Z

0

MySQL expects a JSON string:

$users = User::whereRaw('JSON_CONTAINS(agencies, ?)',
    ['"0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12"'])->get();
$users = User::whereRaw('JSON_CONTAINS(agencies, ?)',
    [json_encode('0eb2edf0-50cb-44ff-a0a6-b2a104a9dc12')])->get();

answered Jun 27, 2018 at 1:21

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

Collectives™ on Stack Overflow

Laravel 5.4 query on a JSON field containing a JSON array

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related