4 ms·
Postgres cannot (without some wild and wooly SQL) do an increment upset in a JSONB field. I.e. If I wanted to add 1 to a JSONB attribute that may not exist, I c
by DanHulton 8y ago
Postgres cannot (without some wild and wooly SQL) do an increment upset in a JSONB field. I.e. If I wanted to add 1 to a JSONB attribute that may not exist, I cannot do this, not simply.
I'd, of course, love to be wrong about this, but when I looked into it about six months ago, the best solution I could find was a hack that had untested performance characteristics. While in Mongo, it's a very simple, standard operation.
- robbyt 8y agoGenerally speaking, I like to use json tables for inserts only.
- brandur 8y agoSee the `||` operator and `jsonb_set` (use Cmd-F): https://www.postgresql.org/docs/current/static/functions-json.html https://www.postgresql.org/docs/current/static/functions-jso... From the docs for the `||` operator: > Concatenate two jsonb values into a new jsonb value And for `jsonb_set`: > Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true ( default is true) and the item designated by path does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays. You'd use this function in conjunction with an `UPDATE` statement to get the result that you want.
- chatmasta 8y agoIf you are incrementing a field, then by definition you know it exists and is an integer. In that case, what advantage are you gaining by storing it in JSONB? Just factor the integer field out of the JSONB column and into the table schema.
- rpedela 8y agoI deal with messy data often and there are definitely times when a JSON row should contain a field but doesn't. If I also needed to increment the field as part of cleaning the data, it would also be nice to upsert in the cases where the field is missing. Extracting a field into its own column may make sense sometimes, but usually I just need the data to be correct and stored rather than be queried or updated frequently so adding another column is a waste of time and space.
- brightball 8y agoIt seems like you're talking about a couple of different cases here. If you're going to be incrementing a field infrequently, then just resaving the JSONB shouldn't be a big deal. If you're updating a field frequently, having it in it's own column makes more sense and should absolutely be worth the time. It's tremendously more efficient to increment a typed number than it is to increment a piece of a serialized chunk of JSON. When you create a column with postgres that has a null default, it doesn't allocate space for it. It's one of the things that makes postgres so good for huge databases because adding an empty column is an instantaneous operation, rather than a locking operation. On the flip side, when you create a field in JSON you're duplicating the space used just to name the field in every single row. But the short version is, if you're updating something often enough that you're willing to fight around upserting into a JSON column rather that just creating that same column it might be worth it to take a step back and re-evaluate why doing it that way is important to you.
- rpedela 8y ago> If you're updating a field frequently, having it in it's own column makes more sense and should absolutely be worth the time. It's tremendously more efficient to increment a typed number than it is to increment a piece of a serialized chunk of JSON. Yes. When did I say the opposite?
- brightball 8y agoI think I assumed based on your need for increment upsert on a JSON field. My mistake.