---
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;
```