Welcome back to learn more about jsonb ...✨✨
After a little digging into jsonb and getting to know it and how to add and query data, let's dive in more to learn how to modify it...
Update JSONB
In rails work with it as hash
> book.payload['publisher'] = "new publisher"
> book.save
> book.reload.payload['publisher']
=> "new publisher"
> new_payload = {publisher: "Blue Ocean"}
> book.update(payload: new_payload)
> book.reload.payload
=> {"publisher"=>"Blue Ocean"}
> book.update(payload: {})
but deal with jsonb as hashes to grape the data and change it then save or update each record is not feasible or functional!
if we need to update so much records or add keys to all records … we really need some postgres tools to help here ….
so let’s ask postgres how we can do that in it ?
Postgres : sure use my update statement with some of my operators and functions
Use update statement can be trickery … so if we try something like
UPDATE books SET payload -> 'publisher' = 'sara'
WHERE title = 'book1';
-- we will get error
ERROR: syntax error at or near "->"
LINE 1: UPDATE books SET payload -> 'publisher' = 'sara' WHERE title…
so actually let’s start from the beginning and from the fact that payload as whole is table column and in database
so we can use update to change the whole value of whole column not to change parts of it …
so if we want to update or delete something from jsonb column ( like payload here ) we should replace the whole payload with new one and save it to database and this is what we will discover now ...
Update & insert keys in postgres
jsonb_set is function that allow you to change the value of specific key and return new version of jsonb column that we can use in update statement to replace the old value
as from Postgres
| Function | Return Type | Description | Example | Example Result |
|---|---|---|---|---|
| jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean]) | jsonb | Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true (default is true) and the item designated by path does not exist. As with the path oriented operators, negative integers that appear in path count from the end of JSON arrays. | jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false) | jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]') |
first to explain jsonb_set I will use it in SELECT statement to see the return value from it
in [{"f1":1,"f2":null},2] jsonb array replace the value of path {0,f3} which mean as we saw before that the value of key f3 inside first element – 0 index - {"f1":1,"f2":null}
here in below example we want to change the value of that f3 by [2,3,4] and here the create_missing params does not pass to function so we will use the default value which true mean create f3 if it does not exists and then return the new version of the whole jsonb after update
SELECT jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]');
-- jsonb_set
-- ---------------------------------------------
-- [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
-- (1 row)
but here in create_missing we pass (false) which means if you did not find f3 do not create it (update only)
SELECT jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]',false);
-- jsonb_set
-- ----------------------------
-- [{"f1": 1, "f2": null}, 2]
-- (1 row)
here we did not find f3 so the return value will be the original value ( on change )
let's learn more by these examples ...
Yes, more examples as you know...
If you've been following the series, you'll know that I use a lot of examples as I think it's a great way to discover and examine new information.
here we use -1 as index which mean the last element in array (negative integers that appear in path count from the end )
SELECT jsonb_set('[1,2,{"f1":1,"f2":null}]', '{-1,f2}','[2,3,4]',false);
-- jsonb_set
-- ------------------------------------
-- [1, 2, {"f1": 1, "f2": [2, 3, 4]}]
-- (1 row)
SELECT jsonb_set('[{"f1":1,"f2":null},2]', '{0}','[2,3,4]',false);
-- jsonb_set
-- ----------------
-- [[2, 3, 4], 2]
-- (1 row)
what if we want to change the value of publisher in our book example … here I use SELECT just to show the new value of payload
SELECT jsonb_set(payload, '{publisher}', '"new publisher"') from books WHERE title = 'book1';
-- {"price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}
now let’s really update the value of publisher in book1 as it is new publisher now!
UPDATE books SET payload = jsonb_set(payload, '{publisher}', '"new publisher"') WHERE title = 'book1';
take more complicated examples with tags
- Add new key tags or update it if it is exists
UPDATE books SET payload = jsonb_set(payload, '{tags}', '["tag3", "tag4"]') WHERE title = 'book1';
-- {"tags": ["tag3", "tag4"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}
- Replacing the second tag (0-indexed):
UPDATE books SET payload = jsonb_set(payload, '{tags,1}', '"tag5"') WHERE title = 'book1';
-- {"tags": ["tag3", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}
Delete key from JSONB
jsonb_set is really great tool to update or add new keys to jsonb … but what about delete or concatenate values ?
Fortunately we have some nice jsonb operators to do that for us
take a look to them in this table from postgres documentation
| Operator | Right Operand Type | Description | Example |
|---|---|---|---|
| || | jsonb | Concatenate two jsonb values into a new jsonb value | '["a", "b"]'::jsonb || '["c", "d"]'::jsonb |
| - | text | Delete key/value pair or string element from left operand. Key/value pairs are matched based on their key . | '{"a": "b"}'::jsonb - 'a' |
| - | integer | Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array. | '["a", "b"]'::jsonb - 1 |
| #- | text[] | Delete the field or element with specified path (for JSON arrays, negative integers count from the end) | '["a", {"b":1}]'::jsonb #- '{1,b}' |
As before I find it is handy to explain it first by using SELECT to show the final result of our jsonb
||
the two pipe || use concatenate jsonbs objects or arrays
SELECT '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
-- ?column?
-- ----------------------
-- ["a", "b", "c", "d"]
-- (1 row)
it will not remove duplicates in jsonb arrays
SELECT '["a", "b"]'::jsonb || '["a", "d"]'::jsonb;
-- ?column?
-- ----------------------
-- ["a", "b", "a", "d"]
-- (1 row)
SELECT '{"a":1, "b":2}'::jsonb || '{"c":3, "d":4}'::jsonb;
-- ?column?
-- ----------------------------------
-- {"a": 1, "b": 2, "c": 3, "d": 4}
-- (1 row)
but in jsonb object as we sow in first tutorial of this series that jsonb replace duplicate keys with last value
SELECT '{"a":1, "b":2}'::jsonb || '{"a":3, "d":4}'::jsonb;
-- ?column?
-- --------------------------
-- {"a": 3, "b": 2, "d": 4}
-- (1 row)
–
to delete key use mins – with string of key in jsonb object
SELECT '{"a": "b"}'::jsonb - 'a';
-- ?column?
-- ----------
-- {}
-- (1 row)
it is ok if key does not exists as no error will be raisen … cool and handy !
SELECT '{"a": 1,"b":2}'::jsonb - 'c';
-- ?column?
-- ------------------
-- {"a": 1, "b": 2}
-- (1 row)
to delete element use mins – with number of index or string element in jsonb array
SELECT '["a", "b"]'::jsonb - 1;
-- ?column?
-- ----------
-- ["a"]
-- (1 row)
SELECT '["a", "b"]'::jsonb - -1;
-- ?column?
-- ----------
-- ["a"]
-- (1 row)
SELECT '["a", "b"]'::jsonb - 'a';
-- ?column?
-- ----------
-- ["b"]
-- (1 row)
we cannot delete from object using integer index if integer is key in jsonb it should be string
SELECT '{"1":1, "2":2}'::jsonb - 1;
ERROR: cannot delete from object using integer index
SELECT '{"1":1, "2":2}'::jsonb - '1';
?column?
----------
{"2": 2}
(1 row)
#-
what if we want to delete field or element with specified path … sure we know to use – to delete and for select path we can use # which indicate that we will use path - as we see in #> and #>> -
so let’s combine the functionality of # and – to get #- which will delete field or element with specified path
SELECT '["a", {"b":1}]'::jsonb #- '{1,b}';
-- ?column?
-- -----------
-- ["a", {}]
-- (1 row)
SELECT '{"1": {"b":1,"c":2}}'::jsonb #- '{1,b}';
-- ?column?
-- -----------------
-- {"1": {"c": 2}}
-- (1 row)
as I said before it is ok if it does not find the element in path no errors will annoy you
SELECT '["a", {"b":1}]'::jsonb #- '{0,b}';
-- ?column?
-- -----------------
-- ["a", {"b": 1}]
-- (1 row)
let’s work with tags in book … with this examples
this is the current value of payload for one book
{"tags": ["tag3", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}
and we will show changes to it in these examples after each update statement
- Remove the last tag:
UPDATE books SET payload = payload #- '{tags,-1}' WHERE title = 'book1';
-- {"tags": ["tag3"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}
- Complex update (delete the last tag, insert a new tag, and change the name or insert it if it is not there):
- delete the last tag payload
#- '{tags,-1}' - insert a new tag
jsonb_set(payload #- '{tags,-1}', '{tags,0}', '"tag10"', true) - change the name or insert it
jsonb_set(jsonb_set(payload #- '{tags,-1}', '{tags,0}', '"tag10"', true), '{name}', '"my-other-name"')
- delete the last tag payload
UPDATE books SET payload = jsonb_set(jsonb_set(payload #- '{tags,-1}', '{tags,0}', '"tag10"', true), '{name}', '"my-other-name"') WHERE title = 'book1';
-- {"name": "my-other-name", "tags": ["tag10"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}
- more interesting examples to to practice ...
UPDATE books SET payload = jsonb_set(payload, '{tags,1}', '"tag5"') WHERE title = 'book1';
-- {"name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "publisher1", "published_date": "2017-04-07"}
UPDATE books SET payload = payload || '{"a": "apple"}' WHERE title = 'book1';
-- {"a": "apple", "name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}
- here notice that I use
selectnotupdate. So the original value ( which from the last update statement ) will not change after each example
SELECT payload - 'a' from books WHERE title = 'book1';
-- {"name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}
SELECT (payload -> 'tags') - 'tag10' from books WHERE title = 'book1';
-- ["tag5"]
-- tags are "tags": ["tag10", "tag5"] the proives query is select not update
SELECT (payload -> 'tags') - 0 from books WHERE title = 'book1';
-- ["tag5"]
SELECT payload #- '{"tags",0}' from books WHERE title = 'book1';
-- {"a": "apple", "name": "my-other-name", "tags": ["tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}
SELECT payload #- '{"authors",0,"name"}' from books WHERE title = 'book1';
-- {"a": "apple", "name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}
-- no error if key is not found
SELECT payload #- '{"authors",0,"age"}' from books WHERE title = 'book1';
-- {"a": "apple", "name": "my-other-name", "tags": ["tag10", "tag5"], "price": 100, "authors": [{"id": 1, "name": "author1"}, {"id": 2, "name": "author2"}], "publisher": "new publisher", "published_date": "2017-04-07"}
Code snippets :
all code examples in jsonb series in my GitHub account 💻jsonb_posts
Top comments (0)