postgresql, jsonb field, array append via jsonb_set and jsonb_array_length

Question

I have postgresql with jsonb field that always contains array. I need to append new values to that array or update already existing values by index.

Looks like jsonb_set function meet my requirements. And for append new element i just need to max array index and update element with it. But i have a trouble doing this. Lets make it step by step. We have table campaigns with jsonb field team_members.

 select id, jsonb_set(team_members, '{0}', '{"name" : "123"}') from campaigns;
 id  |     jsonb_set     
 -----+-------------------
 102 | [{"name": "123"}]

Okay great, if set path '{0}' statically everything works. Lets do that dynamically

SQL for getting array length (it is our index for append)

  select  '{' || jsonb_array_length(team_members) || '}'::text from campaigns;
  ?column? 
  ----------
  {0}

Getting all together

   select jsonb_set(team_members, '{' || jsonb_array_length(team_members) || '}', '{"name" : "123"}') from campaigns;

ERROR: function jsonb_set(jsonb, text, unknown) does not exist LINE 1: select jsonb_set(team_members, '{' || jsonb_array_length(tea... ^ HINT: No function matches the given name and argument types. You might

need to add explicit type casts.

My question is - how can i get rid of this error ? What i'm doing wrong ?

Thanks in advance.

Answer 1

something like this?..

t=# with jpath as (select concat('{',0,'}')::text[] path) select jsonb_set('[]'::jsonb,path,'{"name": "123"}'::jsonb) from jpath;
     jsonb_set
-------------------
 [{"name": "123"}]
(1 row)

In your case should be like:

select 
  jsonb_set(
    team_members
  , concat('{',jsonb_array_length(team_members),'}')::text[]
  , '{"name" : "123"}'
  ) 
from campaigns;