Helios de Guerra Helios de Guerra - 1 year ago 140
JSON Question

Postgresql Update JSON Column Retaining Some KeyValues and Adding Additional KeyValue as null

I am trying to use a

JSONB column for a multi-tenant application. Every
for each tenant must have the same
, but the tenants have varying

In order to keep all user metadata in sync per tenant, when the tenant admin modifies the metadata fields I need to make sure all users have their metadata JSONB column updated with the following criteria:

  1. If the metadata field/key already exists, the value needs to be retained

  2. If the metadata field/key is new, the key needs to added with a null value

  3. If there are any metadata fields/keys that are not included in the updated list, they should be deleted from the JSON object

For example, all the users for Tenant #1 have the following metadata assigned:
{ "EmployeeNo" : 123, "HireDate" : "2012-10-10", "Age" : 43 }
and somewhere down the line the admin decides they don't care about Age, but they do want to start tracking ParkingSpace.

I need the new metadata record to retain the EmployeeNo and HireDate values, remove the Age key/value, and add the ParkingSpace key with a null value.
{ "EmployeeNo" : 123, "HireDate" : "2012-10-10", "ParkingSpace" : null }

I would have thought that I could run an update query similar to the following where it returns a JSONB object where it selects the values if the key exists and a null if it doesn't:

UPDATE users SET metadata = metadata[keys: 'EmployeeNo', 'HireDate', 'ParkingSpace'] WHERE tenant_id = 1;

Obviously that won't work, but hopefully it indicates the issue?

Answer Source

Update: I might have misunderstood your question. Maybe you wanted something like that instead:

UPDATE users
SET metadata = (SELECT json_object_agg(n,metadata->>n) FROM unnest(ARRAY['EmployeeNo','HireDate','ParkingSpace']) AS t(n))

This solution involves creating a brand new jsonb object by extracting only the field that you want from the original metadata. The fields to be copied over are specified as an array that you can easily customize.

Original answer: I think this should do it:

UPDATE users
SET metadata = (metadata - 'Age') || '{"ParkingSpace": null}'::jsonb;

I am using the || operator which merges 2 jsonb objects into one and the - operator which removes a key/value pair.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download