plpgsql - How to convert a text field of chars to json using pl/pgsql?

Question

Need to take a text field such as 'O T D' and convert it to a json output like:

"types": [
        "O",
        "T",
        "D"
    ],

This is a sub output of the entire query being exported to json with the json_agg() function. This code works in t-sql:

JSON_QUERY('["' + replace(rtrim(ltrim(type)), ' ','","') + '"]')  as 'types',

i.e. What pl/pgsql function is similar to t-sql JSON_QUERY?

question from:https://stackoverflow.com/questions/65910884/how-to-convert-a-text-field-of-chars-to-json-using-pl-pgsql

Answer 1

There is no PL/pgSQL function for this, but you can use SQL functions:

SELECT jsonb_agg(x) FROM regexp_split_to_table('O T D', ' ') AS x;

    jsonb_agg    
-----------------
 ["O", "T", "D"]
(1 row)