Useful JSONB PG functions for transforming JSONB objects to property bags, and vice-versa

jsonb_object_to_property_bag.pgsql

--# :down

DROP FUNCTION IF EXISTS jsonb_object_to_property_bag(jsonb, text, text)
;

--#

--# :up

CREATE FUNCTION jsonb_object_to_property_bag(jsonb, text = 'key', text = 'value')
RETURNS jsonb
LANGUAGE sql IMMUTABLE AS
$function$
  SELECT
     jsonb_agg(
       jsonb_build_object(
          $2, kv.key
         ,$3, kv.value
       )
     )
  FROM jsonb_each($1) kv
  ;
$function$
;

--#

jsonb_property_bag_to_object.pgsql

--# :down

DROP FUNCTION IF EXISTS jsonb_property_bag_to_object(jsonb, text, text)
;

--#

--# :up

CREATE FUNCTION jsonb_property_bag_to_object(jsonb, text = 'key', text = 'value')
RETURNS jsonb
LANGUAGE sql IMMUTABLE AS
$function$
  WITH _unnested AS
  (
     SELECT
        item->>$2              AS key
       ,i
       ,CASE item->>$3
          WHEN 'true'
          THEN to_jsonb(true)
          WHEN 'false'
          THEN to_jsonb(false)
          ELSE item->$3
        END                 AS value
     FROM jsonb_array_elements($1) WITH ORDINALITY items(item, i)
  )
  ,_reduced AS
  (
    SELECT
       key
      ,value
      ,MIN(i) AS i
    FROM _unnested
    WHERE value IS NOT NULL
    GROUP BY
       key
      ,value
  )
  ,_aggregated AS
  (
    SELECT
       key
      ,jsonb_agg(value ORDER BY i) AS values
    FROM _reduced
    GROUP BY
       key
  )
  SELECT
     jsonb_object_agg(
        key
       ,CASE
          WHEN values @> jsonb_build_array(true)
          THEN to_jsonb(true)
          WHEN values @> jsonb_build_array(false)
          THEN to_jsonb(false)
          ELSE values->0
        END
     )
  FROM _aggregated
  ;
$function$
;

--#