Querying "jsonable" fields in your models

daft · October 21, 2022, 11:34pm

Laravel supports JSON “where” clauses and other queries natively as per the documentation:

Keep in mind a database is needed that supports JSON columns, which is MySQL 5.7 and MariaDB 10.2 onwards. You may also need to use the json type in the database migration. For example:

Declare a column as JSON

Schema::create('my_table', function ($table) {
    $table->increments('id');
    $table->json('jsonable_field');
    // ...
});

Then you can query the inner content:

$users = Db::table('users')
    ->where('jsonable_field->dining->meal', 'salad')
    ->get();

You can also use the whereJsonContains query method:

$users = Db::table('users')
    ->whereJsonContains('jsonable_field->languages', 'en')
    ->get();

At the base layer, the query uses the JSON_EXTRACT function to perform an inspection, which can be used for more advanced queries.

Filter data like {“subkey”: [10]}

$users = Db::table('users')
    ->whereRaw('JSON_EXTRACT(array_of_values, ?) >= ?', ['$."subkey"[0]'])
    ->get();

Order by JSON value like {“value_name”: “abc”}

$users = Db::table('users')
    ->orderByRaw('JSON_EXTRACT(field_name, ?) ASC', ['$."value_name"'])
    ->get();