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

Check the first function mentioned here to validate a single json-string: stackoverflow.com/questions/30187554/… Select all rows of the table and send the column you want to validate through this function, then you will see which rows are invalid. How you repair them.. is up to you. Btw, your example has a comma to much. — Islingre
– Islingre, Commented Apr 11, 2022 at 20:24

Ramin Faracov · Accepted Answer · 2022-04-14 01:08:36Z

2

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;

answered Apr 14, 2022 at 1:08

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dev Babai Over a year ago

corrected the SQL statement, thank you

Dev Babai Over a year ago

does the text column needs to have same keys in all the records to convert it to jsonb ? this column has varying fields

Bergi Over a year ago

@DevBabai No, it's json, every object stands on its own; there is no "document schema" or something

Collectives™ on Stack Overflow

Convert text column to jsonb in Postgres

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related