基于json的Postgresql表更新

I am getting some data in json format.

我正在以json格式获取一些数据。

jsonData json;

jsonData = '[{
    "id": 1,
        "text": "Materials and varieties",
        "parent": "0"
}, {
    "id": 2,
        "text": "Bricks",
        "parent": "1"
}, {
    "id": "new_1",
        "text": "newitem",
        "parent": "1"
}
]';

Is it possible to do an update for a table field based on id field of jsonData.

是否可以基于jsonData的id字段对表字段进行更新。

I want to do something like

我想做点什么

UPDATE item_table SET active = 'N' WHERE
    item_id NOT IN ((jsonData ->>'id')::INTEGER) ;

I also want to restrict jsonData by not allowing ids that match pattern 'new_%'.

我还想通过不允许匹配模式'new_%'的id来限制jsonData。

I am using Postgres 9.3

我正在使用Postgres 9.3

1 个解决方案

#1

I might be doing it a bit too complex, but at least you can get the ids this way:

我可能会做得太复杂,但至少你可以这样得到ID:

select id::int from (select (json_array_elements('[{
    "id": 1,
        "text": "Materials and varieties",
        "parent": "0"
}, {
    "id": 2,
        "text": "Bricks",
        "parent": "1"
}, {
    "id": "new_1",
        "text": "newitem",
        "parent": "1"
}
]'::json))->>'id' as id) a where id not like 'new_%';

Of course a better check would be to test if the id is actually numeric, but if you know it's either a number or new_, then this also works.

当然,更好的检查是测试id是否实际上是数字,但如果你知道它是数字或new_,那么这也有效。

So just add that to your update ... where in ... and you can use the JSON ids.

所以只需将其添加到您的更新中......在...中,您可以使用JSON ID。

#1