- jsonb_pretty(jsonb) produces nicely indented json output.
- jsonb || jsonb concatenates two jsonb values.
- jsonb - text removes a key and its associated value, or a matching array element, from the json
- jsonb - int removes the designated array element
- jsonb - text[] removes a key and associated value or array element at the designated path
- jsonb_replace(jsonb,text[],jsonb) replaces the array element designated by the path or the value associated with the key designated by the path with the given value.
Wednesday, May 13, 2015
New jsonb features for 9.5.
Yesterday I committed some new features for jsonb. These are:
Subscribe to:
Post Comments (Atom)
How to increment/decrement values?
ReplyDeleteYeah Andrew, it needs a way to have increment/decrement/append/append-to-set/set/unset (and also work with NULL, to create them and not fail if they don't exist) like mongodb does to be really comparable to it.
ReplyDeleteMongo equivalence isn't necessarily our design goal. This is Open Source. If you want features, feel free to contribute. Postgres improves incrementally. That's part of what's made it so stable over a very long period. I'm a bit curious about append-to-set since JSON doesn't have a set type, and since sets are mathematically conceptually unordered. Is this simply append-to-list-if-not-present?
ReplyDeleteI'm not ะก developer, but would very much like to use PostgreSQL instead of MongoDB, but it still inferior MongoDB in JSON functionality
ReplyDeleteThere are lots of ways to contribute to the project besides writing code. For example, you can contribute funding to people who DO write C.
ReplyDeleteI understand you, but I'm only rich ideas.
ReplyDeleteThere is the perfect way to make the whole desired functionality, you need to implement support for syntax:
// increment
UPDATE SET field->'key' = field->'key' + 1
// append
UPDATE SET field->'key' = field->'key' || 'string'
That's not going to happen. The target of a SET needs to be a column name, not a sub-column expression, according to the SQL standard, as I understand it.
ReplyDeletePostgreSQL users like to work with keys JSON as the to the columns of the table.
ReplyDeleteYou speak on behalf of all Postgres users? No, please stop with this nonsense. We haven't provided a facility like this for sub-fields of other stuctured objects, such as arrays. If we start then we can revisit this, but it seems very unlikely. I think we need to end this discussion.
ReplyDeleteHi Andrew!
ReplyDeleteWould there be interest in the community for an overlap (have elements in common) operator ( && ) for jsonb?
That operator exists for array types and jsonb already supports the contains ( @> ) and contained by ( <@ ) operators that are also present in array types.
I was wondering: is the lack of an overlap operator a design decision or just lack of time/resources to add it?
Thanks!
Well, 9.5 is definitely closed for this sort of thing. I would ask on the pgsql-hackers mailing list, or possibly on pgsql-general, to see how much interest there would be in such an operator. I don't know of any plans anyone else has for it.
ReplyDeleteAndrew: An array *can* be updated by a single element: http://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-MODIFYING
ReplyDeleteOh, yes, Peter is right of course. Apologies to all, I must have been having a bit of brain fade.
ReplyDeletesimilar to getting pk back, is there any way to use "returning"? eg, "insert into t1(...) values(...) returning *" - this solves a lot of problems.
ReplyDeleteWhat does this have to do with jsonb?
Delete