PostgresSQL嵌套jsonb更新复杂键值对的值

Question

从JSONB数据类型开始，我希望有人能帮助我。

我有一个表(属性))，包含两列(id)作为主键和datajsonb)。结构:

{
"ProductType": "ABC",
"ProductName": "XYZ",
"attributes": [
{
"name": "Color",
"type": "STRING",
"value": "Silver"
},
{
"name": "Case",
"type": "STRING",
"value": "Shells"
},
...
]
}

我想更新值

属性name对于给定id的行。例如，对于具有name"="Case">的元素，将值更改为"Glass">。最后是

{
"ProductType": "ABC",
"ProductName": "XYZ",
"attributes": [
{
"name": "Color",
"type": "STRING",
"value": "Silver"
},
{
"name": "Case",
"type": "STRING",
"value": "Glass"
},
...
]
}

这是可能的与此结构使用SQL吗?

我已经创建了表格结构，如果你们有谁想试试的话。dbfiddle

Answer 1

使用jsonb连接操作符||来动态替换键:

WITH properties (id, data) AS (
values 
(1, '{"ProductType": "ABC","ProductName": "XYZ","attributes": [{"name": "Color","type": "STRING","value": "Silver"},{"name": "Case","type": "STRING","value": "Shells"}]}'::jsonb), 
(2, '{"ProductType": "ABC","ProductName": "XYZ","attributes": [{"name": "Color","type": "STRING","value": "Red"},{"name": "Case","type": "STRING","value": "Shells"}]}'::jsonb)
)
SELECT id,
data||
jsonb_build_object(
'attributes',
jsonb_agg(
case 
when attribs->>'name' = 'Case' then attribs||'{"value": "Glass"}'::jsonb
else attribs
end
)
) as data
FROM properties m
CROSS JOIN LATERAL JSONB_ARRAY_ELEMENTS(data->'attributes') as a(attribs)
GROUP BY id, data

更新小提琴