Laravel的查询生成器JSON选择器`field-> key`导致语法错误 [英] Laravel's query builder JSON selector `field->key` causes syntax error

问题描述

因此，我想通过将某个ID与data列进行比较来查询Laravel中的notifications表.这是data列的样子:

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
}

现在，我需要选择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();

但这会引发以下错误:

SQLSTATE [42000]:语法错误或访问冲突:1064您有一个 您的SQL语法错误；检查与您的手册相对应的手册 MariaDB服务器版本，用于在'>'$."id"'=附近使用的正确语法 '在第1行(SQL:从notifications中选择*，其中 data->'$."id"'= 3)

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?

推荐答案

您的查询没有问题.这是你的环境.

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

Laravel的 MySqlGrammar 将字段名称中的field->key表示法(在Laravel方面)转换为field->'$.key'样式的提取(在MySQL方面):

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);
}

我刚刚确认MariaDB不支持 ->提取运算符作为

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.

假设此test表:

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

使用->提取运算符的查询:

A query that uses the -> extraction operator:

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

针对MariaDB 10.2.8失败，同时针对MySQL 5.7.19服务器生成正确的2.

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

正确的解决方案取决于您在生产中使用的产品.

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

如果您使用的是MySQL，请在您的开发环境中将MariaDB替换为MySQL.在由自制程序管理的macOS机器上，它很容易做到:

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

您的数据将保持不变.

但是，如果在生产环境中使用MariaDB，则需要重写查询以将JSON_EXTRACT()函数用作 Elias .如您所见，您需要使用Laravel API更加详细.

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.

上面的查询将是:

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

这篇关于Laravel的查询生成器JSON选择器`field-> key`导致语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！