问题[jsonb](dba)

我很难弄清楚这个加入。我有两个相关的表：

Reservations

 id | data                 
----+------------
  1 | something

CalendarEvents

 id |                 relates_to                                      | time
----+-------------------------------------------------------------------------------
  1 | {"type:": "", "relations": [{"#type": "", "relatesToId": "1"}]} | "2022-02-02"

我想选择 Reservations 和所有通过 id 匹配它们的 CalendarEvents，像这样（简化）

SELECT r, ce FROM reservations r
LEFT JOIN calendar_events ce ON r.id = ce.relates_to.relations.relatesToId
WHERE ce.time > '2022-01-01';

relates_to在jsonb中

我有两张桌子：

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

c.data是jsonb对象，像这样：{programs:[{...},{...}]}

c.data->'programs'是 jsonb 数组，像这样[{a:1},{b:2},{c:3,d:4}]或null

已经尝试过这个：

SELECT p
FROM Clients c , jsonb_array_elements(c.data->'programs') p

或这个

SELECT p
FROM Clients c , jsonb_array_elements(COALESCE(c.data->'programs','[]'::JSONB)) p

但它显示一个错误：

[22023] ERROR: cannot extract elements from a scalar

如何将里面的对象检索为对象data->'programs'行？

有没有办法构造一个只有一个数值的jsonb？

例如，

SELECT pg_typeof(('{"a":1}'::jsonb) -> 'a');

表示('{"a":1}'::jsonb) -> 'a'jsonb 类型，并且只包含一个数值1。

但是如何在不构造和破坏对象的情况下直接创建一个1jsonb呢？

直接类型转换似乎不起作用：

# SELECT 1::jsonb;
ERROR:  cannot cast type integer to jsonb
LINE 1: SELECT 1::jsonb;