如何在PostgreSQL中基于两次自连接表的唯一值创建数组？

Got it, let's figure out how to merge those unique tag names from your tag1 and tag2 self-joins into a single, deduplicated array column. Since you're using jsonb_agg, I assume you're working with PostgreSQL—here are a couple of clean, efficient ways to do this:

Method 1: Use a LATERAL Join to Combine Tags Before Aggregating #

This is the most straightforward and performant approach. We'll first collect all non-null tag names from both tag1 and tag2 using a LATERAL subquery, then aggregate and deduplicate them in one go:

SELECT
  st.id, -- Replace with your actual grouping column(s)
  jsonb_agg(DISTINCT t.tag_name) AS tag
FROM your_main_table st -- Replace with your main table name
LEFT JOIN tag tag1 ON st.some_id = tag1.related_id -- Your existing tag1 join condition
LEFT JOIN tag tag2 ON st.another_id = tag2.related_id -- Your existing tag2 join condition
LEFT JOIN LATERAL (
  -- Pull in valid tags from tag1
  SELECT tag1.tag_name
  WHERE tag1.tag_name IS NOT NULL
  UNION ALL
  -- Pull in valid tags from tag2
  SELECT tag2.tag_name
  WHERE tag2.tag_name IS NOT NULL
) t ON true
GROUP BY st.id; -- Match this to your grouping column(s)

How this works: #

• The LATERAL subquery acts as a per-row "collection bucket" that gathers all valid tags from both joined tables, skipping null values.
• UNION ALL preserves all entries (including duplicates between tag1 and tag2) so we can deduplicate them later with DISTINCT in jsonb_agg.
• Finally, jsonb_agg(DISTINCT t.tag_name) rolls up all unique tags into a single JSONB array exactly like you need.

Method 2: Merge Arrays and Deduplicate After Aggregation #

If you prefer to keep your initial aggregation logic for tag1 and tag2, you can merge the two arrays and then remove duplicates by expanding and re-aggregating:

SELECT
  st.id, -- Replace with your grouping column(s)
  jsonb_agg(DISTINCT elem) AS tag
FROM (
  SELECT
    st.id,
    -- Expand merged arrays into individual elements
    jsonb_array_elements(
      COALESCE(jsonb_agg(DISTINCT tag1.tag_name) OVER (PARTITION BY st.id), '[]'::jsonb) ||
      COALESCE(jsonb_agg(DISTINCT tag2.tag_name) OVER (PARTITION BY st.id), '[]'::jsonb)
    ) AS elem
  FROM your_main_table st
  LEFT JOIN tag tag1 ON st.some_id = tag1.related_id
  LEFT JOIN tag tag2 ON st.another_id = tag2.related_id
) sub_query
GROUP BY st.id;

How this works: #

• COALESCE replaces any null arrays (from left joins with no matches) with an empty JSONB array '[]' to avoid merge errors.
• The || operator combines the two arrays into one.
• jsonb_array_elements breaks the merged array into individual rows, then we re-aggregate with DISTINCT to eliminate duplicates.

Both methods will give you a single tag column containing all unique values from tag1 and tag2, formatted like ["Sport", "Music", "Games"] as you requested.

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