4 ms·
How are JSON updates handled? For those who aren't aware, Postgres currently lacks support for doing updates to JSON fields via SQL[1]. For many this isn't a p
by nl 12y ago
How are JSON updates handled?
For those who aren't aware, Postgres currently lacks support for doing updates to JSON fields via SQL[1]. For many this isn't a problem, but I'd imagine that people expecting a MongoDB clone would need it.
[1] But even though you can individually address the various fields within the JSON document, you can’t update a single field. Well, actually you can, but by extracting the entire JSON document out, appending the new values and writing it back, letting the JSON parser sort out the duplicates.
https://blog.compose.io/is-postgresql-your-next-json-database/ https://blog.compose.io/is-postgresql-your-next-json-databas...
- benlambt 12y agoWrite a function in PLV8?
- bonesmoses 12y agoOr Python, or any language that has a PL in PostgreSQL. Unfortunately a lot of people consider that a hack, and they're not wrong. If we can address fields with operators (->, ->>, etc.) then why can't those operators modify, too? It works for everything else, after all. It's fairly counter-intuitive from a dev perspective. That, and PLV8 still doesn't support JSONB. With JSONB being so much more efficient, faster, and so on, not being able to interact with it using the most logical procedural language is an EPIC oversight.
- jerrysievert 12y agoThere have been calls to move toward jsonb, as well as a pull request for newer v8, but development seems to have stalled.
- nunwuo 12y ago> If we can address fields with operators (->, ->>, etc.) then why can't those operators modify, too? It works for everything else, after all. It's fairly counter-intuitive from a dev perspective. It certainly doesn't work for "everything else". You can update individual "parts" of arrays and record types, that's it. And those exception are hard-coded, so someone would either have to hard-code similar exceptions for JSON values (ugly, inflexible, generally a bad idea) or generalize support for custom operators on the left-hand side of assignments in UPDATE. All this while trying to push out a release which was already late by months. You can call everything an oversight, but with very limited resources available and the need to cut a release at some point, this is far from the truth in this case.
- chc 12y agoWhy is special-casting json or jsonb worse than special-casing arrays?
- jerrysievert 12y agocurrently, that is exactly how they are handled. the JSON structure is brought into memory, parsed, modified, and then written again to the database. this is non-optimal, but currently the only method available.
- nunwuo 12y agoThat wouldn't change even if there was a specialized syntax, because of MVCC. So the only problem is that syntactic sugar is lacking, not that the performance isn't as good as it could be.
- laurencerowe 12y agoYou can also write an update function in plain SQL. See my example in this comment on a recent plant postgresql post: http://bonesmoses.org/2015/04/10/pg-phriday-functions-and-addressing-json-data/#comment-37536 http://bonesmoses.org/2015/04/10/pg-phriday-functions-and-ad...
- deleted 12y ago[deleted]
- wcummings 12y agoUse a transaction maybe?
- rpedela 12y agoI don't know the answer to your question but JSON update is being worked on by the PG community. I think it is unlikely to make 9.5, but we will see. http://www.postgresql.org/message-id/flat/54E00D1F.9060101@dunslane.net#54E00D1F.9060101@dunslane.net http://www.postgresql.org/message-id/flat/54E00D1F.9060101@d...