Is there a way to loop through nested objects in json column in table and check for condition with query builder in Laravel?

Question

I have events table with json column guests. The data of guests field looks like this:

{"lines":
   [{"men":1,
     "women":1,
     "children":1,
     "language_id":291,
     "language_name":"test",
   }],
   [{"men":1,
     "women":1,
     "children":1,
     "language_id":292,
     "language_name":"test2",
   }],
 "totalMen":2,
 "totalWomen":2,
 "totalChildren":2,
 "total":6,}

I need to get events in controller with specific language_id. But field guests can have multiple arrays inside lines, so i need somehow to check through all of the lines for specific language_id. Now i am doing it like this:

$language = Language::findOrFail($lid);
$events = Event::get();
foreach($events as $event) {
    foreach($event['guests']['lines'] as $line){
        if($line['language_id'] === $language->id) {
            // do smth
            break;
        }
    }
}

Is there is a way to get events with query builder in laravel with my condition, so i don't need to get all events first, and then filter them?

Muhammed Shihabudeen Labba A · Accepted Answer · 2019-08-22 13:43:10Z

1

You can use JSON_EXTRACT mysql function.

Please follow this article for more details. https://database.guide/json_extract-return-data-from-a-json-document-in-mysql/

I am writing in core PHP. Please customize accordingly.

$sql =  "SELECT JSON_EXTRACT(guests, '$.lines') as guest FROM `t1`";

$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        $json_array = json_decode($row['guest']);
        foreach($json_array as $line) {
            echo "<br>";
            print_r($line->men);
        }
    }
}

edited Aug 22, 2019 at 13:43

answered Aug 22, 2019 at 12:42

Muhammed Shihabudeen Labba A

6398 silver badges21 bronze badges

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

3 Comments

Тим Ким Over a year ago

I understood how to extract exact value, but if i have multiple lines, how to loop through them and check for condition

Muhammed Shihabudeen Labba A Over a year ago

@ТимКим, added the core PHP file in the answer. Please check

Тим Ким Over a year ago

That's far from what i asked, but thanks for advices, i ll try to think of using properly JSON_EXTRACT

jureispro · Accepted Answer · 2019-08-22 12:46:13Z

0

You can use built-in Laravel JSON Where Clauses. I think that with some playing and some debugging you can achieve what you need to do.

Here is the example using your JSON schema:

$events = DB::table('events')
                ->where('guests->lines->language_id',  $language->id)
                ->get();

answered Aug 22, 2019 at 12:46

jureispro

1,4026 gold badges25 silver badges47 bronze badges

1 Comment

Тим Ким Over a year ago

But there are multiple lines, like in the description above, so i need to loop through lines first

Collectives™ on Stack Overflow

Is there a way to loop through nested objects in json column in table and check for condition with query builder in Laravel?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related