From 224b7c7a557b6060535fe127de7be29d5142d568 Mon Sep 17 00:00:00 2001
From: Patrick Spek <p.spek@tyil.nl>
Date: Thu, 1 Apr 2021 07:55:09 +0200
Subject: Two posts, lost forever on my old desktop

rip sessifet.tyil.net
---
 .../2020-12-15-merging-json-in-postgresql.md       | 50 ++++++++++++++++++++++
 1 file changed, 50 insertions(+)
 create mode 100644 src/_posts/2020-12-15-merging-json-in-postgresql.md

(limited to 'src/_posts/2020-12-15-merging-json-in-postgresql.md')

diff --git a/src/_posts/2020-12-15-merging-json-in-postgresql.md b/src/_posts/2020-12-15-merging-json-in-postgresql.md
new file mode 100644
index 0000000..0d7e087
--- /dev/null
+++ b/src/_posts/2020-12-15-merging-json-in-postgresql.md
@@ -0,0 +1,50 @@
+---
+layout: post
+tags: PostgreSQL Programming JSON
+social:
+  email: mailto:~tyil/public-inbox@lists.sr.ht&subject=Merging JSON objects in PostgreSQL
+description: >
+  Left unsatisfied trying to find a nice way to merge two JSON objects in
+  PostgreSQL, I whipped up my own. This small post shows off my glorious
+  concoction for future generations."
+---
+
+At my `$day-job` we have a lot of `jsonb` in our database. From time to time, I
+have to manually run a query to fix something in there. This week was one of
+those times.
+
+While you can pretty much do everything you need with regards to JSON editing
+with `jsonb_se`t, I thought it might be nice if I were able to *merge* a given
+JSON object into an existing object. This might be cleaner in some situations,
+but mostly it is fun to figure it out. And who doesn’t like spending time with
+`plpgsql`?
+
+The way I wanted to have it work is like this:
+
+{% highlight sql %}
+UPDATE user SET properties = jsonb_merge(properties, '{"notifications": {"new_case": false, "new_document": true}}');
+{% endhighlight %}
+
+And this is the eventual function I produced to do it:
+
+{% highlight sql %}
+CREATE OR REPLACE FUNCTION jsonb_merge(original jsonb, delta jsonb) RETURNS jsonb AS $$
+    DECLARE result jsonb;
+    BEGIN
+    SELECT
+        json_object_agg(
+            COALESCE(original_key, delta_key),
+            CASE
+                WHEN original_value IS NULL THEN delta_value
+                WHEN delta_value IS NULL THEN original_value
+                WHEN (jsonb_typeof(original_value) <> 'object' OR jsonb_typeof(delta_value) <> 'object') THEN delta_value
+                ELSE jsonb_merge(original_value, delta_value)
+            END
+        )
+        INTO result
+        FROM jsonb_each(original) e1(original_key, original_value)
+        FULL JOIN jsonb_each(delta) e2(delta_key, delta_value) ON original_key = delta_key;
+    RETURN result;
+END
+$$ LANGUAGE plpgsql;
+{% endhighlight %}
-- 
cgit v1.1