Postgres中JSONB字段数组转TEXT报错问题求助

Got it, let's work through this problem step by step. You're trying to convert a JSONB array into a clean comma-separated text string, but your first two attempts hit function mismatch errors. Let's break down what went wrong and get you the right solution.

Why Your Previous Attempts Failed #

First Error: array_to_string() with jsonb_array_elements() #

Your first query used:

SELECT array_to_string( jsonb_array_elements( '{"id": 10,"domain_list": [ "dom1.com", "dom2.com", "dom3.com"]}'::jsonb->'domain_list' ), ', ' );

The issue here is that jsonb_array_elements() returns a set of JSONB values (a row set), not a native PostgreSQL array. The array_to_string() function only works with PostgreSQL's built-in array types (like text[]), so it doesn't recognize the JSONB row set as a valid input. Hence the "function does not exist" error.

Second Error: json_array_elements() with Text Input #

Your second query tried:

SELECT string_agg(domain_list::text, ', ') FROM json_array_elements( '{"id": 10,"domain_list": [ "dom1.com", "dom2.com", "dom3.com"]}'::jsonb->>'domain_list' ) as domain_list;

Here, ->> extracts the domain_list as a text string (not JSON/JSONB). But json_array_elements() requires a json type input, not text. That's why you got the function mismatch error—PostgreSQL can't find a version of json_array_elements() that accepts text.

Correct Solutions #

Option 1: Use jsonb_array_elements_text() + string_agg() #

The simplest and most direct way is to use jsonb_array_elements_text(), which extracts each element of the JSONB array as a text value, then aggregate those values with string_agg():

SELECT string_agg(domain, ', ')
FROM jsonb_array_elements_text('{"id": 10,"domain_list": [ "dom1.com", "dom2.com", "dom3.com"]}'::jsonb->'domain_list') AS domains(domain);

This will return exactly what you want: dom1.com, dom2.com, dom3.com.

Option 2: For Existing Table Columns #

If you're working with a table (instead of a literal JSONB value), adjust the query to reference your table and column. For example, if your table is named site_data and the JSONB column is metadata:

SELECT string_agg(domain, ', ')
FROM site_data,
     jsonb_array_elements_text(metadata->'domain_list') AS domains(domain)
WHERE metadata->>'id' = '10'; -- Optional: filter by the id field

Option 3: Convert to Native Array First (Alternative) #

If you prefer using array_to_string(), you can first aggregate the text values into a native PostgreSQL array with array_agg(), then pass that to array_to_string():

SELECT array_to_string(array_agg(domain), ', ')
FROM jsonb_array_elements_text('{"id": 10,"domain_list": [ "dom1.com", "dom2.com", "dom3.com"]}'::jsonb->'domain_list') AS domains(domain);

This works, but it's an extra step compared to Option 1.

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