如何在Postgres中合并jsonb数组元素？

Question

我正在使用Postgres 9.5，以下是我的表格：

Users

• id UUID
• name TEXT

Images

• id UUID
• key TEXT
• width INTEGER
• height INTEGER

Posts

• id UUID
• title TEXT
• author_id UUID
• content JSONB

帖子的内容如下：

[
  { "type": "text", "text": "learning pg" },
  { "type": "image", "image_id": "8f4422b4-3936-49f5-ab02-50aea5e6755f" },
  { "type": "image", "image_id": "57efc97c-b9b4-4cd5-b1e1-3539f5853835" },
  { "type": "text", "text": "pg is awesome" }
]

现在我想加入图片类型的内容，并填充它们与image_id，例如：

{
  "id": "cb1267ca-b1ac-4daa-8c7e-72d4c000e9fa",
  "title": "Learning join jsonb in Postgres",
  "author_id": "deba01b7-ec58-4cc2-b3ae-7dc42e582767",
  "content": [
    { "type": "text", "text": "learning pg" },
    {
       "type": "image",
       "image": {
         "id": "8f4422b4-3936-49f5-ab02-50aea5e6755f",
         "key": "/upload/test1.jpg",
         "width": 800,
         "height": 600
       }
    },
    {
       "type": "image",
       "image": {
         "id": "57efc97c-b9b4-4cd5-b1e1-3539f5853835",
         "key": "/upload/test2.jpg",
         "width": 1280,
         "height": 720
       }
    },
    { "type": "text", "text": "pg is awesome" }
  ]
}

这是我的测试SQL文件：

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Images;
DROP TABLE IF EXISTS Posts;

CREATE TABLE Users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name text NOT NULL
);

CREATE TABLE Images (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  key TEXT,
  width INTEGER,
  height INTEGER,
  creator_id UUID
);

CREATE TABLE Posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT,
  author_id UUID,
  content JSONB
);


DO $$
DECLARE user_id UUID;
DECLARE image1_id UUID;
DECLARE image2_id UUID;

BEGIN

INSERT INTO Users (name) VALUES ('test user') RETURNING id INTO user_id;
INSERT INTO Images (key, width, height, creator_id) VALUES ('upload/test1.jpg', 800, 600, user_id) RETURNING id INTO image1_id;
INSERT INTO Images (key, width, height, creator_id) VALUES ('upload/test2.jpg', 600, 400, user_id) RETURNING id INTO image2_id;
INSERT INTO Posts (title, author_id, content) VALUES (
  'test post',
  user_id,
  ('[ { "type": "text", "text": "learning pg" }, { "type": "image", "image_id": "' || image1_id || '" }, { "type": "image", "image_id": "' || image2_id || '" }, { "type": "text", "text": "pg is awesome" } ]') :: JSONB
);

END $$;

是否有实现这个要求的方法？

- user2331095

---

如果您发布实际的表定义（CREATE TABLE语句），我们可以使用它们来重现问题，这将更有用，而不是伪代码。并且请务必注明您使用的Postgres版本。 - Erwin Brandstetter

@ErwinBrandstetter 感谢您的建议，我已将SQL语句添加到问题中。 - user2331095

Answer 1

SELECT jsonb_pretty(to_jsonb(p)) AS post_row_as_json
FROM  (
   SELECT id, title, author_id, c.content
   FROM   posts p
   LEFT   JOIN LATERAL (
      SELECT jsonb_agg(
               CASE WHEN c.elem->>'type' = 'image' AND i.id IS NOT NULL
                    THEN elem - 'image_id' || jsonb_build_object('image', i)
                    ELSE c.elem END) AS content
      FROM   jsonb_array_elements(p.content) AS c(elem)
      LEFT   JOIN images i ON c.elem->>'type' = 'image'
                          AND i.id = (elem->>'image_id')::uuid
      ) c ON true
   ) p;

如何做？

1. 将 jsonb 数组展开，每个数组元素生成1行：

   jsonb_array_elements(p.content) AS c(elem)
   

2. 对于每个元素，在条件上使用LEFT JOIN到images，条件为：

   • ... 键值 'type' 的取值为'image'：c.elem->>'type' = 'image'
   • ...image_id中的UUID匹配：i.id = (elem->>'image_id')::uuid
     如果content中有无效的UUID将引发异常。

3. 对于图像类型，如果找到匹配的图像

c.elem->>'type' = 'image' AND i.id IS NOT NULL

移除键名为'image_id'的键，并将相关图片行添加为jsonb值：

elem - 'image_id' || jsonb_build_object('image', i)

否则保留原始元素。

4. 使用jsonb_agg()重新聚合修改后的元素到一个新的content列中。
   也可以使用普通的数组构造器。

5. 无条件地将结果左连接到posts上，并选择所有列，只将 p.content替换为生成的替换内容c.content

6. 在外部SELECT中，使用简单的to_jsonb()将整行转换为jsonb。
   jsonb_pretty()仅用于人类可读的表示，完全是可选的。

所有jsonb函数都在手册中有说明。

- Erwin Brandstetter

---

感谢您指出使用 jsonb_array_elements 的问题 - 这帮助我找到了解决我们略微相关的问题的方法。 - shawmanz32na