How to get size of PostgreSQL jsonb field?

Question

I have a table with jsonb field in table.

CREATE TABLE data.items
(
  id serial NOT NULL,
  datab jsonb
)

How to get size of this field in a query like this:

select id, size(datab) from data.items

Gin

This can help you #12395038 – Rale

pg_column_size() or pg_size_bytes() postgresql.org/docs/current/static/… – Essentialism

Answer 1

For the number of bytes used to store:

select id, pg_column_size(datab) from data.items;

For the number of elements on the jsonb object:

select id, jsonb_array_length(datab) from data.items;

Load

pg_column_size returns "Number of bytes used to store a particular value (possibly compressed)" for the curious postgresql.org/docs/12/functions-admin.html – Keyser

is there a similar function that will return the number of elements in a jsonb[] object? – Cusk

the second one only works for json arrays, not objects – Borkowski

Answer 2

If the column uses EXTENDED storage (TOAST compression), you should use octet_length(datab::text) instead of pg_column_size

Ethyne

OP didn’t indicate whether the desired result is (compressed) size on disk or (uncompressed) size of the object. octet_length is uncompressed size, which seems more useful to me. – Sprinkle

Answer 3

To count the json string size:

select length(jsonb_field::text) from test;

Chronoscope