From 0a19618ae4ba2ab1dbcc243e0b91d4792349a2cd Mon Sep 17 00:00:00 2001
From: Patrick Spek
Date: Tue, 18 Jan 2022 15:50:55 +0100
Subject: Add missing articles from 2020
---
.../2020/2020-12-15-merging-json-in-postgresql.md | 50 ++++++++++++++++++++++
1 file changed, 50 insertions(+)
create mode 100644 content/posts/2020/2020-12-15-merging-json-in-postgresql.md
(limited to 'content/posts/2020/2020-12-15-merging-json-in-postgresql.md')
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;
+```
--
cgit v1.1