sql - 如何根据对象 ID 将 jsonb 数组展开为每个 jsonb 列的对象？

Question

给定类似于以下的现有数据结构:

CREATE TEMP TABLE sample (id int, metadata_array jsonb, text_id_one jsonb, text_id_two jsonb);
INSERT INTO sample
VALUES ('1', '[{"id": "textIdOne", "data": "foo"},{"id": "textIdTwo", "data": "bar"}]'), ('2', '[{"id": "textIdOne", "data": "baz"},{"id": "textIdTwo", "data": "fiz"}]');

我正在尝试将现有 元数据 列中的 jsonb 对象数组展开到同一表中的新 jsonb 列中；我已经根据已知的固定 ID 键列表创建了 textIdOne、textIdTwo 等

我以为我已经接近使用 jsonb_populate_recordset() 但后来意识到将根据所有 jsonb 对象的键填充列；不是我想要的。期望的结果是基于对象 ID 的每列对象。

此操作的另一个棘手部分是我的 JSON 对象的 id 值使用 camelCase 并且似乎应该避免使用引号/大小写的列名称，但我没有不介意引用或修改列名作为结束的手段 & 一旦更新查询完成，我可以根据需要手动更改列名。

我正在使用 PostgreSQL 9.5.2

现有数据和结构:

id | metadata_array jsonb                             | text_id_one jsonb | text_id_two jsonb
---------------------------------------------------------------------------------------------
1  | [{"id": "textIdOne"...}, {"id": "textIdTwo"...}] | NULL              | NULL
2  | [{"id": "textIdOne"...}, {"id": "textIdTwo"...}] | NULL              | NULL

期望的结果:

id | metadata_array jsonb     | text_id_one jsonb      | text_id_two jsonb
-------------------------------------------------------------------------------
1  | [{"id": "textIdOne",...  | {"id": "textIdOne"...} | {"id": "textIdTwo"...}
2  | [{"id": "textIdOne",...  | {"id": "textIdOne"...} | {"id": "textIdTwo"...}

---

说明:

感谢大家到目前为止的回答!虽然我知道完整的键列表(大约 9 个)，但我不能指望顺序是一致的。

Answer 1

如果所有 json 数组都包含两个新列的两个元素，则使用 dmfay 的答案中的固定路径。否则你应该使用 jsonb_array_elements() 取消嵌套数组两次，分别用于 text_id_one 和 text_id_two。

update sample t set
    text_id_one = value1,
    text_id_two = value2
from sample s, 
    jsonb_array_elements(s.metadata_array) as e1(value1),
    jsonb_array_elements(s.metadata_array) as e2(value2)
where s.id = t.id
    and value1->>'id' = 'textIdOne'
    and value2->>'id' = 'textIdTwo'
returning t.*

Test the query in SqlFiddle.

如果数组的元素超过两个，这个变体可能更有效(也更方便):

update sample t
set 
    text_id_one = arr1->0,
    text_id_two = arr2->0
from (
    select 
        id,
        jsonb_agg(value) filter (where value->>'id' = 'textIdOne') as arr1,
        jsonb_agg(value) filter (where value->>'id' = 'textIdTwo') as arr2
    from sample,
    jsonb_array_elements(metadata_array)
    group by id
    ) s
where t.id = s.id
returning t.*

SqlFiddle.