PostgreSQL: array in text to array

Question

I have JSON array in text type field in DB, and I want to check whether there are some elements in the array, and in that case, add some elements to this array and save it back to DB. The main issue I am having is that this text field looks like this:

["elem1","elem2","elem3"]

and I cannot figure out how to work with those double-quotes.

When I tried to_json it resulted in:

"[\"elem1\",\"elem2\",\"elem3\"]"

When I tried string_to_array:

{"[\"elem1\"","\"elem2\"","\"elem3\"]"}

I just need something like that:

['elem1', 'elem2', 'elem3']

Answer 1

To get a JSON array, simply cast it into json directly:

demo:db<>fiddle

SELECT '["elem1","elem2","elem3"]'::json

If you need a simple array (without JSON), you need to expand the elements of the JSON array (json_array_elements_text()) and aggregate them afterwards (array_agg()):

SELECT 
    array_agg(elems)
FROM mytable,
    json_array_elements_text(mydata::json) AS elems