Updates a specific jsonb property

update_jsonb.sql

-- https://www.db-fiddle.com/f/e8aeGk7cRNYnpjsqi1ncrs/24

create table tenants (id int, identities jsonb);

insert into tenants (id, identities) values (
  1,
  '[
        {
            "source": "my.edgecast.com",
            "external_id": "77"
        },
        {
            "source": "wholesale.edgecast.com",
            "external_id": "123"
        }
    ]'
);

insert into tenants (id, identities) values (
  2,
  '[
        {
            "source": "wholesale.edgecast.com",
            "external_id": "456"
        }
    ]'
);



-- get list of tenants
select * from tenants;


-- get the index of array item that matches where clause
select id as tenant_id, index-1 as index
  from tenants
      , jsonb_array_elements(identities) with ordinality arr(source, index)
-- uses `source` from ordinality function
where source->>'source' = 'wholesale.edgecast.com'
and source->>'external_id' = '456';
 

-- now update the tenant
with tenant_identity as (
  -- construct json array index syntax from example above
  -- you'll want to pass in your condition into the where clause into cte
  select 
  	id as tenant_id
  , ('{'||index-1||',external_id}')::text[] as path
    from tenants
        , jsonb_array_elements(identities) with ordinality arr(source, index)
  where source->>'source' = 'wholesale.edgecast.com'
  and source->>'external_id' = '456'
)
-- perform update here
update tenants
-- pass in current identities obj
-- pass in the path to the `external_id` attribute that we built in the cte
-- pass in the new value `789`
set identities = jsonb_set(identities, tenant_identity.path, '789', false)
    from tenant_identity;


select * from tenants;