Laravel's query builder JSON selector `field->key` causes syntax error

Question

So, I want to query the notifications table in Laravel by comparing a certain ID with the data column. This is how data column looks like:

{
    "Message": "some message",
    "id": 3
}

Now, I need to select all the notifications that have an ID that is equal to 3. Here is how I was trying to do it:

DB::table('notifications')->where('data->id', '3')->get();

But this throws the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$."id"' = ?' at line 1 (SQL: select * from notifications where data->'$."id"' = 3)

I am losing my mind here, can anyone help me out?

Answer 1

There's nothing wrong with your query. It's your environment.

Problem

Laravel's MySqlGrammar translates the field->key notation in field names (on Laravel side) into field->'$.key'-style extractions (on MySQL side):

/**
 * Wrap the given JSON selector.
 *
 * @param  string  $value
 * @return string
 */
protected function wrapJsonSelector($value)
{
    $path = explode('->', $value);

    $field = $this->wrapValue(array_shift($path));

    $path = collect($path)->map(function ($part) {
        return '"'.$part.'"';
    })->implode('.');

    // Here:
    return sprintf('%s->\'$.%s\'', $field, $path);
}

I just confirmed that MariaDB does not support the -> extraction operator as an alias to the JSON_EXTRACT() function. However, the same query works against a vanilla MySQL 5.7 server.

Assuming this test table:

╔════╤══════════════════╗
║ id │ payload          ║
╟────┼──────────────────╢
║  1 │ {"a": 1, "b": 2} ║
╚════╧══════════════════╝

A query that uses the -> extraction operator:

SELECT payload->"$.b" FROM test;

fails against MariaDB 10.2.8 while it yields a correct 2 against a MySQL 5.7.19 server.

Solutions

The right solution depends on what you're using on production.

Replace MariaDB

If you're using MySQL, replace MariaDB with MySQL in your development env. On a macOS machine managed by homebrew, it'd be as easy as:

brew services stop mysql
brew uninstall mariadb
brew install mysql
brew services start mysql

your data will remain intact.

Rewrite your queries

However, if you're using MariaDB in production, you need to rewrite your queries to use JSON_EXTRACT() function as Elias already mentioned. As you can see you need to be much more verbose with the Laravel API.

The above query would be:

SELECT JSON_EXTRACT(payload, "$.b") FROM test;

Answer 2

The problem is that only MySQL supports the -> operator, then the query will fail on MariaDB, but:

Both MariaDB and MySQL supports the JSON_EXTRACT function, that parses the JSON and get a value from it.

You can solve it by doing a query like this:

SELECT * FROM notifications WHERE JSON_EXTRACT(`notifications.data`, "$.id") = 5

To do it with Laravel's Query Builder, you'll need to use DB::raw method:

DB::table('notifications')->where(DB::raw('JSON_EXTRACT(`notifications.data`, "$.id")'), '=', 3);

Please try it and tell me if go wrong.

Note: The JSON_EXTRACT() is only available on MySQL >= 5.7 or MariaDB >= 10.2.3

Thanks to @Sepehr, I didn't know that the -> was an MySQL Operator, neither that JSON type existed. :-)

Answer 3

I made Laravel MariaDB driver for json support. Get it here: ybr-nx/laravel-mariadb

Answer 4

The issue is with the ->, i think you have a var called $data with some collection in it. If that so then the correct way is:

DB::table('notifications')->where($data->id, '3')->get();

Or if you have a Model related to notifications table then:

Notification::where($data->id, '3')->get();

If the Model is called Notification (following Eloquent convention)

But if you tried to find all ID's equals 3 then just:

DB::table('notifications')->where('id', '3')->get();