如何按jsonb数组中指定local_id的published字段排序？

云原生阿岚

2026-7-19

PostgreSQL 基于jsonb数组的用户筛选与指定元素排序方案

针对你需要筛选登录过local_id='3f46c949'应用的用户，并按该应用对应published字段排序的需求，提供两种无需新建关联表的可行方案：

方案一：使用`jsonb_path_query_first`精准提取排序字段

利用PostgreSQL的jsonb_path_query_first函数，直接定位数组中匹配指定local_id的元素并提取其published值，同时保留单条用户记录：

SELECT 
    u.*,
    jsonb_path_query_first(u.apps_last_accessed, '$[*] ? (@.local_id == "3f46c949")')->>'published' AS target_published
FROM 
    users u
WHERE 
    u.apps_last_accessed @> '[{"local_id":"3f46c949"}]'
ORDER BY 
    target_published DESC; -- 可根据需求改为ASC

逻辑说明：WHERE子句通过@>运算符快速筛选出包含目标应用记录的用户；jsonb_path_query_first精准提取数组中第一个匹配local_id的元素的published值，作为排序依据，确保每个用户仅返回一行数据。

方案二：使用`LATERAL`子查询展开并过滤

通过LATERAL关联子查询展开jsonb数组，同时过滤出匹配local_id的元素，直接用该元素的published排序：

SELECT 
    u.*,
    app->>'published' AS target_published
FROM 
    users u,
    LATERAL jsonb_array_elements(u.apps_last_accessed) AS app
WHERE 
    app->>'local_id' = '3f46c949'
ORDER BY 
    target_published DESC; -- 可根据需求改为ASC