Menu
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?
946
Postgres offers a jsonb_set function for updating JSON fields. The second parameter path defines, which property you want to update. To update items in an array, you can use an index-based approach. To update the first entry in the items array in the example above, a path woud look like this: {items, 0, customerId} .
The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed.
Objects can be nested inside other objects. Each nested object must have a unique access path. The same field name can occur in nested objects in the same document.
Attributes and event data can contain nested (JSON) values—arrays, objects, and arrays of objects. You can use these nested values to match people in segments, filters, and trigger criteria.
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.
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
