Update & delete JSONB in rails and postgres
Tags: jsonb, postgres, rails, sql
Published: 2022-08-05 19:01:09

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 

```ruby
> 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

```sql
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](https://www.postgresql.org/docs/9.5/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE)

|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]')|[{"f1":[2,3,4],"f2":null},2,null,3|[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]|


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

```sql
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)**

```sql
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 )_

```sql
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`

```sql
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!

```sql
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

```sql
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):

```sql
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](https://www.postgresql.org/docs/9.5/functions-json.html#FUNCTIONS-JSONB-OP-TABLE) 

|Operator|Right Operand Type|Description|Example
|:-----|:-----|:-----|:-----|
| &#124;&#124; |jsonb|Concatenate two jsonb values into a new jsonb value|'["a", "b"]'::jsonb &#124;&#124; '["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

```sql
SELECT '["a", "b"]'::jsonb || '["c", "d"]'::jsonb;
-- ?column? 
-- ----------------------
-- ["a", "b", "c", "d"]
-- (1 row)
```

it will not remove duplicates in jsonb arrays

```sql
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](https://dev.to/sara_alhaddadi/brief-introduction-to-jsonb-in-postgresql-26im) of this series that jsonb **replace duplicate keys with last value** 

```sql
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 

```sql
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 !

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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 

```json
{"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:

```sql
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"')`

```sql
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 ...

```sql
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 `select` not `update` . So the original value ( which from the last update statement ) will not change after each example 

```sql
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](https://github.com/SaraAlhaddadi/jsonb_posts)

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