0

I have inserted the escaped string into jsonb column of table in pgadmin. as below

Table name: tbl1

1. In this table I have a jsonb type column named as "info_data(jsonb column)" By my mistake I have inseerted escapped string in this jsonb column where slashes are coming as below.

id info_data(jsonb column)
1 { "area": { "unit": "yards", "value": 32.45 }, "houseName": "MyHome", "registrationDate": "{\"value\":\"2020-12-30T00:00:00Z\",\"source\":\"\",\"modifiedBy\":\"Admin\"}", "SuperArea": 51 }
2 null

To fix it I tried few queries like but all these did not worked:

UPDATE tbl1 SET info_data = (info_data #> '{}')::jsonb WHERE id = 1;

UPDATE tbl1 SET info_data = (replace(info_data::text, '\"', '"'))::json WHERE id = 1;

Please tell me how I can achieve the expected result using sql update query to correct my data?

My expected result is:

2. Now I have to correct this data using the update sql query by updating jsonb with valid json value without any escapped character.

id info_data(jsonb column)
1 { "area": { "unit": "yards", "value": 32.45 }, "houseName": "MyHome", "registrationDate": {"value":"2020-12-30T00:00:00Z","source":"","modifiedBy":"Admin"}, "SuperArea": 51 }
2 null

Please tell me how I can achieve the expected result using sql update query to correct my data?

vivek
  • 1
  • 1
  • if i copy your text into a json_b field it is all gome, https://dbfiddle.uk/MO5LmhF1 – nbk Apr 03 '23 at 19:43
  • Can you please try to paste the jsonb value directly into the column without using the insert statement because insert statement is automatically handling it and removing escape characters. – vivek Apr 04 '23 at 04:41
  • No when you say you have a jsonb like column, so what do you have exactly , what is jsonb like? – nbk Apr 04 '23 at 05:41

0 Answers0