Concat values to string array postgres

Question

I want to reach following structure:

["687ccca","da075bd","4194d"]

and try to achieve it like this:

UPDATE table1
SET "ids"= CONCAT  ('[', result, ']')
FROM (SELECT string_agg(  id::character varying, ', ')
FROM   table2 where "name" like '%W11%'
or "name" like '%12%'
or "name" like '%13%'
or "name" like '%5%'
or "name" like '%W9%'
or "name" like '%74%'
) AS result
WHERE "ids"='all';

however I get this:

[("df6bd58d, 26e094b, 637c1, 4a8cf387ff43c5, 9b0bf9f")]

How do I remove ( and ) and add " after each id?

Answer 1

I believe you want to get an JSON array:

demo:db<>fiddle

SELECT
    json_agg(your_texts)
FROM
    your_table

If you really want text you can cast this result with ::text into a text afterwards:

json_agg(your_texts)::text