Using PostgreSQL for JSON Storage
info.crunchydata.com
Using PostgreSQL for JSON Storage
1–10 of 29 posts
Re: Using PostgreSQL for JSON Storage
#2At my office, we heavily lean on Postgres' JSONB capabilities, especially for archived data that is occasionally accessed, and also to handle data dumps from other services. The JSONB traversal operators are intuitive and the searching is fast-enough to be useful. Yet another reason that I reach for Postgres first time, every time.
Re: Using PostgreSQL for JSON Storage
#3Love it!
Re: Using PostgreSQL for JSON Storage
#4At my office, we heavily lean on Postgres' JSONB capabilities, especially for archived data that is occasionally accessed, and also to handle data dumps from other services. The JSONB traversal operators are intuitive and the searching is fast-enough to be useful. Yet another reason that I reach for Postgres first time, every time.
And you can index the fields in the jsonb too! PG all the things!
Re: Using PostgreSQL for JSON Storage
#5I made a linter for PG with a rule that will complain if you use JSON instead of JSONB: [link redacted]
Re: Using PostgreSQL for JSON Storage
#6Postgres admittedly cheated a bit at first with the basic JSON support, validation of JSON but then storing it in a text field didn't quite make it a full document database. But that was also 8 years ago... Even then there is still a valid use case for JSON if you're not looking to parse it or want to preserve the whitespacing-this is handy when recording API input/output for logs.
JSONB came just 2 years later and very much gives more document capabilities. Being a compressed format, the ability to index it, query based on keys and values... all those things make it incredibly handy. It's very rare for me to have an application these days with Postgres that doesn't leverage JSONB in someway.
Re: Using PostgreSQL for JSON Storage
#7Postgres admittedly cheated a bit at first with the basic JSON support, validation of JSON but then storing it in a text field didn't quite make it a full document database. But that was also 8 years ago... Even then there is still a valid use case for JSON if you're not looking to parse it or want to preserve the whitespacing-this is handy when recording API input/output for logs. JSONB came just 2 years later and v…
> Postgres admittedly cheated a bit at first with the basic JSON support, validation of JSON but then storing it in a text field didn't quite make it a full document database.
Did anybody claim it was back then?
> Being a compressed format
I'd argue its a structured format, rather than a compressed format. I.e. it allows traversal without (re-)parsing the json from text. But most of the time the jsonb version won't be (meaningfully) smaller.
Re: Using PostgreSQL for JSON Storage
#8> Let's get the users last name.
SELECT json_content ##> {person, last_name} FROM mytable;
That should be #>> (two ">"), not ##> (two "#").
The official docs are really good and they include a number of inline examples showing the difference between the operators: https://www.postgresql.org/docs/current/functions-json.html
The bottom example is missing a colon on the cast as well:
> SELECT json_content FROM mytable WHERE json_content @> '{"status": "Awesome"}':jsonb;
Should be two colons ("::") for an explicit cast: '{"status": "Awesome"}'::jsonb
Re: Using PostgreSQL for JSON Storage
#9Postgres admittedly cheated a bit at first with the basic JSON support, validation of JSON but then storing it in a text field didn't quite make it a full document database. But that was also 8 years ago... Even then there is still a valid use case for JSON if you're not looking to parse it or want to preserve the whitespacing-this is handy when recording API input/output for logs. JSONB came just 2 years later and v…
> Postgres admittedly cheated a bit at first with the basic JSON support, validation of JSON but then storing it in a text field didn't quite make it a full document database. Did anybody claim it was back then? > Being a compressed format I'd argue its a structured format, rather than a compressed format. I.e. it allows traversal without (re-)parsing the json from text. But most of the time the jsonb version won't b…
Hi Andres... I think I did back then ;)
Though to be fair, yes the PostgreSQL has never officially said it's a full document database to my knowledge.
Re: Using PostgreSQL for JSON Storage
#10I use JSONB heavily for a few things:
* Tags. Basically the same as array of strings, which postgres also supports, but I don't need to remember a different set of operators.
* Polymorphic data. Instead of countless nullable fields that represent the union of all fields of all subtypes, I just include a single (or in some cases, a couple) JSONB columns whose schema varies by type. The schema is enforced by types at the application layer.
* Truly schemaless data. Sometimes I just need to keep track of an arbitrary JSON blob defined by some other service (say, an Order from shopify). Just dump it in my table as-is and now I can search orders.
Postgres' json functions and operators aren't wholly intuitive (updating bits of nested data can be hard; it's really a lot better if you can just update whole json fields at once) but overall it works pretty well.