How to push a JSON object to a nested array in a JSONB column

Question

I need to somehow push a JSON object to a nested array of potentionally existing JSON objects - see "pages" in the below JSON snippet.

{
    "session_id": "someuuid",
    "visitor_ui": 1,
    "pages": [
        {
            "datetime": "2016-08-13T19:45:40.259Z",
            "duration,": 0,
            "device_id": 1,
            "url": {
                "path": "/"
            }
        },
        {
            "datetime": "2016-08-14T19:45:40.259Z",
            "duration,": 0,
            "device_id": 1,
            "url": {
                "path": "/test"
            }
        },
        // how can i push a new value (page) here??
    ]
    "visit_page_count": 2
}

I'm aware of the jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean]) (although still finding it a bit hard to comprehend) but I guess using that, would require that I first SELECT the whole JSONB column, in order to find out how many elements inside "pages" already exists and what index to push it to using jsonb_set, right? I'm hoping theres a way in Postgres 9.5 / 9.6 to achieve the equivalent of what we know in programming languages eg. pages.push({"key": "val"}).

What would be the best and easiest way to do this with Postgresql 9.5 or 9.6?

Patrick · Accepted Answer · 2016-09-04 02:28:19Z

10

The trick to jsonb_set() is that it modifies part of a jsonb object, but it returns the entire object. So you pass it the current value of the column and the path you want to modify ("pages" here, as a string array), then you take the existing array (my_column->'pages') and append || the new object to it. All other parts of the jsonb object remain as they were. You are effectively assigning a completely new object to the column but that is irrelevant because an UPDATE writes a new row to the physical table anyway.

UPDATE my_table
SET my_column = jsonb_set(my_column, '{pages}', my_column->'pages' || new_json, true);

The optional create_missing parameter set to true here adds the "pages" object if it does not already exist.

edited Sep 4, 2016 at 2:28

answered Aug 14, 2016 at 11:10

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

5 Comments

Dac0d3r Over a year ago

Wow it works! Thank you so much - also great you took the time to explain it in more detail, since I wasn't able to figure it out from reading the docs. :-)

Patrick Over a year ago

Glad to help and, yes, the docs can be a hard read sometimes, especially these tables where functions are described. I'll put this one in SO Documentation with more elaboration.

Dac0d3r Over a year ago

Is there a way to set the "pages" property IF it does NOT exist? Without having to do a separate update first?

Patrick Over a year ago

jsonb_set() has an optional create_missing parameter which you can use. Just add , true to the function call; see updated answer.

Dac0d3r Over a year ago

Hi again Patrick. Thanks, but I can't get it to work. It's not that important though, since I can just do something like: select coalesce(data, '{}'::jsonb ) || '{"pages": [{"url":"test"}]}'::jsonb from my_table but it would have been very cool, had it worked :)

Collectives™ on Stack Overflow

How to push a JSON object to a nested array in a JSONB column

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related