Yotam Ofek Yotam Ofek - 2 months ago 22
JSON Question

Use Postgres to parse stringified JSON object

I've been using Postgres to store JSON objects as strings, and now I want to utilize PG's built-in

json
and
jsonb
types to store the objects more efficiently.

Basically, I want to parse the stringified JSON and put it in a
json
column from within PG, without having to resort to reading all the values into Python and parsing them there.

Ideally, my migration should look like this:

UPDATE table_name SET json_column=parse_json(string_column);


I looked at Postgres's JSON functions, and there doesn't seem to be a method of doing this, even though it seems pretty trivial. For the record, my JSON objects are just one-dimensional arrays of strings.

Is there any way to do this?

Answer

There is no need for a parse_json column, just change the type of the column:

ALTER TABLE table_name
ALTER COLUMN json_column TYPE json USING json_column::json;

Note that if you plan on doing a lot of JSON operations on these values (i.e. extracting elements from objects, modifying objects etc) it's better to use jsonb. json should only be used for storing JSON data. Also, as Laurenz Albe points out, if you don't need to do any JSON operations on these values and you are not interested in the validation that postgresql can do on them (e.g. because you trust that the source always provides valid JSON), then using text is a perfectly valid option (or bytea).