DEV Community

Cover image for Update & delete JSONB in rails and postgres
Sara Alhaddadi
Sara Alhaddadi

Posted on

Update & delete JSONB in rails and postgres

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: {})
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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"}
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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"}
Enter fullscreen mode Exit fullscreen mode
  • 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"}
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

to delete key use mins with string of key in jsonb object

SELECT '{"a": "b"}'::jsonb - 'a';
-- ?column? 
-- ----------
-- {}
-- (1 row)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

#-

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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"}
Enter fullscreen mode Exit fullscreen mode

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"}
Enter fullscreen mode Exit fullscreen mode
  • 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"')
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"}
Enter fullscreen mode Exit fullscreen mode
  • 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"}
Enter fullscreen mode Exit fullscreen mode
  • here notice that I use select not update . 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"}
Enter fullscreen mode Exit fullscreen mode

Code snippets :

all code examples in jsonb series in my GitHub account 💻jsonb_posts


References

https://aaronbos.dev/posts/update-json-postgresql

Top comments (0)