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

This can help you http://stackoverflow.com/questions/12394538/calculating-the-size-of-a-column-type-in-postgresql — A l w a y s S u n n y, Nov 05 '16 at 16:27
`pg_column_size()` or `pg_size_bytes()` https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE — , Nov 05 '16 at 16:30

score 89 · Accepted Answer · edited Jun 19 '22 at 10:31

89

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;

edited Jun 19 '22 at 10:31

2240

answered Sep 18 '17 at 08:00

dopeddude

2

`pg_column_size` returns "Number of bytes used to store a particular value (possibly compressed)" for the curious https://www.postgresql.org/docs/12/functions-admin.html – ajsharma Jun 17 '20 at 20:45
is there a similar function that will return the number of elements in a jsonb[] object? – Brian D Feb 16 '22 at 21:47
the second one only works for json arrays, not objects – Joachim Lous Aug 30 '23 at 09:11

score 9 · Answer 2 · answered Jan 13 '22 at 10:01

9

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

answered Jan 13 '22 at 10:01

sudokai

1

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. – Aryeh Leib Taurog Oct 24 '22 at 12:54

2 Answers2