英文:
Postgres - updating an array element in a json column
问题
我需要创建一个更新查询，为所有具有"externalSite": true的数组项添加另一个属性（在下面的示例中为'newAttribute'），因此，在运行更新查询后，第二个数组元素将变为：
{
"site": {
"code": "2",
"display": "Site2"
},
"externalSite": true,
"newAttribute": true
}
以下是更新查询的语法：
UPDATE myTable
SET data = jsonb_set(data, '{sites, ' || index || ', newAttribute}', 'true'::jsonb)
FROM (
SELECT
data,
jsonb_array_elements(data->'sites') AS site_data,
jsonb_array_length(data->'sites') AS index
FROM myTable
WHERE site_data->>'externalSite' = 'true'
) AS subquery
WHERE myTable.data->'sites' @> jsonb_build_array(subquery.site_data);
请注意，在此查询中，我们首先选择需要更新的数组项，然后使用jsonb_set函数将'newAttribute'添加到这些项中。
英文:
I have a json column in a postgres table.
The column contains the following json data:
{
"data": {
"id": "1234",
"sites": [
{
"site": {
"code": "1",
"display": "Site1"
}
},
{
"site": {
"code": "2",
"display": "Site2"
},
"externalSite": true
},
{
"site": {
"code": "3",
"display": "Site3"
}
}
]
}
}
I need to create an update query that adds another attribute ('newAttribute' in the sample below) to all array items that have '"externalSite": true', so, after running the update query the second array element will be:
{
"site": {
"code": "2",
"display": "Site2"
},
"externalSite": true,
"newAttribute": true
}
The following query returns the array elements that need to be updated:
select * from myTable, jsonb_array_elements(data -> 'sites') sites
where sites ->'externalSite' = 'true'
What is the syntax of the update query?
Thanks
Kobi
答案1
得分: 0
假设您的表名为test，列名为data，您可以像这样更新它：
UPDATE test SET data =
(SELECT jsonb_set(data::jsonb, '{"data","sites"}', sites)
FROM test
CROSS JOIN LATERAL (
SELECT jsonb_agg(CASE WHEN site ? 'externalSite' THEN site || '{"newAttribute":"true"}'::jsonb
ELSE site
END) AS sites
FROM jsonb_array_elements( (data#>'{"data","sites"}')::jsonb ) as ja(site)
) as sub
);
请注意，我将数据转换为jsonb数据，因为对jsonb的操作和运算符比普通json更多。
您可以单独运行SELECT语句以查看它的功能，但基本思想是通过使用jsonb_array_elements扩展它并在存在externalSite时添加newAttribute属性来重新创建sites对象。
然后，使用jsonb_agg对数组进行聚合，最后，在外部SELECT中，将sites对象完全替换为这个新计算的版本。
英文:
Assuming your table is called test and your column is called data, you can update it like so:
UPDATE test SET data =
(select jsonb_set(data::jsonb, '{"data","sites"}', sites)
FROM test
CROSS JOIN LATERAL (
SELECT jsonb_agg(CASE WHEN site ? 'externalSite' THEN site || '{"newAttribute":"true"}'::jsonb
ELSE site
END) AS sites
FROM jsonb_array_elements( (data#>'{"data","sites"}')::jsonb ) as ja(site)
) as sub
);
Note that I cast the data to jsonb data as there are more functions and operators available for manipulating jsonb than plain json.
You can run the SELECT statement alone to see what it is doing, but the basic idea is to re-create the sites object by expanding it with jsonb_array_elements and adding the newAttribute attribute if externalSite exists.
This array is then aggregated with jsonb_agg and, finally, in the outer select, the sites object is replaced entirely with this newly computed version.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论