如何在PostgreSQL中计算JSON的setof /键数？

Question

我在jsonb中有一个保存映射的列，如{'a':1,'b':2,'c':3}，其中每行键的数量不同。

我想对其进行计数-- jsonb_object_keys可以检索键，但它是setof类型

是否有类似于此的东西？

(select count(jsonb_object_keys(obj) from XXX )

（这样做不会起作用，因为 ERROR: set-valued function called in context that cannot accept a set）

Postgres JSON 函数和操作文档

json_object_keys(json)
jsonb_object_keys(jsonb)

setof text  Returns set of keys in the outermost JSON object.
json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')  

json_object_keys
------------------
f1
f2

由于键的数量可能很大，交叉表并不可行。

- vincentlcy

Answer 1

最短的：

SELECT count(*) FROM jsonb_object_keys('{"a": 1, "b": 2, "c": 3}'::jsonb);

返回 3

如果你想获得表格中所有 JSON 键的数量，可以使用以下代码：

SELECT (SELECT COUNT(*) FROM jsonb_object_keys(myJsonField)) nbr_keys FROM myTable;

编辑：第二个例子有一个拼写错误。

- Le Droid

---

我得到了 function json_object_keys(character varying) does not exist 的错误提示。 提示：没有找到匹配给定名称和参数类型的函数。您可能需要添加显式类型转换。 - ericn

@ericn 我使用的是 PostgreSQL v12，你用的是哪个版本？ - Le Droid

@ericn 在 Amazon Redshift 上执行 "select version();" 命令可以获取 PG 版本。文档中指出，它不支持所有 9.x 版本的功能，其中包括 Json 实现。参考链接：https://docs.aws.amazon.com/redshift/latest/dg/r_VERSION.html - Le Droid

Answer 2

你可以将键转换为数组，并使用array_length来获取这个结果：

select array_length(array_agg(A.key), 1) from (
    select json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') as key
) A;

如果您需要针对整个表格进行此操作，只需按主键分组即可。

- hruske

---

我得到了 function json_object_keys(character varying) does not exist 的错误提示。 提示：没有找到匹配给定名称和参数类型的函数。您可能需要添加显式类型转换。; - ericn

感谢 @LeDroid，我实际上使用 Amazon Redshift。 - ericn

Answer 3

虽然必须使用子查询将JSON键集转换为行，但以下经过微调的查询可能会更快地跳过构建临时数组：

SELECT count(*) FROM
   (SELECT jsonb_object_keys('{"a": 1, "b": 2, "c": 3}'::jsonb)) v;

并且它有点更短 ;)

将其变为一个函数：

CREATE OR REPLACE FUNCTION public.count_jsonb_keys(j jsonb)
  RETURNS bigint
  LANGUAGE sql
AS $function$
SELECT count(*) from (SELECT jsonb_object_keys(j)) v;
$function$

- mindex

Answer 4

另一种方法是直接将键值的上限作为数组返回：

SELECT
    ARRAY_UPPER( -- Grab the upper bounds of the array
        ARRAY( -- Convert rows into an array.
            SELECT JSONB_OBJECT_KEYS(obj)
        ),
        1 -- The array's dimension we're interested in retrieving the count for
    ) AS count
FROM
    xxx

使用'{"a": 1, "b": 2, "c": 3}'::jsonb作为obj，计数将得到值为三(3)。

可粘贴的示例：

SELECT
    ARRAY_UPPER( -- Grab the upper bounds of the array
        ARRAY( -- Convert rows into an array.
             SELECT JSONB_OBJECT_KEYS('{"a": 1, "b": 2, "c": 3}'::jsonb)
        ),
        1 -- The array's dimension we're interested in retrieving the count for
    ) AS count

- Joshua Burns

Answer 5

为了简化工作，你可以按照以下方式定义一个函数：

CREATE OR REPLACE FUNCTION json_map_length(json) RETURNS integer AS
$$ SELECT array_length(array_agg(A.key), 1) FROM (
  SELECT json_object_keys($1) as key
) A; $$
LANGUAGE sql IMMUTABLE;

- Tom Yeh