Postgres/jOOQ替换jsonb[]元素

Question

我有一个带有jOOQ和Postgresql数据库的Spring应用程序，它有一个包含以下两列的表（问题）：

id (Long)
documents (jsonb[]) <- array of jsonb (not jsonb array)

文档json结构采用以下格式：

{
   "id": (UUID),
   "name": (String),
   "owner"; (String)
}

我想实现的是能够用一个新文档替换具有匹配id的文档（通常只有一个）。我正在与jOOQ甚至普通SQL作斗争。

我想我需要在jOOQ中编写一些简单的SQL才能做到这一点，但这是可以的（至少）。我有一个想法要做以下事情：

1. 取消对文档列的测试
2. 过滤出数组中应更新的文档
3. 附加应更新的文档
4. 存储整个阵列

原始SQL看起来像这样，但缺少要添加的新文档：

UPDATE issues SET documents = (SELECT ARRAY_AGG(doc) FROM issues, UNNEST(issues.documents) AS doc WHERE doc->>'id' != 'e4e3422f-83a4-493b-8bf9-37980d532538') WHERE issues.id = 1;

我的最终目标是在jOOQ中编写这篇文章，并附加要替换的文档。我使用的是jOOQ 3.11.4。

Answer 1

您应该能够在PostgreSQL中连接数组：

UPDATE issues 
SET documents = (
  SELECT ARRAY_AGG(doc) || '{"id":"e4e3422f-83a4-493b-8bf9-37980d532538","name":"n"}'::jsonb
  FROM issues, UNNEST(issues.documents) AS doc 
  WHERE doc->>'id' != 'e4e3422f-83a4-493b-8bf9-37980d532538'
)
WHERE issues.id = 1

一些常见的数组函数将在不久的将来添加到jOOQ中，例如数组连接，但我怀疑您现在可以使用普通的SQL模板吗？