CODEWITHSUNDEEP
postgresqltextjsonb
Joshua Rajandiran
Joshua Rajandiran

Reputation: 2928

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?

Upvotes: 0

Views: 593

Answers (1)

Leo C
Leo C

Reputation: 22439

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)

Upvotes: 1

Related Questions