英文:
Update all JSONB scoped array elements depending on type
问题
以下是您提供的内容的翻译：
我有以下的模式：
| id::UUID | payload::JSONB |
|---|---|
| 1 | [{ "edits": [{"type": "A", "value": 1 }, {"type": "B", "value": 2 }] }] |
| 2 | [{ "edits": [{"type": "A", "value": 1 }, {"type": "A", "value": 2 }] }] |
| 3 | [{ "edits": [{"type": "B", "value": 1 }] }] |
| 4 | [{ "edits": [] }] |
对于payload中的每个元素，我需要将value更新为一个对象。该对象根据类型略有不同：
CASE type = A => value: { src: <prevVal> }
CASE type = B => value: { url: <prevVal> }
对于上述示例，我期望得到：
| id::UUID | payload::JSONB |
|---|---|
| 1 | [{ "edits": [{"type": "A", "value": { "src": 1 }}, {"type": "B", "value": { "url": 2 } }] }] |
| 2 | [{ "edits": [{"type": "A", "value": { "src": 1 }}, {"type": "A", "value": { "src": 2 } }] }] |
| 3 | [{ "edits": [{"type": "B", "value": { "url": 1 } }] }] |
| 4 | [{ "edits": [] }] |
英文:
I've got the following schema:
| id::UUID | payload::JSONB |
|---|---|
| 1 | [{ "edits": [{"type": "A", "value": 1 }, {"type": "B", "value": 2 }] }] |
| 2 | [{ "edits": [{"type": "A", "value": 1 }, {"type": "A", "value": 2 }] }] |
| 3 | [{ "edits": [{"type": "B", "value": 1 }] }] |
| 4 | [{ "edits": [] }] |
For each element of payload I need to update value to be an object. That object slightly differs given the type:
CASE type = A => value: { src: <prevVal> }
CASE type = B => value: { url: <prevVal> }
For the above example, I expect to get:
| id::UUID | payload::JSONB |
|---|---|
| 1 | [{ "edits": [{"type": "A", "value": { "src": 1 }}, {"type": "B", "value": { "url": 2 } }] }] |
| 2 | [{ "edits": [{"type": "A", "value": { "src": 1 }}, {"type": "A", "value": { "src": 2 } }] }] |
| 3 | [{ "edits": [{"type": "B", "value": { "url": 1 } }] }] |
| 4 | [{ "edits": [] }] |
答案1
得分: 1
首先将payload数组扁平化，然后使用新的value重新构建它。
update the_table set payload =
(select jsonb_agg(p || jsonb_build_object(
'value', jsonb_build_object
(
case when p ->> 'type' = 'A' then 'src' else 'url' end,
(p ->> 'value')::integer
)
))
from jsonb_array_elements(payload) p
);
select * from the_table;
| id | payload |
|---|---|
| 1 | [{"type": "A", "value": {"src": 1}}, {"type": "B", "value": {"url": 2}}] |
| 2 | [{"type": "A", "value": {"src": 1}}, {"type": "A", "value": {"src": 2}}] |
| 3 | [{"type": "B", "value": {"url": 1}}] |
查看 演示。
与此无关，你的示例中 JSON 不是有效的。
英文:
First flatten the payload array and then rebuild it with the new value.
update the_table set payload =
(select jsonb_agg(p || jsonb_build_object(
'value', jsonb_build_object
(
case when p ->> 'type' = 'A' then 'src' else 'url' end,
(p ->> 'value')::integer
)
))
from jsonb_array_elements(payload) p
);
select * from the_table;
| id | payload |
|---|---|
| 1 | [{"type": "A", "value": {"src": 1}}, {"type": "B", "value": {"url": 2}}] |
| 2 | [{"type": "A", "value": {"src": 1}}, {"type": "A", "value": {"src": 2}}] |
| 3 | [{"type": "B", "value": {"url": 1}}] |
See demo.
Unrelated but JSON is not valid in your examples.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论