Get DB table from json array with Laravel

Question

I have the following record in my database where I have to get all records containing any of the records.

Table record contains: $record_ids = ["123","234","111"]

Trying to search for it like the following with Laravel:

$records = DB::table('records_table')
  ->where('id', $id)
  ->whereRaw('JSON_CONTAINS(record_id, ?)', $record_ids)
  ->pluck('records');

I've also tried with similar solutions like:

->whereIn('record_id', $record_ids)

I'm using Laravel 5.5 and Mysql.

Update: It works when I 'manually' add it like the following:

->whereIn('record_id', ["123","234","111"])

But when fetching the array from the database it doesn't work

You want to find the rows where the record_id column contains all the ids from $record_ids? — Jonas Staudenmeir
– Jonas Staudenmeir, Commented Sep 28, 2018 at 20:09

Marcin Nabiałek · Accepted Answer · 2018-09-28 20:04:02Z

1

I think you want to use here:

->whereIn('record_id', json_decode($json, true));

answered Sep 28, 2018 at 20:04

Marcin Nabiałek

112k43 gold badges271 silver badges299 bronze badges

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

1 Comment

Kevin Over a year ago

Yes! That worked! I tried something similar and the issue was in the code above so the result didn't return anything.

Jonas Staudenmeir · Accepted Answer · 2018-09-28 20:44:28Z

1

The types have to match (integer vs. string) and the search value has to be JSON encoded:

$search = json_encode(array_map('intval', $record_ids));

$records = DB::table('records_table')
    ->where('id', $id)
    ->whereRaw('JSON_CONTAINS(record_id, ?)', $search)
    ->pluck('records');

In Laravel 5.6, you can use whereJsonContains():

->whereJsonContains('record_id', array_map('intval', $record_ids));

edited Sep 28, 2018 at 20:44

answered Sep 28, 2018 at 20:23

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

Collectives™ on Stack Overflow

Get DB table from json array with Laravel

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related