Search code examples
postgresqltextjsonb

Converting this text to jsonb?

I have this text:

("{""A"":""0/000"",""B"":2}")

I want to convert it to a jsonb like this:

{"A":"0/000","B":2}

How do I do this?

Solution

  • Assuming that you want to convert the value of a text column into a separate jsonb column, you can use functions substring and replace, and cast the result to jsonb as follows:

    INSERT INTO my_table (id, text_col, jsonb_col) VALUES
  (1, '("{""A"":""0/000"",""B"":2}")', null);

UPDATE my_table SET
  jsonb_col = replace(substring(text_col, '{.*}'), '""', '"')::jsonb
WHERE id = 1;

SELECT id, text_col, jsonb_col FROM my_table WHERE id = 1;
 id |             text_col          |          jsonb_col          
----+-------------------------------+------------------------
  1 | ("{""A"":""0/000"",""B"":2}") | {"A": "0/000", "B": 2}
(1 row)