如何展开JSON数组列并提取指定key的string_value？

你需要从event_params JSON数组列中，为每个user_id提取key为"label"对应的string_value，且每个user_id仅返回一行记录。之前使用jsonb_to_recordset的交叉连接会将数组中每个元素拆分为单独行，导致同一user_id出现多条重复记录，以下是几种可行的解决方案：

方案1：使用JSON路径直接定位目标值 #

利用jsonb_path_query_first函数直接在JSON数组中匹配key为"label"的元素，提取其string_value，无需展开整个数组：

SELECT
    user_id,
    jsonb_path_query_first(
        event_params,
        '$[*] ? (@.key == "label").value.string_value'
    )::TEXT AS label_string_value
FROM google_analytics.firebase_events_analytics;

说明：

• $[*]遍历数组中的所有元素
• ? (@.key == "label")筛选出key等于"label"的元素
• .value.string_value提取该元素下value对象中的string_value值
• jsonb_path_query_first返回第一个匹配的结果，确保每个user_id仅一行

方案2：横向连接+过滤+去重 #

通过LATERAL横向连接展开数组元素，过滤出目标key后，用DISTINCT ON保证每个user_id只取第一条匹配记录：

SELECT DISTINCT ON(user_id)
    user_id,
    (ev -> 'value' ->> 'string_value') AS label_string_value
FROM google_analytics.firebase_events_analytics,
LATERAL jsonb_array_elements(event_params) AS ev
WHERE ev ->> 'key' = 'label'
ORDER BY user_id;

说明：

• jsonb_array_elements(event_params)将JSON数组拆分为单个元素
• WHERE ev ->> 'key' = 'label'只保留key为"label"的元素
• DISTINCT ON(user_id)确保每个user_id仅返回一行，ORDER BY user_id指定去重的排序依据

方案3：聚合为JSON对象后提取值 #

先将每个user_id的所有event_params元素聚合成一个JSON对象，再直接提取label对应的string_value：

SELECT
    user_id,
    (jsonb_object_agg(ev.key, ev.value) -> 'label' ->> 'string_value') AS label_string_value
FROM google_analytics.firebase_events_analytics,
LATERAL jsonb_to_recordset(event_params) AS ev(key TEXT, value JSONB)
GROUP BY user_id;

说明：

• jsonb_to_recordset将数组元素拆分为key和value列
• jsonb_object_agg(ev.key, ev.value)将同一user_id的所有key-value对聚合成一个JSON对象
• -> 'label' ->> 'string_value'直接从聚合后的对象中提取目标值

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