文字SQL工作：数组值必须以"{"或维度信息开头

Question

我试图添加一个数组到现有的jsonb数组。该数组将被添加到现有数组的array[0]中。当我硬编码细节时，它正在工作，但当我尝试动态地执行它时，它会因上述错误而失败。我做错了什么?

Postgresql 13 db server version

with whatposition as (select position pos from users cross join lateral 
jsonb_array_elements(user_details->'Profile') with ordinality arr(elem,position)
where display_ok=false)
update users set user_details=jsonb_set(
user_details,concat('ARRAY[''userProfile'',''',(select pos-1 from whatposition)::text,'''',',''DocumentDetails'']')::text[],
'[{"y":"supernewValue"}]')
where display_ok=false;

SQL Error [22P02]:"ARRAY [' userProfile ', ' 0 ', ' DocumentDetails ']";详细信息:数组值必须以"{"或维度信息。

这是with子查询的输出。

with whatposition as (select position pos from users cross join lateral 
jsonb_array_elements(user_details->'userProfile') with ordinality arr(elem,position)
where display_ok=false)
select concat('ARRAY[''userProfile'',''',(select pos-1 from whatposition)::text,'''',',''DocumentDetails'']');

上述SQL

的输出

ARRAY [' userProfile ', ' 0 ', ' DocumentDetails ']

但是当我将值作为字面量传递给上面的SQL时，它工作得很好。

with whatposition as (select position pos from users cross join lateral 
jsonb_array_elements(user_details->'userProfile') with ordinality arr(elem,position)
where display_ok=false)
update users set user_details=jsonb_set(
user_details,ARRAY['userProfile','0','DocumentDetails'],'[{"y":"cccValue"}]')
where display_ok=false;

Answer 1

您不应该将ARRAY[…]语法放在文字值中。

with whatposition as (
select position pos
from users
cross join lateral jsonb_array_elements(user_details->'Profile') with ordinality arr(elem,position)
where display_ok=false
)
update users
set user_details=jsonb_set(
user_details,
ARRAY['userProfile', (select pos-1 from whatposition)::text, 'DocumentDetails'],
'[{"y":"supernewValue"}]'
)
where display_ok=false;

Answer 2

您正在尝试的查询超出了表面语法错误(由Bergi解决)。

如果CTE返回多行(如预期的那样)，ARRAY构造函数将失败，因为嵌套的子选择只允许在此位置返回单个值。

"upsert"(插入或更新)属性"DocumentDetails": [{"y": "cccValue"}]}到嵌套JSON数组user_details->'userProfile'的第一个元素(下标为0的元素):

Postgres 14及以上版本

使用JSONB下标:

UPDATE users
SET    user_details['userProfile'][0]['DocumentDetails'] = '[{"y":"cccValue"}]'
WHERE  display_ok = FALSE;

Postgres 13

使用jsonb_set()-完全像您在上一个代码示例中已经使用的那样，只是没有不必要的CTE:

UPDATE users
SET    user_details = jsonb_set(user_details, '{userProfile, 0, DocumentDetails}', '[{"y":"cccValue"}]')
WHERE  display_ok = FALSE;

db<此处小提琴>