如何在PostgreSQL中选择和更新JSON数组元素？

Question

如何在PostgreSQL中选择和更新JSON数组元素？

6

我使用的是9.6版本。我有一些类似于这样的文档：

{
    "name" : "John Doe", 
    "phones" : [
        {
            "type" : "mobile", 
            "number" : "555-555-0000",
            "deleted": false
        }, 
        {
            "type" : "home", 
            "number" : "555-555-0001",
            "needsUpdated" : true
        }, 
        {
            "type" : "work", 
            "number" : "555-555-0002"
        }
    ]
}

我按照以下方式创建它们：

create table t_json (c_json json not null);

insert into t_json (c_json) values ('{"name":"John Doe","phones": [{"type":"mobile","number":"555-555-0000"},{"type":"home","number":"555-555-0001"},{"type": "work","number": "555-555-0002"}]}');

insert into t_json (c_json) values ('{"name":"Jane Dane","phones": [{"type":"mobile","number":"555-555-0030"},{"type":"home","number":"555-555-0020"},{"type": "work","number": "555-555-0010"}]}');

现在我正在尝试弄清如何A，选择名为John Doe的行，并将其手机号码更新为“555-555-0003”。

从这里Postgresql 9.6文档中，我发现可以像这样查询正确的文档：

select c_json from t_json where c_json->>'name' = 'John Doe';

我不知道如何通过类型选择电话数组中正确的子文档并更新号码值。有人能帮我吗？

编辑

我需要假设子文档有额外的值并且不一致。因此，我在上面添加了一些内容。我相信这个更新目前无法实现而不会丢失数据。

- Joshua Lawrence Austill

我认为你可以使用 jsonb_set 来完成这个任务。 - maxymoo

可能是如何修改新的PostgreSQL JSON数据类型中的字段？的重复问题。 - Jorge Campos

1

@JorgeCampos 这不是重复的问题。我已经阅读了整个问题和每一个答案，但没有找到更新数组中子文档的方法。谢谢。 - Joshua Lawrence Austill

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- crvv · Accepted Answer

如何按类型选择正确的phones数组子文档？

如果您想获取电话号码，请使用此代码。对应的文档是 https://www.postgresql.org/docs/9.6/static/queries-table-expressions.html#QUERIES-LATERAL。

SELECT c_json ->> 'name', phones.type, phones.number
    FROM t_json
    CROSS JOIN json_to_recordset(c_json -> 'phones')
        AS phones("type" TEXT, "number" TEXT);

如果您想按电话号码搜索，可以这样做：

SELECT * FROM t_json
    WHERE (c_json -> 'phones')::JSONB @>
        '[{"type":"mobile","number":"555-555-0000"}]'::JSONB;

如何更新数字值？

正如评论中提到的，有一个类似的问题：如何修改新的PostgreSQL JSON数据类型中的字段？还有其他方法可以做到这一点，比如：

UPDATE t_json SET c_json = newvalue FROM (
    SELECT to_json(updated) AS newvalue FROM (
        SELECT c_json ->> 'name' as "name",
            json_agg(json_build_object('type', phones.type, 'number',
                CASE phones.type WHEN 'mobile' THEN '555-555-0003' ELSE phones.number END)
            ) AS phones
    FROM t_json
    CROSS JOIN json_to_recordset(c_json -> 'phones')
        AS phones("type" TEXT, "number" TEXT)
    WHERE c_json->>'name' = 'John Doe'
    GROUP BY name
    ) as updated
) AS sub WHERE c_json ->> 'name' = 'John Doe';