更新嵌套JSON数组中的所有键实例
更新嵌套JSON数组中的所有键实例
提问于 2022-06-29 21:01:35
我试图更新存储在CockroachDB表中的复杂JSON文档。
除了缺乏过程代码之外，CockroachDB查询语言大多与Postgres兼容，因此没有用户函数或显式循环的Postgres回答将很可能有效。下面的示例代码也是Postgres兼容的。
使文档复杂的其他因素包括它包含嵌套数组和一些冗余结构。
该表和文件类似于以下内容：
table_name | create_statement
-------------+--------------------------------------------------------------
foo | CREATE TABLE public.foo (
id INT8 NOT NULL,
data JSONB NOT NULL
);
> insert into foo (id, data)
values(1, '{
"foo": {
"bar": {
"bar": [
{
"fields": [
{
"things": [],
"key": "something",
"value": ""
},
{
"things": [],
"key": "somethingelse",
"value": "1"
},
{
"things": [],
"key": "color",
"value": "pink"
}
]
},
{
"id": "ALL",
"fields": [
{
"things": [],
"key": "something",
"value": ""
},
{
"things": [],
"key": "somethingelse",
"value": "1"
},
{
"things": [],
"key": "color",
"value": "white"
}
]
},
{
"id": "1",
"fields": [
{
"things": [],
"key": "something",
"value": ""
},
{
"things": [],
"key": "somethingelse",
"value": "1"
},
{
"things": [],
"key": "color",
"value": "green"
}
]
},
{
"id": "ALL",
"fields": [
{
"things": [],
"key": "something",
"value": ""
},
{
"things": [],
"key": "somethingelse",
"value": "1"
},
{
"things": [],
"key": "color",
"value": "red"
}
]
}
]
}
}
}');
> select jsonb_pretty(data) from foo;
jsonb_pretty
-------------------------------------------------------
{
"foo": {
"bar": {
"bar": [
{
"fields": [
{
"key": "something",
"things": [],
"value": ""
},
{
"key": "somethingelse",
"things": [],
"value": "1"
},
{
"key": "color",
"things": [],
"value": "pink"
}
]
},
{
"fields": [
{
"key": "something",
"things": [],
"value": ""
},
{
"key": "somethingelse",
"things": [],
"value": "1"
},
{
"key": "color",
"things": [],
"value": "white"
}
],
"id": "ALL"
},
{
"fields": [
{
"key": "something",
"things": [],
"value": ""
},
{
"key": "somethingelse",
"things": [],
"value": "1"
},
{
"key": "color",
"things": [],
"value": "green"
}
],
"id": "1"
},
{
"fields": [
{
"key": "something",
"things": [],
"value": ""
},
{
"key": "somethingelse",
"things": [],
"value": "1"
},
{
"key": "color",
"things": [],
"value": "red"
}
],
"id": "ALL"
}
]
}
}
}如何将名为color的键的所有实例的值设置为空字符串("")？在嵌套数组上处理这一问题的语法使我无法理解。
回答 1
Database Administration用户
发布于 2022-06-30 19:05:45
对于处理JSON，前一个问题应该和官方CockroachDB文档一样有帮助。下面是一个更改JSON对象数组键值的示例，尽管嵌套数组可能需要更多的工作。
SELECT jsonb_agg(updated_jsonb)
FROM jsonb_array_elements('[{"key": 1}, {"key": 2}]'::JSON) individual_object,
LATERAL jsonb_set(individual_object, '{key}', '"foo"') updated_jsonb;
jsonb_agg
------------------------------------
[{"key": "foo"}, {"key": "foo"}]页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/313919
复制相关文章
相似问题
腾讯云开发者
