Is there a way to only pass specific jsonb properties to a SET in postgresql?-CodePudding

I'm currently trying to create a new row of data using PostgreSQL with two columns (ID, identity_data). Although, the original identity_data is a jsonb object with a lot of unnecessary properties, and I only want to include four (full_name, avatar_url, provider_id, email_verified) of the around 10 paths that are provided.

Currently, if you look at the SQL statement below, I delete the paths that I don't want to pass directly. The issue with this is that if there are any ever new paths, they won't be removed until I manually specify them for removal.

BEGIN -- Insert/Update clone of identity_data to public.identities table:
INSERT INTO public.identities (id, identity_data)
VALUES (new.id, new.identity_data::JSONB #- '{email}' #- '{iss}' #- '{sub}') ON CONFLICT (id) DO
UPDATE
SET id = excluded.id,
    identity_data = excluded.identity_data::JSONB #- '{email}' #- '{iss}' #- '{sub}';

RETURN NEW;

END;

Therefore, my question is if there's a way to explicitly select the keypaths I want to pass to the new identity_data column, instead of deleting the ones I currently know of.

CodePudding user response：

There is no operator as simple and convenient as #-, but you can write

INSERT INTO public.identities (id, identity_data)
VALUES (NEW.id, jsonb_build_object(
  'full_name', NEW.identity_data->'full_name',
  'avatar_url', NEW.identity_data->'avatar_url',
  'provider_id', NEW.identity_data->'provider_id',
  'email_verified', NEW.identity_data->'email_verified'
))
ON CONFLICT (id) DO UPDATE
SET id = excluded.id,
    identity_data = excluded.identity_data;

INSERT INTO public.identities (id, identity_data)
VALUES (NEW.id, to_jsonb(
  SELECT *
  FROM jsonb_to_record(NEW.identity_data) AS id(full_name text, avatar_url text, provider_id int, email_verified boolean)
))
ON CONFLICT (id) DO UPDATE
SET id = excluded.id,
    identity_data = excluded.identity_data;

^{(if you don't want to assert the types of the individual elements, jsonb works always)}

or (most clearly expressing the intention)

INSERT INTO public.identities (id, identity_data)
VALUES (NEW.id, (
  SELECT jsonb_object_agg(key, value)
  FROM jsonb_each(NEW.identity_data)
  WHERE key IN ('full_name', 'avatar_url', 'provider_id', 'email_verified')
))
ON CONFLICT (id) DO UPDATE
SET id = excluded.id,
    identity_data = excluded.identity_data;