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? — Jorge Campos
– Jorge Campos, Commented Jun 1, 2017 at 4: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
– Joshua Lawrence Austill, Commented Jun 1, 2017 at 16:02

crvv · Accepted Answer · 2017-06-01 13:25:46Z

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 1, 2017 at 13:25

answered Jun 1, 2017 at 6:52

crvv

6105 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joshua Lawrence Austill Over a year ago

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?

crvv Over a year ago

Yes, I expand the entire JSON object and reconstruct it. Within one SQL statement, of course it is ACID compliant.

Joshua Lawrence Austill Over a year ago

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?

crvv Over a year ago

That extra data will be lost.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related