postgresql add tag
FAZ_PG_SQL (imported from SE)
I am having trouble writing a `WHERE` clause to update a JSON column in Postgres database:

Given a table of the form: 

```sql
create table Test_Table (
    clusterId INT, 
    owner VARCHAR(50), 
    data JSONB
)
```

And three rows of data:

```
insert into Test_Table
values
(1, 'Jacky',{data}),
(2, 'Bob', NULL), 
(3, 'Wolf',NULL);
```

...where the `{data}` in row 1 is a json document of the form...

```json
{
    "certified": [
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-03",
            "id_no": "f6b2197c4889"
        },
        {
            "Type": "New Instance",
            "Status": "Failed",
            "created": "2020-02-04",
            "id_no": "216430036272"
        },
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-04",
            "id_no": "3bb515f781c6"
        },
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-04",
            "id_no": "26ae740e9b21"
        },
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-04",
            "id_no": "7e30a80211a2"
        }
    ],
    "in_common": [
        {
            "name": "Kodali",
            "owner": "Jet"
        },
        {
            "name": "Lakers",
            "owner": "Fantastic"
        },
    ]
}
```

I want to write an update statement to update the `data` column and change the `"Status"` to `'Active'` where `cluster_id` is 1 and `"id_no"` is "216430036272". How do I write a `WHERE` clause that will update only this attribute?
Top Answer
PeterVandivier (imported from SE)
As noted in comments, this would be a much easier task if the data was properly normalised. However, in the interest of wasting a morning playing with [postgres json functions][1], you could use the following query ([fiddle][2])

```sql
with js as (
    select 
        jsonb_array_elements((data->>'certified')::jsonb) as elems
    from test_table
    where clusterid = 1
), to_keep as (
    select elems
    from js
    where js.elems->>'id_no' != '216430036272'
), to_upd as (
    select
        jsonb_set(elems,'{Status}','"Active"') as elems
    from js
    where js.elems->>'id_no' = '216430036272'
), final_array as (
    select jsonb_agg(elems) as elems
    from (
        select elems from to_keep
        union all 
        select elems from to_upd
    ) j
)
update test_table set 
    data = jsonb_set(data,'{certified}',elems)
from final_array fa
where clusterid = 1;
```

---

Pretty clearly this is very specifically answering your narrow question, so you'll need to make your own modifications to generalise it. By way of a breakdown though...

> I want to ... update the `data` column and change the `"Status"` to "Active" where `"cluster_id"` is 1 and `"id_no"` is "216430036272" 


### `js`

```sql
select 
    jsonb_array_elements((data->>'certified')::jsonb) as elems
from test_table
where clusterid = 1
```

The first [common table expression (CTE)][3] filters down to `clusterid = 1` out of all the rows in `Test_Table`

The element `certified` in your json document is itself an array of objects - as indicated by the `[{},{}]` notation. For this reason, you need to unwrap the array. `jsonb_array_elements` turns a single row into many rows. Thus the cell...

```
+---------+
| array   |
+---------+
| [{},{}] |
+---------+
```

...becomes...

```
+---------+
| objects |
+---------+
| {}      |
| {}      |
+---------+
```

### `to_keep`

Now that you've unwrapped the objects in the array, you need to preserve them. This CTE isolates those objects that you **do not** want to modify (`js.elems->>'id_no' != '216430036272'`) so that they can be re-aggregated unchanged into the array later.

### `to_upd`

This is the object you've stated you want to update (specified by `js.elems->>'id_no' = '216430036272'`). `jsonb_set(elems,'{Status}','"Active"')` modifies the `Status` element as desired (case sensitive).

### `final_array`

```sql
select jsonb_agg(elems) as elems
from (
    select elems from to_keep
    union all 
    select elems from to_upd
) j
```

You need to turn many rows back into one row to re-create the array-of-objects you decomposed to perform the update in the first place. [`UNION ALL`][4] re-connects the rowsets from `to_keep` and `to_upd` and then `jsonb_agg` causes 

```
+---------+
| objects |
+---------+
| {}      |
| {}      |
+---------+
```

...to turn back into...

```
+---------+
| array   |
+---------+
| [{},{}] |
+---------+
```

...which allows us to use it in...

### The actual `UPDATE` statement

```sql
update test_table set 
    data = jsonb_set(data,'{certified}',elems)
from final_array fa
where clusterid = 1;
```

You must re-specify `clusterid = 1` in order to target the correct row for modification. `jsonb_set` is seen here again, this time supplanting the entire `certified` node with the reconstructed data from `final_array.elems`

[1]: https://www.postgresql.org/docs/current/functions-json.html
[2]: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=b0a03e025654a407a088a08b2c4101fb
[3]: https://www.postgresql.org/docs/current/queries-with.html
[4]: https://www.postgresql.org/docs/current/queries-union.html

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.