SQL Query on Nested JSON

SQL JSON

I have come across a scenario in an interview where I have to write an optimized SQL query for a nested JSON structure and insert it into the database. I thought i might share it with you.

the problem can be a bit tricky as you have done it in an optimized way.
Here is what my nested json structure look like:

users: { id:1, name: ‘ashar’, comments: [{ id:0212, replies: [{id:001,message:’hello’},{id:003,message:’ttx’},{id:004,message:’abc’}] }, { id:2, name: ‘saleem’, comments: [{ id:02115, replies: [{id:00211,message:’he2llo’},{id:0223,message:’2ttx’},{id:024,message:’2abc’}] }] }

There are 3 different tables in the database
users
comments
replies

and I have to insert that nested data into JSON
here is what your SQL query looks like:

I am using laravel query builder syntax here but you will get the idea.:)

To insert a JSON structure like this into three different tables (users, comments, and replies) using the Laravel database query builder, you can use a loop to iterate over the users array and insert the data for each user. Here is an example of how you could do this:

DB::transaction(function () {
    foreach ($json_data['users'] as $user) {
        // Insert the data into the users table
        DB::table('users')->insert([
            'id' => $user['id'],
            'name' => $user['name'],
        ]);

        foreach ($user['comments'] as $comment) {
            // Insert the data into the comments table
            DB::table('comments')->insert([
                'user_id' => $user['id'],
                'id' => $comment['id'],
            ]);

            // Insert the data into the replies table
            DB::table('replies')->insert(
                DB::table('comments')
                    ->join('replies', 'replies.comment_id', '=', 'comments.id')
                    ->select('comments.id', 'replies.id', 'replies.message')
                    ->where('comments.id', $comment['id'])
                    ->get()
                    ->toArray()
            );
        }
    }
});

This code uses the transaction() method to start a transaction, which allows you to insert the data into multiple tables in a single operation. It iterates over the users array using a foreach loop, and uses the insert() method to insert the data into the users table for each user. Then, it iterates over the comments array for each user using another foreach loop, and uses the insert() method to insert the data into the comments table and the replies table for each comment. It uses the join() method to join the data from the comments table with the data from the replies array, and the select() method to specify the columns to be inserted. Finally, it converts the result set to an array using the toArray() method and passes it to the insert() method.

I hope this helps! Let me know if you have any questions.