主页 / dba / 问题 / 317034
Accepted
Alex Tbk
Asked: 2022-09-18 06:07:42 +0800 CST

Postgres 11 使用 jsonb 值连接表

  • 772

我有两张桌子：

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 组合。我需要获取与给定过滤器匹配的任何这些元素。

postgresql jsonb

1 个回答

  1. Best Answer

    有许多不同的方法可以做到这一点，但最简单的可能是：

    • 加入表的条件NOT EXISTS是子查询中的任何内容：
    • 您filter使用jsonb_to_recordset
    • 然后从它使用的对象EXCEPT的突破metadatajsonb_each_text
    SELECT e.*
FROM device_group_v2 g
JOIN device_entity e
  ON NOT EXISTS (
    SELECT f."key", f.value
    FROM jsonb_to_recordset(g.filter) AS f("key" text, value text)

    EXCEPT

    SELECT md."key", md.value
    FROM jsonb_each_text(e.metadata) md
    )
WHERE g.name = 'Devices-UK';

    db<>小提琴

    如果您想要AND所有过滤器，这将为您提供结果。如果你想要OR他们，改变NOT EXISTStoEXISTS和EXCEPTtoINTERSECT

    SELECT e.*
FROM device_group_v2 g
JOIN device_entity e
  ON EXISTS (
    SELECT f."key", f.value
    FROM jsonb_to_recordset(g.filter) AS f("key" text, value text)

    INTERSECT

    SELECT md."key", md.value
    FROM jsonb_each_text(e.metadata) md
    )
WHERE g.name = 'Devices-UK';

    db<>小提琴

    • 0

相关问题