Convert comma separated non json string to json-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

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.