PostgreSQL中如何从JSON文本提取哈希数组键值并逗号分隔展示？

云原生阿岚

2026-5-12

Solution to Extract Comma-Separated Genre Texts from JSON Text Field

Got it, let's get that query working exactly how you need it. The issue with your current statement is that you're pulling the entire genres array instead of extracting just the text values and combining them. Here's how to fix it:

Step-by-Step Breakdown

Since your data field is stored as text, we'll first cast it to jsonb (it’s more efficient for array operations than plain json) and then:

Use jsonb_array_elements to "unpack" the genres array into individual rows, each holding one genre object
Extract the text value from each genre object using ->>'text' (the double arrow returns a plain text string instead of a JSON value)
Use string_agg to stitch all the genre texts into a single comma-separated string
Group by the book’s id to ensure each book gets one row with its combined genres

Final Working Query

SELECT
  id,
  string_agg(genre->>'text', ',') AS genre_list
FROM
  books,
  jsonb_array_elements((data::jsonb)->'genres') AS genre
WHERE
  (data::jsonb)->'genres' IS NOT NULL
GROUP BY
  id
LIMIT 1;

Handling Edge Cases

If some books might have an empty genres array or the field is null, use COALESCE to return a clean default value (like an empty string) instead of null:

SELECT
  id,
  COALESCE(string_agg(genre->>'text', ','), '') AS genre_list
FROM
  books,
  jsonb_array_elements((data::jsonb)->'genres') AS genre
GROUP BY
  id
LIMIT 1;