检查对象键在给定jsonb阵列看起来像这样的对象
问题描述:
的JSON数组中出现两次:检查对象键在给定jsonb阵列看起来像这样的对象
[
{ "type": "foo", "desc": "example" },
{ "type": "foo", "desc": "second example" },
{ "type": "bar", "desc": "third example" }
]
我想创建如果{ "type": "foo" }
出现两次返回true的PostgreSQL的功能。
答
使用jsonb_array_elements()
,例如:
with data(js) as (
select
'[
{ "type": "foo", "desc": "example" },
{ "type": "foo", "desc": "second example" },
{ "type": "bar", "desc": "third example" }
]'::jsonb
)
select elem->>'type' as "type", count(elem->'type')
from data, jsonb_array_elements(js) elem
group by 1;
type | count
------+-------
foo | 2
bar | 1
(2 rows)
的功能应该是这样的:
create or replace function check_duplicates(source jsonb, key text)
returns boolean language sql as $$
select max(count) > 1
from (
select count(elem->key)
from jsonb_array_elements(source) elem
group by elem->key
) s
$$;
用法:
with data(js) as (
select
'[
{ "type": "foo", "desc": "example" },
{ "type": "foo", "desc": "second example" },
{ "type": "bar", "desc": "third example" }
]'::jsonb
)
select check_duplicates(js, 'type')
from data;
check_duplicates
------------------
t
(1 row)
答
这里,做一个函数。
CREATE OR REPLACE FUNCTION more_than_two_foos(s jsonb) RETURNS bool AS $$
DECLARE
c integer;
BEGIN
SELECT count(*)
FROM (
SELECT 1
FROM jsonb_array_elements(s)
WHERE value->>'type'='foo'
LIMIT 2
) t
INTO c;
RETURN c>=2;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
下面是一个例子:
$ SELECT more_than_two_foos('[
{ "type": "foo", "desc": "example" },
{ "type": "foo", "desc": "second example" },
{ "type": "bar", "desc": "third example" }
]');
more_than_two_foos
--------------------
t
(1 row)
$ SELECT more_than_two_foos('[
{ "type": "foo", "desc": "second example" },
{ "type": "bar", "desc": "third example" }
]');
more_than_two_foos
--------------------
f
(1 row)
的想法是,它通过使用jsonb_array_elements
的jsonb
数组的元素,并计算有type
等于foo
的元素。
+0
您是否看到任何方式创建索引以加快对此函数的查询? –
+0
这只是一个函数,它不会对任何表进行任何查询,所以它不会受到索引的影响。总的来说,我怀疑是否有一些索引可以用来加快搜索满足手头财产的行。 – redneb
我已经说明了每个对象都不同的例子。 –