如何在PostgreSQL中返回仅包含指定属性的稀疏JSON数据

PostgreSQL没有直接命名为including_only的语法，但有两种成熟的原生方案可以实现你要的仅保留指定键的效果：

方案1：兼容PostgreSQL 12及以上版本 #

使用jsonb_build_object手动构造仅包含目标键的JSON对象，支持保留完整的嵌套结构，示例代码如下：

SELECT jsonb_build_object(
    'a', json_col -> 'a',
    'b', json_col -> 'b',
    'c', json_col -> 'c',
    'g', json_col -> 'g',
    'n', json_col -> 'n'
) AS filtered_json
FROM your_table
WHERE 你的查询条件;

如果需要自动过滤原JSON中不存在的键（避免生成值为null的键），可以在外层套入jsonb_strip_nulls函数：

SELECT jsonb_strip_nulls(jsonb_build_object(
    'a', json_col -> 'a',
    'b', json_col -> 'b',
    'c', json_col -> 'c',
    'g', json_col -> 'g',
    'n', json_col -> 'n'
)) AS filtered_json
FROM your_table
WHERE 你的查询条件;

方案2：PostgreSQL 16及以上版本（推荐） #

PostgreSQL 16新增了原生函数jsonb_select_keys，专门用于仅提取JSONB对象的指定键，语法更简洁，性能也经过原生优化：

SELECT jsonb_select_keys(json_col, ARRAY['a','b','c','g','n']) AS filtered_json
FROM your_table
WHERE 你的查询条件;

普通JSON类型适配方案 #

如果你使用的是普通JSON类型而非推荐的JSONB类型，可以先将字段转为JSONB类型处理，完成后再转回JSON类型即可：

SELECT to_json(jsonb_select_keys(json_col::jsonb, ARRAY['a','b','c','g','n'])) AS filtered_json
FROM your_table
WHERE 你的查询条件;

嵌套字段处理 #

如果需要保留的是嵌套层级的属性，直接使用路径运算符提取即可，例如要保留顶层键a下的子键sub1：

SELECT jsonb_build_object(
    'a_sub1', json_col #> '{a,sub1}'
) AS filtered_json
FROM your_table;

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