how to find the json size stored in a column of postgres how to find the json size stored in a column of postgres sql sql

how to find the json size stored in a column of postgres

If you want to know how many bytes it takes to store column, then you can use

pg_column_size(any) - Number of bytes used to store a particular value (possibly compressed)


SELECT pg_column_size(int2) AS int2, pg_column_size(int4) AS int4,       pg_column_size(int8) AS int8, pg_column_size(text) AS text,       pg_column_size(bpchar) AS bpchar, pg_column_size(char) AS char,       pg_column_size(bool) AS bool, pg_column_size(to_json) AS to_json,       pg_column_size(to_jsonb) AS to_jsonb,       pg_column_size(json_build_object) AS json_build_object,       pg_column_size(jsonb_build_object) AS jsonb_build_object,       octet_length(text) AS o_text, octet_length(bpchar) AS o_bpchar,       octet_length(char) AS o_char, octet_length(to_json::text) AS o_to_json,       octet_length(to_jsonb::text) AS o_to_jsonb,       octet_length(json_build_object::text) AS o_json_build_object,       octet_length(jsonb_build_object::text) AS o_jsonb_build_object  FROM (SELECT 1::int2, 1::int4, 1::int8, 1::text, 1::char, '1'::"char",        1::boolean, to_json(1), to_jsonb(1), json_build_object(1,'test'),        jsonb_build_object(1,'test')       ) AS sub


 int2 | int4 | int8 | text | bpchar | char | bool | to_json | to_jsonb | json_build_object | jsonb_build_object | o_text | o_bpchar | o_char | o_to_json | o_to_jsonb | o_json_build_object | o_jsonb_build_object------+------+------+------+--------+------+------+---------+----------+-------------------+--------------------+--------+----------+--------+-----------+------------+---------------------+----------------------    2 |    4 |    8 |    5 |      5 |    1 |    1 |       5 |       20 |                18 |                 21 |      1 |        1 |      1 |         1 |          1 |                  14 |                   13

Getting row with largest json value is simply sorting by pg_column_size(json_column) desc limit 1.

I assume you are looking for octet_length?..

Number of bytes in string

t=# with d(j) as (values('{"a":0}'),('{"a":null}'),('{"abc":0}'))select j,octet_length(j) from d;     j      | octet_length------------+-------------- {"a":0}    |            7 {"a":null} |           10 {"abc":0}  |            9(3 rows)

so max is:

t=# with d(j) as (values('{"a":0}'),('{"a":null}'),('{"abc":0}'))select j from d order by octet_length(j) desc limit 1;     j------------ {"a":null}(1 row)

I think:

Just to know the what's the biggest value:

select max(pg_column_size(json)) from table;

To know the ID of the biggest value:

select id, pg_column_size(json)from tablegroup by idorder by max(pg_column_size(json)) desc limit 1;

Seems to work for me, but I'm not much of an expert.