Question 114807 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search