php - Laravel 的查询生成器 JSON 选择器 `field->key` 导致语法错误

Question

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

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

现在，我需要选择 ID 等于 3 的所有通知。这是我尝试执行此操作的方法:

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

但这会引发以下错误:

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)

我在这里失去了理智，任何人都可以帮助我吗？

Answer 1

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

问题

Laravel 的 MySqlGrammar将字段名称中的 field->key 符号(在 Laravel 端)转换为 field->'$.key' 样式的提取(在 MySQL 端):

/**
 * 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 不支持 -> extraction operator作为 JSON_EXTRACT() 的别名功能。然而，同样的查询适用于 vanilla MySQL 5.7 服务器。

假设这个测试表:

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

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

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

在 MariaDB 10.2.8 上失败，而它在 MySQL 5.7.19 服务器上生成正确的 2。

解决方案

正确的解决方案取决于您在生产中使用的是什么。

替换 MariaDB

如果您使用的是 MySQL，请在您的开发环境中将 MariaDB 替换为 MySQL。在由自制软件管理的 macOS 机器上，它就像:

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

您的数据将保持不变。

重写您的查询

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

上面的查询是:

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