Alex Tbk's questions

我有两张桌子：

create table device_group_v2
(
    id        uuid not null primary key,
    name      varchar(255),
    filter    jsonb
);

一个条目：

+--------------------------------------+------------+-------------------------------------+
|                  id                  |    name    |               filter                |
+--------------------------------------+------------+-------------------------------------+
| 92f9ac68-7baa-4322-8eb1-e6b9e83dd2ef | Devices-UK | [{"key": "country", "value": "uk"}] |
+--------------------------------------+------------+-------------------------------------+

和

create table device_entity
(
    id       uuid not null primary key,
    name     varchar(255),
    metadata jsonb
);

一个条目：

+--------------------------------------+---------------------+-------------------+
|                  id                  |        name         |     metadata      |
+--------------------------------------+---------------------+-------------------+
| 75cf91d0-01b7-42e0-9791-86def0dc4b7b | Device 7 - Metadata | {"country": "uk"} |
+--------------------------------------+---------------------+-------------------+

是否可以加入两个表，所以我得到所有具有在TestGroup的过滤器中定义的键/值的设备？

在给定的示例中，它是这个查询，但我需要从 device_group_v2 表中的“过滤器”列动态填充“国家”和“英国”。

SELECT * from device_entity where (metadata ->> 'country') = 'uk';

如果有多个键值条目，则所有条目都应与 OR 组合。我需要获取与给定过滤器匹配的任何这些元素。

我有一张桌子

create table device_entity
(
    id       uuid not null primary key,
    name     varchar(255),
    metadata hstore
);

此表的元数据字段中包含 'country' = 'uk' 的条目。

此查询返回元数据字段中存在键“国家”的所有条目。

SELECT * from device_entity where metadata ? 'country'

如何选择键“国家”=“英国”的所有条目？

我试图创建一个接受 uuid 列表作为参数的函数。

我的代码：

create or replace function get_child_groups(input_parents_id uuid[])
returns table(id uuid, name text, parent_id uuid)
as
$$
WITH RECURSIVE c AS (
    SELECT da.*
      from device_group_v2 d
               JOIN device_group_associations da on da.parent_id = d.id
      where d.id in (input_parents_id)

      UNION ALL

      SELECT sa.*
      FROM device_group_associations AS sa JOIN c ON c.child_id = sa.parent_id)
select * from(select d.* from c join device_group_v2 d on c.parent_id = d.id OR c.child_id = d.id) as x
union
select * from device_group_v2 where id in (input_parents_id)
$$ language sql;

这失败了：

[42883] 错误：运算符不存在：uuid = uuid[] 没有运算符与给定的名称和参数类型匹配。您可能需要添加显式类型转换。职位：290

我究竟做错了什么？那里需要一种类型转换吗？

我在 Postgres 中的递归查询有点挣扎。

鉴于这些表：

create table resource_v3
(
    id            uuid not null primary key,
    name          varchar(255),
    resource_id   uuid,
    resource_type varchar(255),
    tenant_id     uuid
);

和

create table resource_associations
(
    parent_id uuid not null
    child_id  uuid not null
    primary key (parent_id, child_id)
);

鉴于这些表，我构建了以下结构

我要存档的是获取 user1 所在的所有团队或组织。我只需要第一个匹配项，因此给定 user1 递归应该在“团队服务技术人员”和“ORG：Seepex”处停止。它不应该返回“ORG:CS”而不是“ORG:Seepex-UK”

我的查询：

WITH RECURSIVE c AS (
   SELECT v.id from resource_v3 v where v.resource_id = '8a84bf66-6dc4-43c7-ace1-d104fddaa1be'

   UNION ALL

   SELECT sa.parent_id
   FROM resource_associations AS sa JOIN c ON c.id = sa.child_id
       JOIN resource_v3 rv3 on c.id = rv3.id
    WHERE rv3.resource_type != 'TEAM' OR rv3.resource_type != 'ORGANISATION'
)
SELECT r.* FROM c JOIN resource_v3 r ON c.id=r.id;

如我所料返回更多数据：

我在这里做错了什么或者你将如何解决这个问题？似乎我几乎在那里，查询应该在第一次命中时停止向上移动。

我的最终目标是获取给定用户所在的 TEAM 或 ORGANIZATION 的所有子元素。因此，逻辑计划是使用上面的查询获取组织/团队，然后从那里递归获取子元素，这相当容易并且运作良好。