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.