Question 169667 in PostgreSQL Question posted in
The official documentation can be found here.

ql Can Postgresql convert a json array to a csv?

Using postgres version 14.x, how do I convert the contents of a column (TEXT type) containing a json array to a csv?

For example

mytable
mycolumn
["10.0.0.1/32", "10.0.0.2/32", "10.0.0.3/32"]

to
10.0.0.1/32,10.0.0.3/32,10.0.0.3/32

Thanks!

Tags:

2

Answers


  1. 0

    you can use jsonb_array_elements_text to Expands a JSON array to a set of text values then string_agg to concatenate those values :

    select id, string_agg(elem, ',')
from 
    mytable t
    cross join jsonb_array_elements_text(t.mycolumn::jsonb) elem
group by id

    Simple data :

    CREATE TABLE mytable (
  id int,
  mycolumn  varchar(250)
);


insert into mytable values
(1, '["10.0.0.1/32", "10.0.0.2/32", "10.0.0.3/32"]'),
(2, '["10.0.2.1/32", "10.0.2.2/32", "10.0.2.3/32"]');

    Result :

    id  csv_line
1   10.0.0.1/32,10.0.0.2/32,10.0.0.3/32
2   10.0.2.1/32,10.0.2.2/32,10.0.2.3/32

    Demo here

    Login or Signup to reply.
  2. 0

    You can use REGEXP_REPLACE to carry out this task.

    The matching pattern ([[]"]) should be a collection of characters to be replaced, namely the open bracket, the closed bracket and the double quote, to be escaped () and enclosed in brackets.

    In order to make sure the operation replaces all occurrences of brackets and double quotes, you need to specify 'g' flag as fourth argument of the function

    UPDATE mytable
SET mycolumn = REGEXP_REPLACE(mycolumn, '[[]"]', '', 'g');

    Output:

    mycolumn
    10.0.0.1/32, 10.0.0.2/32, 10.0.0.3/32

    Check the demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search