Querying "jsonable" fields in your models

Tips and Tricks
#1

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();
2 Likes
Query model by checkbox list data
#2

I have extended the rainlab blog post model with a featured field in the existing jsonable metadata field with the code:

$formWidget->addSecondaryTabFields([
                'metadata[featured]' => [
                    'tab' => 'rainlab.blog::lang.post.tab_manage',
                    'label' => 'Featured post',
                    'type' => 'switch',
                    'default' => false
                ],
            ]);

it is properly saved in the datbase.

but when I try to query the featured posts,

‘’'php
$items = Post::where(‘metadata->featured’, ‘1’)->get();


returns 0 blog posts when there is at least one post replying to the query condition.

Using mysql 8.x or mysql 9.x gives the same issue.

any idea why this is happening please ?
#3

Try this one

$users = DB::table('users')
    ->whereJsonContains('options->languages', 'en')
    ->get();
#4

not working either.

On my laptop with MySQL 8.x its not working but on my Mac Mini with MySQL 8.x it is working well.

#5

on mysql 8.0.41-0ubuntu0.20.04.1 for instance, it is not working either.