Querying "jsonable" fields in your models

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) {
    // ...

Then you can query the inner content:

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

You can also use the whereJsonContains query method:

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

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]'])

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

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