Laravel PostgreSQL环境下JSON列数组对象中price字段大于/小于值的查询方法

嘿，这个场景我之前在项目里碰到过，PostgreSQL配合Laravel处理JSON数组列的查询其实思路很清晰，我给你分情况拆解下：

你的properties列存的是JSON数组，每个顶层对象都有price字段（注意它是字符串类型，必须转成数值才能做大小比较）。在PostgreSQL里可以用json_array_elements把数组展开成单行对象，再过滤条件：

在Laravel里可以这么写：

$targetPrice = 21000;

// 查找顶层price大于指定值的记录
$records = YourModel::whereRaw(
    'EXISTS (
        SELECT 1 FROM json_array_elements(properties::json) AS elem
        WHERE (elem->>\'price\')::numeric > ?
    )',
    [$targetPrice]
)->get();

关键细节解释： #

• json_array_elements(properties::json)：把properties列的JSON数组拆分成独立的对象行
• elem->>'price'：取出price字段的字符串值（->>会返回文本，而->返回JSON类型）
• ::numeric：把字符串转成数值类型，避免字符串比较的逻辑错误（比如"10000"和"999"字符串比较会出问题）
• EXISTS：确保只要数组里有一个对象满足条件，就返回这条模型记录

如果要找小于指定值的记录，把>换成<即可。

如果需要同时检查顶层对象和嵌套的childs数组里的price，可以嵌套一层json_array_elements来处理子数组：

$targetPrice = 22000;

// 查找顶层或childs中price大于指定值的记录
$records = YourModel::whereRaw(
    'EXISTS (
        SELECT 1 FROM json_array_elements(properties::json) AS elem
        WHERE (elem->>\'price\')::numeric > ?
        OR EXISTS (
            SELECT 1 FROM json_array_elements(elem->\'childs\') AS child
            WHERE (child->>\'price\')::numeric > ?
        )
    )',
    [$targetPrice, $targetPrice]
)->get();

这里用了两层EXISTS：第一层检查顶层price，第二层展开childs数组检查子元素的price，只要任意一个满足条件就返回该记录。

如果你的properties列目前是json类型，建议改成jsonb类型——它比json更适合查询，支持索引，性能更好。你可以通过迁移修改：

// 生成迁移文件后修改内容
Schema::table('your_table_name', function (Blueprint $table) {
    $table->jsonb('properties')->change();
});

之后查询时把json_array_elements换成jsonb_array_elements就行。如果数据量较大，还可以给properties列加GIN索引来加速查询：

CREATE INDEX idx_properties_price ON your_table_name USING gin (properties jsonb_path_ops);

为了避免重复写SQL，你可以在模型里定义查询作用域：

class YourModel extends Model
{
    // 查找price大于指定值的记录（含顶层和childs）
    public function scopeWherePriceGreaterThan($query, $price)
    {
        return $query->whereRaw(
            'EXISTS (
                SELECT 1 FROM json_array_elements(properties::json) AS elem
                WHERE (elem->>\'price\')::numeric > ?
                OR EXISTS (
                    SELECT 1 FROM json_array_elements(elem->\'childs\') AS child
                    WHERE (child->>\'price\')::numeric > ?
                )
            )',
            [$price, $price]
        );
    }

    // 查找price小于指定值的记录（含顶层和childs）
    public function scopeWherePriceLessThan($query, $price)
    {
        return $query->whereRaw(
            'EXISTS (
                SELECT 1 FROM json_array_elements(properties::json) AS elem
                WHERE (elem->>\'price\')::numeric < ?
                OR EXISTS (
                    SELECT 1 FROM json_array_elements(elem->\'childs\') AS child
                    WHERE (child->>\'price\')::numeric < ?
                )
            )',
            [$price, $price]
        );
    }
}

之后使用起来就非常简洁：

// 找价格大于22000的记录
$highPriceRecords = YourModel::wherePriceGreaterThan(22000)->get();

// 找价格小于21000的记录
$lowPriceRecords = YourModel::wherePriceLessThan(21000)->get();

内容的提问来源于stack exchange，提问作者Paulo José Oliveira Rosa