What I Learned at Work this Week: Update JSON Property in Postgres

Photo by DTS VIDEOS: https://www.pexels.com/photo/yellow-bird-on-nest-532954/

At work, we’ve designed a Postgres table that uses JSON as one of its column types. The idea was that we wanted to use the data in that column to conditionally trigger features from a script. We could have made each json property into its own column or table, but we wanted to be able to quickly add features without having to run additional migrations.

The results so far have been mixed. We are enjoying the advantage of a very customizable field, but writing SQL to work with this field is challenging. For example, this past week we wanted to conditionally update the property for a bunch of rows in the table. I can write a simple UPDATE query in Postgres, but how do I drill down into the json to avoid overwriting the whole field?

Creating an Example

If you want to follow along, you can actually check out my work on a DB Fiddle here, or you can start one from scratch here. To set up and populate a table, I used the example code from CloudBees here because it does a great job illustrating the utility of jsonb. In case those links ever die, here’s the code:

-- CREATING THE TABLE
CREATE TABLE cards (
  id integer NOT NULL,
  board_id integer NOT NULL,
  data jsonb
);

-- POPULATING THE TABLE
INSERT INTO cards VALUES (1, 1, '{"name": "Paint house", "tags": ["Improvements", "Office"], "finished": true}');
INSERT INTO cards VALUES (2, 1, '{"name": "Wash dishes", "tags": ["Clean", "Kitchen"], "finished": false}');
INSERT INTO cards VALUES (3, 1, '{"name": "Cook lunch", "tags": ["Cook", "Kitchen", "Tacos"], "ingredients": ["Tortillas", "Guacamole"], "finished": false}');
INSERT INTO cards VALUES (4, 1, '{"name": "Vacuum", "tags": ["Clean", "Bedroom", "Office"], "finished": false}');
INSERT INTO cards VALUES (5, 1, '{"name": "Hang paintings", "tags": ["Improvements", "Office"], "finished": false}');

If you’re confused by jsonb, you’re not alone. I’m not great at explaining it, but I did link a useful reference in a previous blog post. Long story short, jsonb is stored differently so it’s more efficient to use.

Let’s say we wanted to add a new element to the tags array to indicate that these first five rows were examples credited to CloudBees. That’s a few layers down; how can I make that change without altering the rest of the metadata?

Concatenation

One way to describe our goal is to say that we want to concatenate our json by taking what exists and combining it with something new. Sort of like this:

data = stuff_that_exists + something_new + rest_of_existing_data

Postgres has a couple of options for this in jsonb. First, we could use ||:

update cards
  set data = data || '{"tags": ["CloudBees"]}';

Here, we’re setting data to itself with one caveat: the tags property will have a new value, ["CloudBees"]. Here are our results:

We were able to maintain some parts of the json, but we completely overwrote tags in every row. We don’t want to lose the existing tags — can we edit the sql to concatenate another layer down?

jsonb_set()

When I searched for an answer to this problem, I primarily found suggestions to use jsonb_set. It’s probably possible to get this working with only the || operation, but usually if I can find one working solution, I’m happy. jsonb_set is a postgres function that can set or replace the value in a specified path:

jsonb_set(
  target JSONB, path TEXT[], new_value JSONB[, create_if_missing BOOLEAN]
) -> JSONB

This is what I wrote to get this working:

update cards
  set data = jsonb_set(
    data,
    '{tags}',
    (data -> 'tags')::jsonb || '["CloudBees"]'::jsonb
  );

• The first argument is the target jsonb, so I passed in the name of the column containing that data.
• The second argument is the name of the property within the jsonb that we want to change. It’s data type is an array of text. This confused me for a while because I kept trying to put jsonb or text in there. Note that the array is indicated by curly brackets and that they are inside of quotes, not outside.
• The third argument is the new value we want to pass as type jsonb. Now we bring back our concatenation syntax, saying “if there are existing tags, pass those + "CloudBees", if not, create the property with an array of just "CloudBees”.” Note that both sides of the concat are explicitly typed as jsonb.

And here are the results:

Conditional Updates

If we wanted, we could add a where on here to filter for only certain rows:

update cards
  set data = jsonb_set(data, '{tags}', (data -> 'tags')::jsonb || '["CloudBees"]'::jsonb)
  where (data -> 'finished')::text = 'false';

Now that we’ve got the core logic, our options are endless. I’m still not 100% convinced that going with jsonb for our work schema was the best decision, but now that we’ve made it, I’m happy to see that postgres supports it well. As always, we’ll just have to work on learning the tools available to us.

Sources

• DB Fiddle
• Unleash the Power of Storing JSON in Postgres, Leigh Halliday, CloudBees
• PostgreSQL jsonb_set() Function, SQLiz
• Appending (pushing) and removing from a JSON array in PostgreSQL 9.5+, StackOverflow