Literal SQL works: Array value must start with "{" or dimension information-CodePudding

I am trying to add an ARRAY to an existing jsonb ARRAY. This array will be added to the ARRAY[0] of the existing array. When I hardcode the details it's working but when I try to do it dynamically it fails with the above error. what am I doing wrong?

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]: ERROR: malformed array literal: "ARRAY['userProfile','0','DocumentDetails']" Detail: Array value must start with "{" or dimension information.

This is the with subquery output.

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'']');

OUTPUT OF THE ABOVE SQL

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

But when I pass the value as a literal to the above SQL it works just fine.

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;

CodePudding user response：

You shouldn't put the ARRAY[…] syntax in a literal value.

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;

CodePudding user response：

The query you are trying is broken beyond the superficial syntax error (which is addressed by Bergi).

If the CTE returns multiple rows (as expected), the ARRAY constructor will fail because the nested subselect is only allowed to return a single value in this place.

It would seem like you want to add the property "DocumentDetails": [{"y": "cccValue"}]} to every element of the nested JSON array user_details->'userProfile'. If so, use this query instead:

UPDATE users
SET    user_details
     = jsonb_set(user_details
               , '{userProfile}'
               , (SELECT jsonb_agg(value || '{"DocumentDetails": [{"y":"cccValue"}]}')
                  FROM   jsonb_array_elements(user_details -> 'userProfile') ja
                 )
                )
WHERE  display_ok = FALSE;

db<>fiddle here

The correlated subquery unnests the array, adds the property to every element object (or updates it if already there, and aggregates the array back.
jsonb_set() then replaces the old nested array with the updated one.