选择PostgreSQL JSONB列中给定键的条目数。

Question

选择PostgreSQL JSONB列中给定键的条目数。

3

我有一个包含以下示例内容的jsonb列：

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

我想找到所有'myMap'中条目数量大于/等于/小于某个整数的行--我有几百万这样的行，因此如果可以使用索引也会很有帮助！

在上面的示例中，'myMap'中有4个条目。因此，对于像“select * from myTable where jsonb_key_length(myJsonbColumn-> 'myMap') = 4”这样的查询，上面的行应该被返回。[假设有一个返回给定json对象长度的函数jsonb_key_length()]

我在这里找到了类似的问题：Postgres json key count。

但是，它需要键的名称，是否可以不使用键名来完成？

解决方案

感谢@jmelesky的建议。

以下查询对我有效：

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

包括@jmelesky的建议

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

如果有更好的建模方法，请提出建议。谢谢！

- Tanmay Baid

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jmelesky · Accepted Answer

有一个称为 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 中进行测试用例。