Updating Postgres 9.5 Jsonb by id

Question

I've read the docs and stack, but I'm too dumb to get this without asking my specific question.

Say I have an array of objects stored in a jsonb column, e.g.

[{"id":1, "value":"a"], {"id":2, "value":"b"}]

What is the most efficient way to change index 1's value from "b" to "c" if you don't have the index and must search by id=2?

To be more specific, I'm writing a react/redux/node real time app, and I don't want to trust the redux state to give the index to update value. Rather, I want the client to send id=2 and let the server/database find the index of that array, and then update the value.

I'm currently using logic to return the index (e.g. select the entire jsonb column, use a lodash function to find the index with id=2, then update jsonb_set with the lodash found index).

My hope is there's a one query, no server logic way to accomplish this. I've tried to use sub queries, but the postgres docs don't show how to return an index.

Thanks for the help!

EDIT: Here's the current database queries and logic using Node.

let _ = require('lodash');
let id=2;
let newValue='c';
let query=`SELECT jsonb_column from table where rowid=1`;
pgQuery(query)
.then((result)=>{
    result=result[0].result // cleaning up the return object
    //result=[{"id":1, "value":"a"], {"id":2, "value":"b"}];
    let index=_.findLastIndex(result, {id}) // index=1
    let query2=`UPDATE table
               SET jsonb_column=jsonb_set(jsonb_column, '{${index}, value}', '${newValue}') 
               WHERE rowid=1` RETURNING jsonb_column

    return pgQuery(query2) 
    // returns [{"id":1, "value":"a"], {"id":2, "value":"c"}];
})

Can that be reduced to one postgres query?

Answer 1

Example data:

create table a_table (rowid int, jsonb_column jsonb);
insert into a_table values (1, '[{"id":1, "value":"a"}, {"id":2, "value":"b"}]');
insert into a_table values (2, '[{"id":2, "value":"a"}, {"id":1, "value":"b"}]');

You have two options. The first (somewhat complicated), use jsonb_array_elements(jsonb_column) with ordinality:

update a_table t1
set jsonb_column = 
        jsonb_set(
            jsonb_column, 
            array[(
                select ordinality::int- 1
                from a_table t2, jsonb_array_elements(jsonb_column) with ordinality
                where t1.rowid = t2.rowid and value->>'id' = '2')::text,
            'value'::text],
            '"c"'
        );

select * from a_table;

 rowid |                    jsonb_column                    
-------+----------------------------------------------------
     1 | [{"id": 1, "value": "a"}, {"id": 2, "value": "c"}]
     2 | [{"id": 2, "value": "c"}, {"id": 1, "value": "b"}]
(2 rows)

The second option (a bit simpler), modify values in consecutive json elements and aggregate the result:

update a_table t1
set jsonb_column = (
    select jsonb_agg(val)
    from (
        select case 
            when value->'id' = '2' then jsonb_set(value, '{value}', '"d"')
            else value end val
        from a_table t2, jsonb_array_elements(jsonb_column)
        where t1.rowid = t2.rowid
        ) s
    );

select * from a_table;

 rowid |                    jsonb_column                    
-------+----------------------------------------------------
     1 | [{"id": 1, "value": "a"}, {"id": 2, "value": "d"}]
     2 | [{"id": 2, "value": "d"}, {"id": 1, "value": "b"}]
(2 rows)