Accessing Laravel Notification JSON data from Database

When you work with Laravel database notification, You will need to store the notification related information in the data column in the notifications table.

Consider this code from Notification class which stores the information like type, id, action, url to notifications table.

public function toArray(object $notifiable): array
    {
        return [
            'type' => 'student',
            'id' => $this->student->id,
            'action' => 'updated',
            'url' => route('students.show', $this->student),
        ];
    }

this data will be stored in the notifications table as follows, in SQL Server Database

In Laravel we could simply use arrow operator to get the data field

$user->notifications()
    ->where('data->type', 'student')
    ->where('data->id', $student->id)
    ->get();

But when you query in any sql client, how will you access this JSON data?

Apparantly there is a specific way to use this JSON data, as per the Microsoft Documentation

JSON_VALUE(jsonCol, '$.info.address.Town')

where, jsonCol is the column name, in our case it is the data column

'$.info.address.Town' is the json keys that we stored in the data. In our case it can be 'type' for example. '$.type'

So the combined query to fetch all rows with data->type='student' and data->id=33 will be:

SELECT * FROM [notifications] where JSON_VALUE(data, '$.type')='student' and JSON_VALUE(data, '$.id')=33;

Hope this helps someone! :-)

• #JSON
• #laravel
• #database
• #sqlserver
• #notification