时间戳

首页 > 解决方案 > 通过Postgresql查询在Object的嵌套数组中插入UUID类型的元素

postgresql - 通过Postgresql查询在Object的嵌套数组中插入UUID类型的元素

问题描述

当前 JSON

{
"layout":"dynamicReport1",
"templateType":"DYNAMIC_REPORTS",
"containers":{
"fieldsContainer":[
{
"fieldName":"role_id",
"displayName":"Role",
"fieldType":"text",
"isHidden":true,
"index":0,
"queryForParam":"select name as \"role_id\" from um_role_master where id=#role_id#",
"queryIdForParam":476
},
{
"fieldName":"course_id",
"displayName":"Course",
"fieldType":"text",
"isHidden":true,
"index":1,
"queryForParam":"select course_name as course_id from tr_course_master where course_id=#course_id#",
"queryIdForParam":477
},
{
"fieldName":"location_id",
"displayName":"Location",
"fieldType":"text",
"isHidden":true,
"index":2,
"queryForParam":"select name as location_id from location_master where id = #location_id#",
"queryIdForParam":478
}
]
}
}

层次结构就像

containers -> fieldContainer -> object

以上是我的 json 配置，我想queryUUIDForParam: random UUID通过查询添加到每个对象。

我怎样才能插入？

我试图通过此查询获取更新的配置，但它会引发错误：

select config::jsonb || ('{"queryUUIDForParam":' || cast(uuid as text)  || '}')::jsonb

错误 ：

SQL 错误 [22P02]：错误：json 类型的输入语法无效详细信息：令牌“5574ff23”无效。其中：JSON 数据，第 1 行：{"queryUUIDForParam":5574ff23...

我的预期输出是在 Object Like 中添加“queryUUIDForParam”元素。我想附加一个带有 UUID 值的元素。这个 UUId 值是使用随机函数生成的。

{
"layout":"dynamicReport1",
"templateType":"DYNAMIC_REPORTS",
"containers":{
"fieldsContainer":[
{
"fieldName":"role_id",
"displayName":"Role",
"fieldType":"text",
"isHidden":true,
"index":0,
"queryForParam":"select name as \"role_id\" from um_role_master where id=#role_id#",
"queryIdForParam":476,
"queryUUIDForParam":"1ea99f17-6965-4a0d-8d31-22b8777b9c62"
},
{
"fieldName":"course_id",
"displayName":"Course",
"fieldType":"text",
"isHidden":true,
"index":1,
"queryForParam":"select course_name as course_id from tr_course_master where course_id=#course_id#",
"queryIdForParam":477,
"queryUUIDForParam":"3ea99f17-6965-4a0d-8d31-22b8777b9c62"
},
{
"fieldName":"location_id",
"displayName":"Location",
"fieldType":"text",
"isHidden":true,
"index":2,
"queryForParam":"select name as location_id from location_master where id = #location_id#",
"queryIdForParam":478,
"queryUUIDForParam":"9ea99f17-6965-4a0d-8d31-22b8777b9c62"
}
]
}
}

提前致谢 ：）

标签： postgresql

解决方案

试试这个：

with cte as (select jsonb_array_elements(jsonb_extract_path(config, 'containers','fieldsContainer')::jsonb) "objects" from example),

final_array as (

select jsonb_build_array(d) "array_data" from (select array_agg(objects::jsonb || jsonb_build_object('queryUUIDForParam',(select uuid_generate_v4()))) "fieldsContainer" from cte )d)

select jsonb_set(
        config::jsonb,
        '{containers,fieldsContainer}', (f.array_data),false) 
 from example, final_array f;

如果您想为每个对象使用不同的 uuid

with cte as (select uuid_generate_v4() "uuid_",jsonb_array_elements(jsonb_extract_path(config, 'containers','fieldsContainer')::jsonb) "objects" from example),

final_array as (

select jsonb_build_array(d) "array_data" from (select array_agg(objects::jsonb || jsonb_build_object('queryUUIDForParam',uuid_)) "fieldsContainer" from cte )d)

select jsonb_set(
        config::jsonb,
        '{containers,fieldsContainer}', (f.array_data),false) 
 from example, final_array f;

注意：我使用 Postgres 的内置函数来生成 UUID。请在使用前运行以下语句

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

DB-Fiddle上的演示

推荐阅读