PostgreSQL jsonb对象数组：按指定prop过滤并获取对应answer值

需求说明 #

现有数据表包含两列：id（标识列）、params（JSONB类型），其中params存储格式如下的对象数组：

[
    {
        "prop": "a",
        "answer": "123"
    },
    {
        "prop": "b",
        "answer": "456"
    }
]

需要编写查询语句，返回id与对应prop为特定值（比如"a"）的answer值，要求每个id最多返回一行，无匹配的id对应answer返回null。

解决方案 #

方法1：使用jsonb_path_query_first（PostgreSQL 12+推荐） #

这个函数会直接从JSON数组中找到第一个匹配条件的元素，提取目标字段，无需展开数组，效率较高：

SELECT 
    id,
    jsonb_path_query_first(params, '$[*] ? (@.prop == "a") ->> "answer"') AS answer
FROM your_table;

替换"a"为你需要匹配的prop值，your_table替换为实际表名即可。无匹配时answer自动返回null，天然保证每个id一行结果。

方法2：展开数组后去重+补全无匹配行 #

如果使用较低版本PostgreSQL，可通过展开数组后用DISTINCT ON去重，再补全没有匹配项的行：

-- 先获取有匹配的id及其answer
SELECT DISTINCT ON (id)
    id,
    elem ->> 'answer' AS answer
FROM your_table,
     jsonb_array_elements(params) AS elem
WHERE elem ->> 'prop' = 'a'

UNION ALL

-- 再补全无匹配的id，answer设为null
SELECT id, NULL AS answer
FROM your_table
WHERE NOT EXISTS (
    SELECT 1
    FROM jsonb_array_elements(params) AS elem
    WHERE elem ->> 'prop' = 'a'
);

方法3：聚合函数实现 #

通过展开数组后分组聚合，筛选出匹配的answer值：

SELECT
    id,
    MAX(CASE WHEN elem ->> 'prop' = 'a' THEN elem ->> 'answer' END) AS answer
FROM your_table,
     jsonb_array_elements(params) AS elem
GROUP BY id;

如果同一个id的数组中有多个匹配prop的元素，MAX会取最大的answer值；若只需任意一个匹配值，也可换成MIN或FIRST_VALUE。

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