Skip to content

Instantly share code, notes, and snippets.

@jackross
Last active April 22, 2021 11:39
Show Gist options
  • Save jackross/556c0051f3eb95d9ffee801f7758aa2d to your computer and use it in GitHub Desktop.
Save jackross/556c0051f3eb95d9ffee801f7758aa2d to your computer and use it in GitHub Desktop.
Useful JSONB PG functions for transforming JSONB objects to property bags, and vice-versa
--# :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$
;
--#
--# :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$
;
--#
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment