CODEWITHSUNDEEP
postgresql
Mano
Mano

Reputation: 711

Convert comma separated non json string to json

Below is the value of a string in a text column.

select col1 from tt_d_tab;

'A:10000000,B:50000000,C:1000000,D:10000000,E:10000000'

I'm trying to convert it into json of below format.

'{"A": 10000000,"B": 50000000,"C": 1000000,"D": 10000000,"E": 10000000}'

Can someone help on this?

Upvotes: 0

Views: 497

Answers (2)

Pepe N O
Pepe N O

Reputation: 2354

If your key is a single capital letter as in your example

select concat('{',regexp_replace('A:10000000,B:50000000,C:1000000,D:10000000,E:10000000','([A-Z])','"\1"','g'),'}')::json json_field;

A more general case with any number of letters caps or not

select concat('{',regexp_replace('Ac:10000000,BT:50000000,Cs:1000000,D:10000000,E:10000000','([a-zA-Z]+)','"\1"','g'),'}')::json json_field;

Upvotes: 1

histocrat
histocrat

Reputation: 2381

If you know that neither the keys nor values will have : or , characters in them, you can write

select json_object(regexp_split_to_array(col1,'[:,]')) from tt_d_tab;

This splits the string on every colon and comma, then interprets the result as key/value pairs.

If the string manipulation gets any more complicated, SQL may not be the ideal tool for the job, but it's still doable, either by this method or by converting the string into the form you need directly and then casting it to json with ::json.

Upvotes: 2

Related Questions