php - 我如何使用 laravel 在 SQLite、PostgreSQL 和 MSSQL 数据库中启用/使用 JSON_EXTRACT 方法？

Question

想要启用/使用/替代 JSON_EXTRACT 方法，它与使用 laravel 的 SQLite、PostgreSQL、MySQL 和 MSSQL 数据库中的 JSON_EXTRACT 一样工作。

以下查询在 MySQL 中运行，其中 table2。attribute_data 是 json 数据列。

    $query->select('table1.*');
    $query->where(function ($q) use ($locale) {
        $q->where(Db::raw('JSON_EXTRACT(`table2`.`attribute_data`, "$.title")'), '!=', "");
        $q->where('table2.locale', '=', $locale);
    });
    $query->join('table2', function ($join) use ($locale) {
        $join->on('table1.id', '=', 'table2.model_id');
    }); 

错误: SQLSTATE[HY000]:一般错误:1 没有这样的函数:JSON_EXTRACT

Answer 1

json 是数据库中的复杂数据结构。您不会找到一种方法适用于四个不同的数据库提供程序，因为它们都有自己处理 json 的专有方式。

Laravel 只为 MySQL 和 Postgres 使用实际的 json 数据类型。对于 SQLite 和 MSSQL，json 存储在 varchar 字段中。

Laravel 理解用于查询 MySQL (5.7+) 和 Postgres 中的 json 字段的“箭头”运算符 (->)。因此，对于 MySQL 和 Postgres，您的查询将如下所示:

$query->select('table1.*')
    ->join('table2', function ($join) {
        return $join->on('table1.id', '=', 'table2.model_id');
    })
    ->where(function ($q) use ($locale) {
        return $q
            ->where('table2.attribute_data->title', '!=', "")
            ->where('table2.locale', '=', $locale);
    });

但是，这在 SQLite 和 MSSQL 中可能会失败，因为它们不理解箭头运算符。

在不更改查询的情况下跨所有数据库提供程序进行这项工作的唯一方法是将 json 数据存储在所有数据库的 varchar 中，然后使用 LIKE 运算符查询 json 字符串。这将是低效的并且可能容易出错。否则，您需要根据您正在使用的数据库调整查询。