Partially Update JSON data in PostgreSQL

The Situation: We’d like to update JSON data across multiple table rows

The TL;DR.: For top-level JSON values, we can use SET. For nested JSON values, we can use SET and jsonb_set

Top-level JSON: using SET

Let’s say I have the following table, with a JSON field to represent metadata associated with each book

TABLE books;
title    | author         | metadata
---------+----------------+------------------------------
VALIS    | Philip K. Dick | { "pp": 271, "date_read": nil }
Solaris  | Stanisław Lem  | { "pp": 214, "date_read": nil }
Binti    | Nnedi Okorafor | { "pp": 96, "date_read": nil }
(3 rows)

After a month of industrious reading, I’d like to update the “date_read” metadata from nil to 2020-07. I’d like, of course, for the page count and any other information in the JSON data to remain unchanged. This I can do with the following statement:

UPDATE books 
SET metadata = metadata::jsonb || '{ "date_read": "2020-07" }';
TABLE books;
title    | author         | metadata
---------+----------------+------------------------------
VALIS    | Philip K. Dick | { "pp": 271, "date_read": "2020-07" }
Solaris  | Stanisław Lem  | { "pp": 214, "date_read": "2020-07" }
Binti    | Nnedi Okorafor | { "pp": 96, "date_read": "2020-07" }
(3 rows)

Nested JSON: using jsonb_set

Let’s say I have the following table, this time with the JSON structured in a nested way

TABLE books;
  title  |     author     |                             metadata                             
---------+----------------+------------------------------------------------------------------
 VALIS   | Philip K. Dick | { "pp": 271, "record": { "date_read": nil, "format": "PAPERBACK" } }
 Solaris | Stanisław Lem  | { "pp": 214, "record": { "date_read": nil, "format": "PAPERBACK" } }
 Binti   | Nnedi Okorafor | { "pp": 96, "record": { "date_read": nil, "format": "EBOOK" } }
(3 rows)

I can update the “date_read” metadata from nil to 2020-07 with the following:

UPDATE books 
SET metadata = jsonb_set(
  metadata::jsonb, 
  '{reading_record, date_read}'::text[], 
  '2020-07'
);
TABLE books;
  title  |     author     |                             metadata                             
---------+----------------+------------------------------------------------------------------
 VALIS   | Philip K. Dick | { "pp": 271, "record": { "date_read": "2020-07", "format": "PAPERBACK" } }
 Solaris | Stanisław Lem  | { "pp": 214, "reading_record": { "date_read": "2020-07", "format": "PAPERBACK" } }
 Binti   | Nnedi Okorafor | { "pp": 96, "reading_record": { "date_read": "2020-07", "format": "EBOOK" } }
(3 rows)

References:

https://www.postgresql.org/docs/9.5/functions-json.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s