blob: 8d97e50ea6f5f6ce3576c7f811c508440bf99f1f (
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
|
---
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;
```
|
