blob: 0d7e08745d76d14f1b3112b1dafce17d2d4212aa (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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 %}
|
