Fast & reliable JSONB deep structure update in PostgreSQL
Tags: postgres, sql, tutorial, webdev
Published: 2020-11-11 23:10:55

Assuming you have a PostgreSQL database with a `survey` table. A `survey` has a `questions` column (JSONB), each `question` has `choices`.

Sample content of the `questions` column for the "web dev" `survey`:
```js
[
  {
    "id": "c98b53c5",
    "text": "pick a language",
    "choices": [
      {"id": "235d0482", "text": "Rust"},
      {"id": "652ee824", "text": "PHP"},
      {"id": "f543366f", "text": "Forth"}
      // etc.
    ]
  },
  {
    "id": "73294354",
    "text": "pick a frontend framework",
    "choices": [
      {"id": "3feba90d", "text": "React"},
      {"id": "d6aa4dbd", "text": "Vue"},
      {"id": "9beb5da3", "text": "Angular"}
      // etc.
    ]
  },
]
```

We want to replace the `choice` with ID `9beb5da3` _(Angular)_ in question `73294354` _(pick a frontend framework)_ for survey `8bf9fedb` (the ID of the survey for _web dev survey_)

```diff
- {"id": "9beb5da3", "text": "Angular"}
+ {"id": "1acffbaf", "text": "Svelte"}
```

To update a subpart of a JSONB column, we could use [`jsonb_set`](https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE). Given a path with indices and keys, we can replace the target object with a new object:

```sql
update survey
set questions = jsonb_set(
    questions,
    '{1,choices,2}', -- path to angular
    '{"id": "1acffbaf", "text": "Svelte"}'::jsonb,
    false)
where id = '8bf9fedb'; -- the survey id
```

Still, the problem is that we don't know the actual positions of the items in the arrays. We have to find them first. The only thing we know is the IDs of the object.

> :thinking: _"I know I want to update choice `9beb5da3` in question `73294354`; I don't know what is their positions in their respective arrays."_

One could have run one first query to get the whole column data, then iterate over objects with their favorite programming language to find item positions, then use these positions to configure the `jsonb_set` path parameter in order to run the `update` query.

This approach has two caveats:
 - It's **slow**; get all the record data from DB, find positions programmatically, then update the data in the database.
 - It's **not reliable**: the positions of data could have changed during this process, between the two queries.

We can do this with only one operation: one `update` main query that contains two sub-queries to build indices.

## Solution

First, we need to get the index of the question `73294354`:

```sql
select question, questionIndex
from survey
cross join jsonb_array_elements(questions) 
    with ordinality arr(question, questionIndex)
where id='8bf9fedb' and question->>'id'='73294354'
```

`jsonb_array_elements` function expands the array to a set of JSON values, `with ordinality` adds a number (starting from 1, increasing by 1) for each row of the function output. This query returns:

| question | questionindex |
| :--- | :--- |
| {"id": "73294354", "text": "pick a frontend framework", "choices": \[{"id": "3feba90d", "text": "React"}, {"id": "d6aa4dbd", "text": "Vue"}, {"id": "9beb5da3", "text": "Angular"}\]} | 2 |

Then, we need to get the index of the choice `9beb5da3` (which is in the question `73294354`: we have to be sure we search only in this question, in case some choices share the same index in different questions).

To do so, we have to use the first query as a subquery, then get the index:

```sql
-- This is the previous query, see above.
-- Using "WITH", also known as Common Table Expressions (CTE),
-- helps in reading complex queries (avoid nested subquery).
with questions_table as (
    select question, questionIndex
    from survey
    cross join jsonb_array_elements(questions)
        with ordinality arr(question, questionIndex)
    where id = '8bf9fedb' and question->>'id'='73294354'
)
-- We can now use questions_table to find choice index.
select choice, choiceIndex
from questions_table
cross join jsonb_array_elements((question->'choices')::jsonb)
    with ordinality arr(choice, choiceIndex)
where choice->>'id'='9beb5da3'
```

We just used again `jsonb_array_elements` and `with ordinality`, nothing new here. As a result, we have got the choice we want to change and it's position.

| choice | choiceindex |
| :--- | :--- |
| {"id": "9beb5da3", "text": "Angular"} | 3 |

Now, we are ready to update the record, since we have the index to build the path. Building the path is simply creating a string thanks to the index we have from previous steps:

```
'{' || questionIndex-1 || ',choices,' || choiceIndex-1 || '}'
```

:point_up: Since ordinality starts to 1, we have subtracted 1 to the indices.

We can modify the second query to directly return the path we want:

```sql
with questions_table as (
    select question, questionIndex
    from survey
    cross join jsonb_array_elements(questions)
        with ordinality arr(question, questionIndex)
    where id = '8bf9fedb' and question->>'id'='73294354'
)
select
  ('{'||questionIndex-1||',choices,'||choiceIndex-1||'}')
  as path
from questions_table
cross join jsonb_array_elements((question->'choices')::jsonb)
    with ordinality arr(choice, choiceIndex)
where choice->>'id'='9beb5da3'
```

| path |
| :--- |
| {1,choices,2} |

For the last step, we need to create the `update` query that uses the two previous queries to actually update the choice:

```sql
with questions_table as (
    select question, questionIndex
    from survey
    cross join jsonb_array_elements(questions)
        with ordinality arr(question, questionIndex)
    where id = '8bf9fedb' and question->>'id'='73294354'
), choices_table as (
    select ('{'||questionIndex-1||',choices,'||choiceIndex-1||'}')::text[] as path
    from questions_table
    cross join jsonb_array_elements((question->'choices')::jsonb)
        with ordinality arr(choice, choiceIndex)
    where choice->>'id'='9beb5da3'
)
update survey
set questions = jsonb_set(
    questions,
    path, -- the path we built just before.
    '{"id": "1acffbaf", "text": "Svelte"}'::jsonb,
    false)
from choices_table
where id = '8bf9fedb';
```


```
1 row affected in 7 ms
```
_It's fast, since we are only reading and writing one row in `survey`. It's reliable because we are doing only one query._

Some links:
- [Common Table Expressions](https://www.postgresql.org/docs/current/queries-with.html)
- [JSON functions and operators](https://www.postgresql.org/docs/13/functions-json.html)
- [How to query JSONB, beginner sheet cheat](https://medium.com/hackernoon/how-to-query-jsonb-beginner-sheet-cheat-4da3aa5082a3) ([blog version](http://raph.site/blog/postgresql.html))

Feel free to post a comment if you need more information.