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.

Possible duplicate of [How do I modify fields inside the new PostgreSQL JSON datatype?](https://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype) — Jorge Campos, Jun 01 '17 at 04:07
@JorgeCampos this is not a duplicate. I've read through that entire question and every answer and did not find a way to update a sub-document inside an array. Thanks though. — Joshua Lawrence Austill, Jun 01 '17 at 16:02

crvv · Answer 1 · 2017-06-01T13:25:46.337

12

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

edited Jun 01 '17 at 13:25

answered Jun 01 '17 at 06:52

crvv

580
4
9

Wow, impressive answer! Thanks for giving me something to look at. This question is not a duplicate, I'm asking specifically how to update a sub-document inside an array, I've read through that entire question in the comment and nowhere do they talk about that. It looks to me like your code snippet actually retrieves the entire json object, modifies it and saves it back, is that correct? Is this acid compliant? – Joshua Lawrence Austill Jun 01 '17 at 12:45
Yes, I expand the entire JSON object and reconstruct it. Within one SQL statement, of course it is ACID compliant. – crvv Jun 01 '17 at 13:24
I thought so, but I don't like to assume :) So you are recreating the sub-document in the json_agg? What happens if that sub-document has an extra key:value pair, like say "updated": "no". That extra data would be lost unless you added it to the statement? Or am I not understanding correctly? – Joshua Lawrence Austill Jun 01 '17 at 14:29
That extra data will be lost. – crvv Jun 02 '17 at 14:04

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

1 Answers1