1 files changed, 50 insertions, 0 deletions
diff --git a/content/posts/2020/2020-12-15-merging-json-in-postgresql.md b/content/posts/2020/2020-12-15-merging-json-in-postgresql.md
new file mode 100644
index 0000000..8d97e50
--- /dev/null
+++ b/content/posts/2020/2020-12-15-merging-json-in-postgresql.md
@@ -0,0 +1,50 @@
+---
+title: Merging JSON in PostgreSQL
+date: 2020-12-15
+tags:
+- JSON
+- PostgreSQL
+- Programming
+social:
+  email: mailto:~tyil/public-inbox@lists.sr.ht&subject=Merging JSON objects in PostgreSQL
+---
+
+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:
+
+```sql
+UPDATE user SET properties = jsonb_merge(properties, '{"notifications": {"new_case": false, "new_document": true}}');
+```
+
+And this is the eventual function I produced to do it:
+
+```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;
+```