Postgres Text to json-CodePudding

I have a column with the following text data (2 rows):

{"{john,m,25.01.1980}","{steve,m,12.12.1995}","{kate,f,17.04.1990}"}
{"{max,m,26.01.1980}","{sarah,f,18.04.1990}"}

This need to be converted into json like this (2 rows):

[{ "birth_date": 1234567890, "name": "john", "gender": "m" }, { "birth_date": 1234567890, "name": "steve", "gender": "m" }, { "birth_date": 1234567890, "name": "kate", "gender": "f" }]
[{ "birth_date": 1234567890, "name": "max", "gender": "m" }, { "birth_date": 1234567890, "name": "sarah", "gender": "f" }]

I have tried to use UNNEST, row_to_json and json_build_object, but cannot fugure out how to do this.

CodePudding user response：

This is the best I could come up with :

select Id
    , json_agg(json_build_object('name', split_part(jsondata, ',',1) , 'gender', split_part(jsondata, ',',2), 'birth_date', split_part(jsondata, ',' ,3))) json_info
from (
select id
,  replace(replace(json_array_elements(replace(replace(info,'{"','["'), '"}','"]')::json) #>> '{}','{',''),'}','') jsondata
from tablename
) t group by id

db<>fiddle here

CodePudding user response：

You can try this :

SELECT jsonb_agg(jsonb_build_object
                       ('birth_date', split_part(left(d.data, -1), ',', 3) :: date
                       ,'name', split_part(right(d.data, -1), ',', 1)
                       ,'gender', split_part(d.data, ',', 2)))
  FROM your_table AS t
 CROSS JOIN LATERAL unnest(t.your_text_column :: text[]) AS d(data)
 GROUP BY t

see the test result in dbfiddle.