PostgreSQL JSONB Array vs JSONB – Which to Use?

arrayjsonpostgresql

Is there any reason to use array of JSONB values instead of just raw JSONB?

My use case is that I need to store custom forms definition in list of JSON objects. And my question is whether it would be better to store it as raw JSON value or split this into separate JSONB objects.

What are pros and cons of each solution?

Best Answer

Generally, if you have an array of one thing (statically typed), it's better to store them as jsonb[] (a native SQL array). There are far more functions that operate over a SQL arrays, and indexes. It also tends to less overhead in storage. JSONb only has,

jsonb_array_length(jsonb)
jsonb_array_elements(jsonb)
jsonb_array_elements_text(jsonb)

While Array has a far more, but most of those features are implemented as operators.

Ultimately, in most use cases it won't matter much. You're better off normalizing your data. You really shouldn't be storing multiple json documents in one row. That sounds like a big "con" to begin with.

Related Solutions

Postgresql – Postgres 9.4+: JSONB instead of EAV

JSONB may be easy to read, but it's complicated and inefficient to write into.

See for example this question: PostgreSQL update and delete property from JSONB column, on how it looks like. It's an order of magnitude harder than an update/delete with classic EAV tables.

Possibly when you'll have written the parts to append/merge/delete key/value pairs, the elegance or simplicity of that JSON-based design are going to seem much less obvious. As for the performance, I'm betting on it being much worse.

At the storage level, any write into one property in a JSON structure will require a rewrite of the entire column (and in fact of the containing row), with the same cost as if all properties changed. This is not optimal in terms of I/O size and pressure on vacuum.

This problem is mentioned on S.O in another question with some more links and references:

How to perform update operations on columns of type JSONB in Postgres 9.4

There's ongoing work to ease JSONB updates for the programmer. jsonbx provides functions and operators that can help with 9.4; presumably these will be integrated into PostgreSQL core in future versions. But the large I/O cost of a small update inside a large JSON object will remain.

jsonbx was demonstrated in a recent PG conference (youtube links):

Update and Delete operations for jsonb (part 1 of 2)
Update and Delete operations for jsonb (part 2 of 2)

Postgres Array into Geoserver – How to Import

You could just alter the table and convert the column in one step:

alter table ogrtable
  alter column arrcolumn
    type varchar
    using (array_to_string(arrcolumn, ', '));

Best Answer

Related Solutions

Postgresql – Postgres 9.4+: JSONB instead of EAV

Postgres Array into Geoserver – How to Import

Related Question