0

I am having a hard time to figure out how to convert a nested array to a simple array in postgresSQL.

I got one column with the content [["one", "two", "three"]] and I would like to alter the column to have ["one", "two", "three"]. So basically to remove one nested array.

Anyone knows how to do it?

EDIT: I would need to update all the current values on the DB via an ALTER COLUMN

user3415011
  • 195
  • 1
  • 2
  • 11

2 Answers2

0

demo:db<>fiddle

Supposed you don't have a JSON array, you can use unnest() to extract all elements into one row and reaggregate these elements:

SELECT ARRAY(
    SELECT unnest(/*your array*/)
)

Further Reading:

demo:db<>fiddle

If you have JSON array, you can

  1. use json_array_elements() do extract all elements

    SELECT
    json_array_elements(a)
FROM (
    SELECT '[["one", "two", "three"]]'::json as a
)

  2. Or you simply use the -> operator to fetch the first element:

    SELECT
    a -> 0
FROM (
    SELECT '[["one", "two", "three"]]'::json as a
)s

Update all JSON data:

demo:db<>fiddle

UPDATE t
SET mydata = mydata -> 0;
S-Man
  • 22,521
  • 7
  • 40
  • 63
  • Thanks, indeed this is a json array. How do I manage to apply your SQL to update all the entries already in the DB at once? – user3415011 Nov 24 '20 at 15:39
  • Did this help? Please don't forget to upvote every helpful answer and additionally accept the most helpful to show that your problem has been solved. – S-Man Nov 25 '20 at 08:09
0

I would need to update all the current values on the DB via an ALTER COLUMN

To update a column value, you use UPDATE not ALTER

The following will replace the value in the column with the "nested" array.

update the_table
  set the_column = the_column -> 0
where jsonb_typeof(the_column) = 'array'
  and jsonb_typeof(the_column -> 0) = 'array';

If the column is defined as json not as jsonb (which it should be), then you need to use json_typeof() instead.