Convert text column to jsonb in Postgres

Question

I have a column products in a table test which is of type of text in the below format:

 [{"is_bulk_product": false, "rate": 0, "subtotal": 7.17, "qty": 2, "tax": 0.90}]

It is an array with nested dictionary values. When I tried to alter the column using this:

alter table test alter COLUMN products type jsonb using products::jsonb;

I get below error:

ERROR:  22P02: invalid input syntax for type json
DETAIL:  Character with value 0x09 must be escaped.
CONTEXT:  JSON data, line 1: ...some_id": 2613, "qty": 2, "upc": "1234...
LOCATION:  json_lex_string, json.c:789
Time: 57000.237 ms (00:57.000)

how can we make sure the json is valid before altering the column ?

Thank You

Answer 1

Your written JSON string is correct, so this SQL code execute without exception:

select '[{"is_bulk_product": false, "rate": 0, "subtotal": 7.17, "qty": 2, "tax": 0.90}]'::jsonb

Maybe the table has an incorrect JSON format in other records. You can firstly check this by selecting data, example:

select products::jsonb from test;

And you have incorrect syntax on your SQL code, you can cast products field to JSONB but not a test, test is your table name:

alter table test 
alter COLUMN products type jsonb using products::jsonb;