Append a Record to an Array in a JSONB Column

YSQL supports the JSONB data type which stores a parsed representation of a JavaScript Object Notation (JSON) document hierarchy of subvalues in an appropriate internal format.

Example:

				
					
yugabyte=# CREATE TABLE jsonb_ex (id INT, payload JSONB, PRIMARY KEY(id));
CREATE TABLE
 
yugabyte=# INSERT INTO jsonb_ex (id, payload) VALUES (1, '{"authorss": [{"fn": "Stephen", "ln": "King"}, {"fn": "Ernest", "ln": "Hemingway"}]}');
INSERT 0 1

yugabyte=# INSERT INTO jsonb_ex (id, payload) VALUES (5, '{"authorss": [{"fn": "Jane", "ln": "Austen"}, {"fn": "Ray", "ln": "Bradbury"}]}');
INSERT 0 1

yugabyte=# SELECT * FROM jsonb_ex ORDER BY id;
 id |                                       payload
----+--------------------------------------------------------------------------------------
  1 | {"authors": [{"fn": "Stephen", "ln": "King"}, {"fn": "Ernest", "ln": "Hemingway"}]}
  5 | {"authors": [{"fn": "Jane", "ln": "Austen"}, {"fn": "Ray", "ln": "Bradbury"}]}
(2 rows)
				
			

I’d like to update the table row where the ID = 5 to include the author Lewis Carol in the Authors array embedded in the JSONB column.

To do that I can make use of the the JSONB_SET function 
in an UPDATE statement.

Example:

				
					
yugabyte=# UPDATE jsonb_ex SET payload = jsonb_set(payload,
yugabyte(#                                         array['authors'],
yugabyte(#                                         (payload->'authors')::jsonb || '[{"fn": "Lewis", "ln": "Carol"}]'::jsonb)
yugabyte-#  WHERE id = 5;
UPDATE 1

yugabyte=# SELECT * FROM jsonb_ex ORDER BY id;
 id |                                                    payload
----+----------------------------------------------------------------------------------------------------------------
  1 | {"authors": [{"fn": "Stephen", "ln": "King"}, {"fn": "Ernest", "ln": "Hemingway"}]}
  5 | {"authors": [{"fn": "Jane", "ln": "Austen"}, {"fn": "Ray", "ln": "Bradbury"}, {"fn": "Lewis", "ln": "Carol"}]}
(2 rows)
				
			

Have fun!

One thought on “Append a Record to an Array in a JSONB Column

Comments are closed.