PostgreSQL CTE中如何处理可能为空的JSON（json_each函数场景）

问题根源 #

当filters变量为空时，'${filters}'会被替换成空字符串，而空字符串不是有效的JSON格式，导致json_each函数抛出pq: invalid input syntax for type json错误。另外PostgreSQL的CTE是预执行的——不管主查询的CASE分支是否用到，所有CTE都会在主查询运行前执行，所以必须保证CTE里的JSON操作始终合法。

解决方案 #

1. 修复JSON输入有效性

用nullif将空字符串转为NULL，再通过coalesce替换成合法的空JSON对象'{}'，确保json_each始终拿到有效的JSON输入：

json_each(coalesce(nullif('${filters}', ''), '{}'::json))

解释：nullif('${filters}', '')会把空字符串转为NULL，非空字符串保持原样；coalesce则把NULL替换成'{}'，彻底避免无效JSON输入。

2. 支持多过滤条件的完整查询示例

如果需要同时处理filter1、filter2等多个过滤条件，可以为每个过滤条件单独创建CTE，然后在主查询中灵活组合过滤逻辑：

with filter1 as (
    select json_array_elements_text(value) as filter_val
    from json_each(coalesce(nullif('${filters}', ''), '{}'::json)) 
    where key = 'filter1'
),
filter2 as (
    select json_array_elements_text(value) as filter_val
    from json_each(coalesce(nullif('${filters}', ''), '{}'::json)) 
    where key = 'filter2'
)
select 
    case 
        -- 检查是否有至少一个过滤条件存在且有值
        when exists (select * from filter1) or exists (select * from filter2)
        then sum(
            case 
                -- 根据业务需求调整AND/OR逻辑，不存在的过滤条件自动跳过
                when (field in (select filter_val from filter1) or not exists (select * from filter1))
                     and (other_field in (select filter_val from filter2) or not exists (select * from filter2))
                then 1 
                else 0 
            end
        )
        else count(*)
    end
from data;

3. 优化：用JSONB简化解析（PostgreSQL 9.4+）

如果你的PostgreSQL版本支持JSONB，可以一次性解析过滤条件，让查询更简洁高效：

with parsed_filters as (
    -- 将输入转为JSONB，空字符串自动转为空JSON对象
    select coalesce(nullif('${filters}', '')::jsonb, '{}'::jsonb) as filters
),
filter1 as (
    select jsonb_array_elements_text(filters->'filter1') as val
    from parsed_filters
    where filters ? 'filter1' -- 检查filter1是否存在于JSON中
),
filter2 as (
    select jsonb_array_elements_text(filters->'filter2') as val
    from parsed_filters
    where filters ? 'filter2'
)
select 
    case 
        when (select count(*) from filter1) > 0 or (select count(*) from filter2) > 0
        then sum(
            case 
                -- 过滤逻辑：不存在的过滤条件自动跳过
                when (field in (select val from filter1) or not exists (select * from filter1))
                     and (other_field in (select val from filter2) or not exists (select * from filter2))
                then 1
                else 0
            end
        )
        else count(*)
    end
from data;

关键注意事项 #

• 避免直接判断'${filters}' != '{}'：如果用户输入的是格式合法但空的过滤条件（比如{"filter1": []}），这个判断会误判为有过滤条件，改用exists或count检查CTE是否有数据更准确。
• 始终保证CTE的合法性：因为CTE预执行的特性，必须确保所有CTE中的操作在任何变量值下都不会报错，修复JSON输入是核心前提。

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