如何在Postgres中加入jsonb数组元素? [英] How to join jsonb array elements in Postgres?

问题描述

我正在使用Postgres 9.5，并且具有以下表格:

I am using Postgres 9.5, and I have the following tables:

用户

• id UUID
• 名称TEXT

图片

• id UUID
• 关键文本
• 宽度整数
• 高度整数

帖子

• id UUID
• 标题文字
• author_id UUID
• 内容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填充内容，例如:

Now I want to join the image type of content, and populate them with image_id, like:

{
  "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文件:

Here is my test sql file:

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 $$;

有什么办法可以实现这一要求?

Is there any way to implement this requirement?

推荐答案

假设至少使用Postgres 9.5，就可以完成这项工作:

Assuming at least Postgres 9.5, this will do the job:

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行:

1. Unnest the jsonb array, producing 1 row per array element:

jsonb_array_elements(p.content) AS c(elem)

对于在
一种. 键类型"具有值图像":c.elem->>'type' = 'image'
b. image_id中的UUID匹配:i.id = (elem->>'image_id')::uuid
^{请注意，content中的无效UUID会引发异常.}

For each element LEFT JOIN to images on the conditions that
a. The key 'type' has the value 'image': c.elem->>'type' = 'image'
b. The UUID in image_id matches: i.id = (elem->>'image_id')::uuid
^{Note that an invalid UUID in content would raise an exception.}

对于图像类型，找到匹配的图像

For image types, where a matching image was found

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

删除键'​​image_id'并将相关图像行添加为jsonb值:

remove the key 'image_id' and add the related image row as jsonb value:

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

否则保留原始元素.

使用jsonb_agg()将修改后的元素重新聚合到新的content列. ^{将使用普通的 ARRAY构造函数也是如此.}

Re-aggregate the modified elements to a new content column with jsonb_agg().
^{Would work with a plain ARRAY constructor as well.}

无条件地将LEFT JOIN LATERAL结果返回到posts并选择所有列，仅将p.content替换为生成的替换c.content

Unconditionally LEFT JOIN LATERAL the result to posts and select all columns, only replace p.content with the generated replacement c.content

在外部SELECT中，使用简单的to_jsonb()将整个行转换为jsonb.
^{jsonb_pretty()对于人类可读的表示形式是完全可选的.}

In the outer SELECT, convert the whole row to jsonb with a simple to_jsonb().
^{jsonb_pretty() is totally optional for human-readable representation.}

所有jsonb函数均在此处的手册中进行了记录.

All jsonb functions are documented in the manual here.

这篇关于如何在Postgres中加入jsonb数组元素?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！