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.
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)
We saw in the previous tip Append a Record to an Array in a JSONB Column how to append a new record to an array in JSONB column, but now I want to add a new record to the beginning of the array.
To do that I can still make use of the the JSONB_SET function
in an UPDATE statement, but this time flipping the values on each side of the concatenation operator in the replacement parameter.
yugabyte=# UPDATE jsonb_ex SET payload = jsonb_set(payload,
yugabyte(# array['authors'],
yugabyte(# ('[{"fn": "Lewis", "ln": "Carol"}]'::jsonb || (payload->'authors')::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": "Lewis", "ln": "Carol"}, {"fn": "Jane", "ln": "Austen"}, {"fn": "Ray", "ln": "Bradbury"}]}
(2 rows)
Have fun!