PostgreSQL中按JSONB数组项排序并关联时如何避免结果重复？

我意识到JSONB列或许不是最佳选择，但不想为了单个字段可排序就新建表。我需要在应用里做一个视图，展示用户登录过的应用，并按登录时间排序。登录数据存在JSONB列中，用jsonb_array_elements把数组拆成行后，结果出现重复行。一开始以为是JOIN导致的，但每个应用的published时间不同，问题应该是没把关联限制到正确的JSONB数组项上。

表结构与测试数据 #

CREATE TABLE suppliers (
    id integer,
    name character varying,
    slug character varying
);
INSERT INTO suppliers (id, name, slug) VALUES (1, 'Okta', 'okta');
INSERT INTO suppliers (id, name, slug) VALUES (2, 'Bitbucket', 'bitbucket');
INSERT INTO suppliers (id, name, slug) VALUES (3, 'Salesforce', 'salesforce');
INSERT INTO suppliers (id, name, slug) VALUES (4, 'Heroku', 'heroku');

CREATE TABLE supplier_sso_apps (
    id integer,
    supplier_id integer,
    target_supplier_id integer,
    sso_app_id character varying(512),
    sso_app_label character varying(512)
);
INSERT INTO supplier_sso_apps (id, supplier_id, sso_app_id, sso_app_label, target_supplier_id) VALUES (1, 1, '0oa2zi72tv1dhRGwt5d7', 'Bitbucket App', 2);
INSERT INTO supplier_sso_apps (id, supplier_id, sso_app_id, sso_app_label, target_supplier_id) VALUES (2, 1, '0oa6eb0tgusEP6WW45d7', 'Salesforce App', 3);
INSERT INTO supplier_sso_apps (id, supplier_id, sso_app_id, sso_app_label, target_supplier_id) VALUES (3, 1, '0oa2fabmgycm617Ug5d7', 'Heroku App 1', 4);
INSERT INTO supplier_sso_apps (id, supplier_id, sso_app_id, sso_app_label, target_supplier_id) VALUES (4, 1, '0oa2fagt7wngg7tNM5d7', 'Heroku App 2', 4);


CREATE TABLE sso_employees_apps (
    id integer,
    user_id integer,
    apps_last_accessed jsonb
);
INSERT INTO sso_employees_apps (id, user_id, apps_last_accessed) VALUES (1, 1, '[{"name": "Atlassian", "local_id": null, "published": "2023-06-06T00:20:16.217Z", "external_id": "0oa9gmxlpkjVzqmum5d7"}, {"name": "Bitbucket Yo", "local_id": "2", "published": "2023-06-06T00:19:58.236Z", "external_id": "0oa2zi72tv1dhRGwt5d7"}, {"name": "Salesforce App", "local_id": "3", "published": "2023-04-02T03:36:39.160Z", "external_id": "0oa6eb0tgusEP6WW45d7"}, {"name": "Heroku App 1", "local_id": "4", "published": "2023-06-14T17:23:22.982Z", "external_id": "0oa2fabmgycm617Ug5d7"}]');
INSERT INTO sso_employees_apps (id, user_id, apps_last_accessed) VALUES (2, 2, '[{"name": "Heroku App 1", "local_id": "4", "published": "2023-06-14T17:23:22.982Z", "external_id": "0oa2fabmgycm617Ug5d7"}, {"name": "Heroku App 2", "local_id": "4", "published": "2023-04-07T03:36:39.160Z", "external_id": "0oa2fagt7wngg7tNM5d7"}]');

原查询问题 #

原查询用交叉连接拆分JSONB数组后，又额外LEFT JOIN了sso_employees_apps，导致每个匹配项都和数组里的所有项关联，产生重复：

SELECT supplier_sso_apps.id, supplier_sso_apps.target_supplier_id, supplier_sso_apps.sso_app_id, supplier_sso_apps.sso_app_label, j ->> 'published' as published FROM
(
  SELECT supplier_sso_apps.id, supplier_sso_apps.target_supplier_id, supplier_sso_apps.sso_app_id, supplier_sso_apps.sso_app_label, j
  FROM supplier_sso_apps, sso_employees_apps, jsonb_array_elements(apps_last_accessed) as x(j)
) supplier_sso_apps
JOIN suppliers ON suppliers.id = supplier_sso_apps.target_supplier_id
LEFT JOIN sso_employees_apps ON supplier_sso_apps.sso_app_id = (j ->> 'external_id')
ORDER BY published ASC

补充需求 #

需要从supplier_sso_apps出发，只显示和用户登录记录匹配的行，用户1的目标结果如下：

正确的做法是在拆分JSONB数组时，直接关联sso_employees_apps和supplier_sso_apps，限制只匹配external_id等于sso_app_id的数组项，同时指定用户ID避免跨用户数据干扰：

SELECT
    s.name AS 供应商名称,
    ssa.sso_app_label AS 应用标签,
    ssa.sso_app_id AS 应用ID,
    app ->> 'published' AS published
FROM supplier_sso_apps ssa
JOIN suppliers s ON ssa.target_supplier_id = s.id
JOIN sso_employees_apps sea ON sea.user_id = 1  -- 指定目标用户
JOIN jsonb_array_elements(sea.apps_last_accessed) app 
    ON app ->> 'external_id' = ssa.sso_app_id
ORDER BY (app ->> 'published') ASC;

解释 #

1. 锁定目标用户：通过sea.user_id = 1限定查询范围，避免其他用户的登录数据混入结果
2. 精准匹配数组项：拆分JSONB数组时，直接用app ->> 'external_id' = ssa.sso_app_id过滤，只保留和当前应用对应的登录记录
3. 简化关联逻辑：无需额外子查询或重复连接表，一次关联即可获取所需数据，避免重复行产生

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