为PostgreSQL JSONB列选择给定键内的条目数

Question

{"kay1": val1, "myMap": {"UniqueKey1": "UniqueValue1", "UniqueKey2": "UniqueValue2", "UniqueKey3": "UniqueValue3", "UniqueKey4": "UniqueValue4"}, "key2": {"key3": {"key4": "val4"}, "val3": {"key5": "val5"}}

我在这里发现了类似的问题：Postgres json键计数。

但是，它需要密钥的名称，这可以不使用密钥名称吗？

解决方案

SELECT id, count(elements)
FROM (SELECT id, jsonb_object_keys(column -> 'myMap') AS elements
      FROM myTable GROUP BY id
     ) x
GROUP BY id

SELECT id, (SELECT count(*)
            FROM (SELECT jsonb_object_keys(a->'myMap')
                  FROM test_json x where x.id = y.id
                  ) z
            ) count
FROM test_json y group by id;

SELECT id, ARRAY_LENGTH(ARRAY(SELECT jsonb_object_keys(column -> 'myMap')), 1) AS count
FROM myTable

CREATE OR REPLACE FUNCTION jsonb_object_keys_length(_j jsonb)
RETURNS INT LANGUAGE SQL IMMUTABLE AS
'SELECT ARRAY_LENGTH(ARRAY(SELECT jsonb_object_keys(column -> 'myMap')), 1)';

CREATE INDEX idx_myMapCount ON myTable (jsonb_object_keys_length(column -> 'myMap'));

在查询中使用函数：

SELECT id, jsonb_object_keys_length(column -> 'myMap') AS count
FROM myTable

如果有更好的方法来建模这个查询，请给出建议。谢了！

Answer 1

有一个名为json_object_keys的函数，它可能是这个问题的关键部分。它接受一个json对象，并将键作为关系的行返回。

=# create table test_json (a json);
CREATE TABLE
=# insert into test_json values ('{"kay1": 1, "myMap": {"UniqueKey1": "UniqueValue1", "UniqueKey2": "UniqueValue2", "UniqueKey3": "UniqueValue3", "UniqueKey4": "UniqueValue4"}, "key2": {"key3": {"key4": "val4"}, "val3": {"key5": "val5"}}}');
INSERT 0 1
=# select json_object_keys(a) from test_json;
 json_object_keys 
------------------
 kay1
 myMap
 key2
(3 rows)
=# select json_object_keys(a->'myMap') from test_json;
 json_object_keys 
------------------
 UniqueKey1
 UniqueKey2
 UniqueKey3
 UniqueKey4
(4 rows)

从那里，您可以将其封装在子查询中，如下所示：

=# select count(*) from (select json_object_keys(a->'myMap') from test_json) x;
 count 
-------
     4
(1 row)

编辑添加：有一个jsonb等效项(jsonb_object_keys)，它与jsonb值相同。对不起，我倾向于用普通的JSON来做我的测试用例。