JSONB array to comma-separated string without using group by in postgres

Question

my_column_name  jsonb not null default '[]'::jsonb

I have a jsonb column which is guaranteed to be an array of strings. How can I select it as a comma-separated string. For example, If the column value is ["foo", "bar"] the selection should return foo,bar (text) (Note: no quotes and no spaces).

For [], it should just give an empty string.

What have I tried so far:

select translate(my_column_name::TEXT, '[]" ', '')

But it may not work if I have those characters within the array itself.

I also want to avoid CTEs, custom functions, group by as much as possible.

Combine built-in string_agg with json_array_elements_text functions. — freakish
– freakish, Commented Oct 1, 2022 at 11:30
"I also want to avoid CTEs, custom functions, group by as much as possible." - why would you? Those are exactly the things that you should use. — Bergi
– Bergi, Commented Oct 1, 2022 at 13:19

score 2 · Accepted Answer · 2022-10-01 14:09:05Z

I would create a function for that:

create function json_array_to_csv(p_input jsonb)
  returns text
as
$$
  select string_agg(x.item, ',' order by idx)
  from jsonb_array_elements_text(p_input) with ordinality as x(item, idx);
$$ 
language sql
immutable;

Then

select json_array_to_csv('["foo", "bar"]');

json_array_to_csv
-----------------
foo,bar

If for some strange (and incomprehensible) reason you don't want to use a custom function and make your life harder than it needs to be, you can use it as a scalar-subquery in the column list of a SELECT query:

select some_column, 
       other_column,
       (select string_agg(x.item, ',' order by idx)
        from jsonb_array_elements_text(the_jsonb_column) with ordinality as x(item, idx)
       ) as items
from the_table;

But I would always prefer:

select some_column, 
       other_column,
       json_array_to_csv(the_jsonb_column) as items
from the_table;

Collectives™ on Stack Overflow

JSONB array to comma-separated string without using group by in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related