How do I select and update a JSON array element in Postgresql?

Question

I'm using version 9.6. I have documents that look like this:

{
    "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"
        }
    ]
}

I created them like this:

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

Now I'm trying to figure out how to A, select the row with the name John Doe, and update his mobile number to "555-555-0003".

From here Postgresql 9.6 documentation I figured out that I could query for the proper document like this:

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

But I'm failing to see how to select the proper sub-document in the phones array by type, and update the number value. Can anyone help me out?

EDIT

I need to assume the sub-documents have extra values and are not consistent. So I added some above. I'm pretty sure this update isn't possible without data loss at this point.

Answer 1

How to select the proper sub-document in the phones array by type?

If you want to get the phone number, use this. Corresponding document is 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);

If you want search by phone number, this works:

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

How to update the number value?

As mentioned in the comment, there is a similar question, How do I modify fields inside the new PostgreSQL JSON datatype?

There are other methods to do this, like

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';