如何在PostgreSQL的列中查找存储的JSON大小

Question

我们正在使用Postgres。在一个表中，我们有一个JSON类型的列。

如何找到存储在特定行中的JSON大小？如何找到该列中具有最大JSON数据大小的行？

- Bravo

---

Postgres 9.1没有JSON类型。我已经删除了过时版本的标签。 - user330315

Answer 1

如果你想知道存储一列需要多少字节，那么可以使用

pg_column_size(any) - 用于存储特定值（可能已压缩）所使用的字节数

例子：

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

获取具有最大json值的行只需要按照 pg_column_size(json_column) desc limit 1 进行排序。

- Łukasz Kamiński

Answer 2

我认为：

只需要知道最大的值是什么：

select max(pg_column_size(json)) from table;

要知道最大值的ID:

select id, pg_column_size(json)
from table
group by id
order by max(pg_column_size(json)) desc limit 1;

对我来说似乎可以工作，但我不是很专家。

- ecoologic

---

选择表中最大的 JSON 列大小并进行格式化输出。 - tester

Answer 3

我猜你正在寻找octet_length函数？..https://www.postgresql.org/docs/current/static/functions-string.html

字符串中的字节数

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)

所以，最大值是：

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)

- Vao Tsun

---

因为这个只适用于字符串列，所以被踩了。问题是关于JSON列的。 - Isaac Lyman

您需要将数据类型转换为JSON类型以使用octet_length。此外，我建议您如何在Postgres 9.1中查找JSON的大小，因此无论具有有效JSON结构的文本是什么，它仍然是文本 - 而获取此类JSON结构文本字段的大小的最佳方法是我认为是 octet_length。请为@Łukasz Kamiński的答案点赞 - 他回答得很好。他还展示了JSON和jsonb之间的差异，非常完美。 - Vao Tsun

谢谢，我之前没有意识到PostgreSQL 9.1的这个问题。看起来那个标签被编辑掉了。 - Isaac Lyman