如何计算PostgreSQL中JSON的setof/键数? [英] How to count setof / number of keys of JSON in postgresql?

问题描述

我在jsonb中有一列存储一个地图，就像{'a':1,'b':2,'c':3}一样，其中每一行的键数都不同.

I have a column in jsonb storing a map, like {'a':1,'b':2,'c':3} where the number of keys is different in each row.

我想算一下-jsonb_object_keys可以检索密钥，但是它在setof

I want to count it -- jsonb_object_keys can retrieve the keys but it is in 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

交叉表不可行，因为密钥数量可能很大.

Crosstab isn't feasible as the number of key could be large.

推荐答案

您可以将键转换为数组，并使用array_length来获取:

You could convert keys to array and use array_length to get this:

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

如果需要获取整个表的内容，则可以按主键进行分组.

If you need to get this for the whole table, you can just group by primary key.

这篇关于如何计算PostgreSQL中JSON的setof/键数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！