CODEWITHSUNDEEP
postgresql
Saif
Saif

Reputation: 3462

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

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.

Upvotes: 0

Views: 769

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 2

Related Questions