Use Postgres to parse stringified JSON object

Question

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?

json and jsonb don't store the objects more efficiently. If all you do is store and retrieve JSON values, you might as well stick with text. The JSON types are interesting if you want the database to do something with the data, like process them (jsonb) or check their integrity (json). — Laurenz Albe
– Laurenz Albe, Commented Sep 21, 2016 at 11:51
Actually, jsonb does store the objects more efficiently, since it is storing it in binary representation instead of string representation. For big data sets this can be a critical gain in storage space, which also affects index size and more. Secondly, storing JSON as text is as stupid as storing integers as text. Even if there is no practical need for it, data clarity should always be enforced, and as you said, having the database checking the integrity is never a bad idea. — Yotam Ofek
– Yotam Ofek, Commented Sep 21, 2016 at 12:11
I agree that conversion to json could hardly be a loss. But I am curious if you actually tested that jsonb needs less space than text. text is stored compressed by default if it exceeds a certain length (TOAST). And INSERT and SELECT will take slightly longer because the value has to be parsed or unparsed. — Laurenz Albe
– Laurenz Albe, Commented Sep 21, 2016 at 12:18
Obviously, it very much depends on what type of data you are storing. As my JSON objects are a list of strings, the storage cost could be comparable to TOASTed text, but for a complex data type with many large numbers, storing binary representations will provide a huge gain. And furthermore, if you've ever dealt with large data sets that might change over a period of time, you've probably learnt that choosing the right data representation type is imperative to a clean DB. Your comments are unconstructive, since my question was not "should I?", but rather "how can I?". — Yotam Ofek
– Yotam Ofek, Commented Sep 21, 2016 at 12:59
I was referring to the OPs remark: I want to utilize PG's built-in json and jsonb types to store the objects more efficiently. But it's no use having a theoretical discussion about this here. — Laurenz Albe
– Laurenz Albe, Commented Sep 21, 2016 at 13:03

redneb · Accepted Answer · 2016-09-21 12:05:31Z

9

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).

edited Sep 21, 2016 at 12:05

answered Sep 21, 2016 at 11:48

redneb

24.3k7 gold badges46 silver badges55 bronze badges

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

1 Comment

Yotam Ofek Over a year ago

Thank you very much for the prompt and informative response! I think a more general answer about parsing JSON objects would be better for future references. That is, the most important thing I learnt from your answer is that casting to JSON types (::json or ::jsonb) is the standard way of parsing JSON strings in PG. Too bad there doesn't seem to be any documentation about this feature.

Collectives™ on Stack Overflow

Use Postgres to parse stringified JSON object

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related