sql - 将包含 JSONB 字符串数组的行拆分为两个不同的行 (PostgreSQL)

Question

给定一行看起来像这样(PostgreSQL 10 和 11):

CREATE TABLE examples (
    "id" varchar NOT NULL,
    "type" varchar NOT NULL,
    "relation_id" varchar NOT NULL,
    "things" jsonb,
    PRIMARY KEY ("id")
);

INSERT INTO examples(id, type, relation_id, things) values
('7287b283-f2d8-4940-94ae-c8253599d479', 'letter-number', 'relation-id-1', '["A", "B", "1", "2", "C"]');
INSERT INTO examples(id, type, relation_id, things) values
('7287b283-f2d8-4940-94ae-c8253599d480', 'letter-number', 'relation-id-2', '["A", "2", "C"]');
INSERT INTO examples(id, type, relation_id, things) values
('7287b283-f2d8-4940-94ae-c8253599d481', 'letter-number', 'relation-id-3', '[]');

您将如何继续将这些行拆分为:

'7287b283-f2d8-4940-94ae-c8253599d480', 'relation-id-1', 'number', '["2"]'

'7287b283-f2d8-4940-94ae-c8253599d482', 'relation-id-1', 'letter', '["A", "C"]'。

本质上是拆分“type”字段，有条件地划分jsonb数组。

假设:

1. JSONB 中的值始终存在(它们可以是空数组)但它们的结构始终如此。
2. 同一张表中还有其他类型。
3. 保持 relation_id 至关重要。
4. JSONB 数组中的值是已知的，它们只有几个(比如说五六个)，所以我们可以在查询中对它们进行硬编码。
5. 更改必须持久化。可以删除/更新原始行。

https://www.db-fiddle.com/f/4VV1tZD3pBYMiCmtTFtQnj/5 <- 数据库 fiddle 。

我尝试使用子查询来处理 json_array_elements 和 INSERT ... SELECT ，但现在我无处可去。

Answer 1

Click: demo:db<>fiddle

SELECT
    id,
    relation_id,
    CASE WHEN elems ~ '[0-9]' THEN 'number' ELSE 'letter' END AS type,  -- 2
    jsonb_agg(elems)                                   -- 3
FROM
    examples,
    jsonb_array_elements_text(things) elems            -- 1
GROUP BY 
    1,2,3                                              -- 3

1. 将数组扩展为每个元素一行
2. 使用正则表达式检查元素是否为数字。如果是，则创建新的类型编号，否则为字母
3. 按此新类型分组并重新聚合元素

---

带有扩展:

• 用拆分行更新表(删除旧的，插入新的)
• 空数组生成两种类型的行

Click: demo:db<>fiddle

WITH del AS (
    DELETE FROM examples
    RETURNING id, relation_id, type, things
)
INSERT INTO examples
SELECT
    id || '_' || type,
    relation_id, 
    type, 
    COALESCE(jsonb_agg(elems) FILTER (WHERE elems IS NOT NULL), '[]')
FROM (
    SELECT
        id,
        relation_id,
        CASE WHEN elems ~ '[0-9]' THEN 'number' ELSE 'letter' END AS type,
        elems
    FROM
        del,
        jsonb_array_elements_text(things) elems

    UNION ALL

    SELECT 
        id,
        relation_id,
        t,
        null
    FROM
        examples,
        unnest(array['number', 'letter']) as t
    WHERE things = '[]'
) s
GROUP BY 1,2,3;