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

Question

因此，我希望通过比较某个ID和data列来查询Laravel中的data表。这是data列的样子：

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

现在，我需要选择ID等于3的所有通知。

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

但这会引发以下错误：

SQLSTATE42000:语法错误或访问冲突: 1064您的SQL语法出现了错误；请检查与MariaDB服务器版本对应的手册，以获得使用“$.id”=？在第1行(SQL: select * from notifications data-‘$.id“= 3)

我快疯了，有人能帮我吗？

Answer 1

你的查询没有什么问题。这是你的环境。

问题

Laravel的MySqlGrammar将字段名中的field->key符号(在Laravel侧)转换为field->'$.key'-style提取(在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不支持提取算子作为JSON_EXTRACT()函数的别名。但是，相同的查询针对普通的MySQL 5.7服务器。

假设这个test表：

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

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

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

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

解决方案

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

替换MariaDB

如果您正在使用MySQL，那么在您的开发env中将MariaDB替换为MySQL。在一台由自制软件管理的macOS机器上，它就像：

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

你的数据将保持原样。

重写查询

但是，如果在生产中使用MariaDB，则需要重写查询以将JSON_EXTRACT()函数用作埃利亚斯已经提到。如您所见，您需要更详细地使用Laravel。

上述查询如下：

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

Answer 2

我为json支持制作了Laravel MariaDB驱动程序。到这里来：ybr-nx/laravel-mariadb

Answer 3

问题是只有MySQL支持->运算符，那么查询将在MariaDB上失败，但是：

MariaDB和MySQL都支持JSON_EXTRACT函数，该函数解析JSON并从中获取值。

您可以通过这样的查询来解决这个问题：

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

要使用Laravel查询生成器执行此操作，您需要使用DB::raw方法：

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

请试试看，如果出了问题，请告诉我。

注： JSON_EXTRACT()只在MySQL >= 5.7或MariaDB >= 10.2.3上可用

感谢 @Sepehr，我不知道->是MySQL操作符，也不知道JSON类型存在。:-)