Postgres Text to json

Question

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.

Are your strings such as "{steve,m,12.12.1995}" contained in arrays, or is each row text as well i.e '{"{max,m,26.01.1980}","{sarah,f,18.04.1990}"}'? — Ajax1234
– Ajax1234, Commented Jan 20, 2022 at 14:51
@Ajax1234 data type - text, so '{"{max,m,26.01.1980}","{sarah,f,18.04.1990}"}' — John Brown
– John Brown, Commented Jan 20, 2022 at 14:53

Edouard · Accepted Answer · 2022-01-20 17:36:09Z

2

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.

edited Jan 20, 2022 at 17:36

answered Jan 20, 2022 at 17:29

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eshirvana · Accepted Answer · 2022-01-20 21:25:28Z

0

here is one way:

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

edited Jan 20, 2022 at 21:25

answered Jan 20, 2022 at 16:12

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Collectives™ on Stack Overflow

Postgres Text to json

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related