[Postgres] More fun with JSON

Hello, coders! 💻

Today we're going to have a quick look at JSON support in Postgres.

What we want to do

Let's say our users can tweak some settings and we want to save them to database in JSONB format.

We'll be using a JSONB column named, you guessed it, settings.

If a user changed their landing page preference then the stored JSON would be {"landingPage":"dashboard"}.
If they changed their language to japanese then setttings would be {"landingPage": "dashboard", "language": "jp"}.

Let's see how we can tackle that. Let's define our table first.

Users table

create table users(
    id SERIAL,
    name text,
    settings jsonb default '{}'
);

Simple enough.

Let's add a user:

insert into users(name) VALUES ('Jools');

Save a setting

First thing that might come to mind would be to

1. run a query to retrieve a user's settings
2. decode JSON
3. modify JSON
4. run an UPDATE query with the new settings value.

That would work but let's see how we can do that with just one query.

Postgres has a cool function called jsonb_build_object which allows us to, as its name states, build JSON objects.

In our case we'd need to run:

jsonb_build_object('landingPage', 'dashboard')

which would return {"landingPage": "dashboard"}.

The whole update query:

update users
set settings = jsonb_build_object('landingPage', 'dashboard')
where id=1;

yeah but what if there's already a setting, this would overwrite it, right?

Yes! Postgres has a concatenate operator which we will use.

update users
set settings = settings || jsonb_build_object('language', 'jp')
where id=1;

What's this does is build a JSON object {"language":"jp"} and concatenate it with any existing settings which results in:

{"language": "jp", "landingPage": "dashboard"}

Note that existing JSON keys will be overwritten.

select '{"age": "31"}'::jsonb || '{"age": "12"}'::jsonb;

results in:
{"age": "12"}

If you want to preserve existing data then just flip ||, which in our case becomes:

update users
set settings = jsonb_build_object('language', 'jp') || settings
where id=1;

Hope you've learned something.

Happy coding! ⌨️