我有emails表sender和reporter列。我想在这些列中搜索给定参数并返回唯一值。
让我用示例来解释。这是我的表和记录：
CREATE TABLE public.emails (
id bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
(MAXVALUE 9223372036854775807),
sender jsonb NOT NULL,
reporter jsonb not null
);
insert into emails (sender, reporter) VALUES ('[{"email": "[email protected]", "name": "dennis1"}]', '[]');
insert into emails (sender, reporter) VALUES ('[{"email": "[email protected]", "name": "dennis1"}]', '[{"email": "[email protected]", "name": "john"}, {"email": "[email protected]", "name": "dennis1"}, {"email": "[email protected]", "name": "dennis2"}]');
insert into emails (sender, reporter) VALUES ('[{"email": "[email protected]", "name": "dennis1"}]', '[]');
insert into emails (sender, reporter) VALUES ('[{"email": "[email protected]", "name": "dennis1"}]', '[]');
我想获取电子邮件地址和名称。我也想避免被骗。只有一封电子邮件和一个名字。我也不想将其作为数组，而是每行一封电子邮件和一个名称。
johnSELECT
* /* i don't know what to put here pr merge with reporters */
FROM "emails" AS "e"
WHERE (EXISTS (SELECT
*
FROM JSONB_ARRAY_ELEMENTS_TEXT("e"."sender") AS "e" ("email")
WHERE ("e"."email" ~* 'john' or "e"."name" ~* 'john'))
);
john：email name
[email protected] john
SELECT
* /* i don't know what to put here pr merge with reporters */
FROM "emails" AS "e"
WHERE (EXISTS (SELECT
*
FROM JSONB_ARRAY_ELEMENTS_TEXT("e"."sender") AS "e" ("email")
WHERE ("e"."email" ~* '' or "e"."name" ~* ''))
);
email name
[email protected] john
[email protected] dennis1
[email protected] dennis2
dennis2在这两个sender和reporter，因此，只需要其中的一个。没有骗子。
实际上，这里有一个陷阱。如果sender或reporter列至少有一个json对象（不是json数组），则此查询也会失败。
cannot extract elements from an object这是另一个故事。
在这种情况下，我如何实现我的目标？
在搜索之前将数据标准化，然后使用distinct on ()子句删除重复项：
with cte as (select x ->> 'name' as name, x ->> 'email' as email
from emails as e, jsonb_array_elements(e.sender || e.reporter) as x)
select distinct on (email) * from cte where
name ~* '' or email ~* ''
--name ~* 'john' or email ~* 'john'
order by email;
请注意，它将始终扫描整个表，在这种情况下将不应用任何索引。考虑架构规范化。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句