通过ID更新Postgres 9.5 Jsonb [英] Updating Postgres 9.5 Jsonb by id

问题描述

我已经阅读了文档和文档栈，但是我很傻而又不问自己的具体问题.

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

说我有一个对象数组存储在jsonb列中，例如

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

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

如果没有索引并且必须按id = 2搜索，将索引1的值从"b"更改为"c"的最有效方法是什么?

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?

更具体地说，我正在编写一个react/redux/node实时应用程序，并且我不想信任redux状态来提供索引以更新值.相反，我希望客户端发送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.

我目前正在使用逻辑来返回索引(例如，选择整个jsonb列，使用lodash函数查找id = 2的索引，然后使用lodash找到的索引更新jsonb_set).

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).

我希望只有一个查询，没有服务器逻辑方法可以完成此操作.我尝试使用子查询，但是postgres文档未显示如何返回索引.

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.

感谢您的帮助！

这是使用Node的当前数据库查询和逻辑.

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"}];
})

可以将其简化为一个postgres查询吗?

Can that be reduced to one postgres query?

推荐答案

示例数据:

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"}]');

您有两个选择.第一个(有些复杂)使用jsonb_array_elements(jsonb_column) with ordinality:

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)

第二个选项(简单一点)，修改连续json元素中的值并汇总结果:

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)

这篇关于通过ID更新Postgres 9.5 Jsonb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！