Posted onAug 5, 2022

Update & delete JSONB in rails and postgres

#jsonb #postgres #rails #sql

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 betrickery … so if we try something like

UPDATEbooksSETpayload->'publisher'='sara'WHEREtitle='book1';-- we will get errorERROR:syntaxerroratornear"->"LINE1:UPDATEbooksSETpayload->'publisher'='sara'WHEREtitle…

so actually let’s start from the beginning and from the fact thatpayload as whole is table column and in database

so we can use update tochange 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 fromPostgres

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 explainjsonb_set I will use it inSELECT 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 keyf3 inside first element– 0 index -{"f1":1,"f2":null}

here in below example we want to change the value of thatf3 by[2,3,4] and here thecreate_missing params does not pass to function so we will use the default value whichtrue mean createf3 if it does not exists and then return the new version of the whole jsonb after update

SELECTjsonb_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 increate_missing we pass (false) which means if you did not findf3 do not create it(update only)

SELECTjsonb_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 findf3 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 thelast element in array(negative integers that appear in path count from the end )

SELECTjsonb_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)SELECTjsonb_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 ofpublisher in our book example … here I useSELECT just to show the new value ofpayload

SELECTjsonb_set(payload,'{publisher}','"new publisher"')frombooksWHEREtitle='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 ofpublisher inbook1 as it isnew publisher now!

UPDATEbooksSETpayload=jsonb_set(payload,'{publisher}','"new publisher"')WHEREtitle='book1';

take more complicated examples with`tags`

Add new key tags or update it if it is exists

UPDATEbooksSETpayload=jsonb_set(payload,'{tags}','["tag3", "tag4"]')WHEREtitle='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):

UPDATEbooksSETpayload=jsonb_set(payload,'{tags,1}','"tag5"')WHEREtitle='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 frompostgres 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 usingSELECT 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 infirst tutorial of this series that jsonbreplace 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:cannotdeletefromobjectusingintegerindexSELECT'{"1":1, "2":2}'::jsonb-'1';?column?----------{"2":2}(1row)

#-

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:

UPDATEbooksSETpayload=payload#-'{tags,-1}'WHEREtitle='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 tagjsonb_set(payload #- '{tags,-1}', '{tags,0}', '"tag10"', true)
- change the name or insert itjsonb_set(jsonb_set(payload #- '{tags,-1}', '{tags,0}', '"tag10"', true), '{name}', '"my-other-name"')

UPDATEbooksSETpayload=jsonb_set(jsonb_set(payload#-'{tags,-1}','{tags,0}','"tag10"',true),'{name}','"my-other-name"')WHEREtitle='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 ...

UPDATEbooksSETpayload=jsonb_set(payload,'{tags,1}','"tag5"')WHEREtitle='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"}UPDATEbooksSETpayload=payload||'{"a": "apple"}'WHEREtitle='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 useselect notupdate . So the original value ( which from the last update statement ) will not change after each example

SELECTpayload-'a'frombooksWHEREtitle='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'frombooksWHEREtitle='book1';-- ["tag5"]-- tags are "tags": ["tag10", "tag5"] the proives query is select not updateSELECT(payload->'tags')-0frombooksWHEREtitle='book1';-- ["tag5"]SELECTpayload#-'{"tags",0}'frombooksWHEREtitle='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"}SELECTpayload#-'{"authors",0,"name"}'frombooksWHEREtitle='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 foundSELECTpayload#-'{"authors",0,"age"}'frombooksWHEREtitle='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"}