Laravel对接PostgreSQL时jsonb查询失效求助（text字段）

问题背景 #

使用Laravel 9.0（PHP 8.1）对接PostgreSQL 15.2，表中response字段类型为text，需要筛选其中存储的有效JSON数据。

尝试执行以下Eloquent代码：

$response = \App\Models\BinResponse::query()
        ->selectRaw('id, response')
        ->whereRaw("response::jsonb IS NOT NULL")
        ->get();

dd($response);

生成的原生SQL为：

select id, response from "bin_responses" where response::jsonb IS NOT NULL

直接执行该原生SQL可正常获取数据，但通过Laravel Eloquent执行时抛出错误：

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type json DETAIL: The input string ended unexpectedly. CONTEXT: JSON data, line 1:

解决方案 #

1. 使用jsonb_valid函数筛选有效JSON #

PostgreSQL提供jsonb_valid函数，可直接判断文本是否为有效JSON格式，避免转换时抛出错误：

$response = \App\Models\BinResponse::query()
    ->selectRaw('id, response')
    ->whereRaw('jsonb_valid(response)')
    ->get();

对应的原生SQL：

select id, response from "bin_responses" where jsonb_valid(response)

该方法会直接过滤所有无效JSON的记录，不会触发类型转换错误。

2. 结合模型字段自动解析 #

若需要Laravel自动将response字段解析为PHP数组/对象，可在BinResponse模型中添加字段转换配置，同时配合jsonb_valid筛选确保数据有效：

class BinResponse extends \Illuminate\Database\Eloquent\Model
{
    protected $casts = [
        'response' => 'json', // 自动将TEXT字段解析为JSON格式
    ];
}

查询代码保持使用jsonb_valid筛选即可，返回的response会自动转为PHP数组，无需手动解析。

3. 使用try_cast处理无效JSON（PostgreSQL 12+） #

若希望将无效JSON转换为NULL而非直接报错，可使用PostgreSQL 12及以上支持的try_cast函数，再筛选非NULL记录：

$response = \App\Models\BinResponse::query()
    ->selectRaw('id, try_cast(response as jsonb) as response')
    ->whereRaw('try_cast(response as jsonb) IS NOT NULL')
    ->get();

该方法会将无效JSON的response字段转为NULL，并过滤掉这些记录，避免转换错误。

问题原因 #

原代码中response::jsonb IS NOT NULL的写法会让PostgreSQL尝试对所有行的response字段执行类型转换，只要存在一行无效JSON（比如空字符串、格式错误的JSON），就会抛出转换错误。而jsonb_valid或try_cast可规避这种逐行转换的报错问题。

内容的提问来源于stack exchange，提问作者Moshiur