Wednesday, May 13, 2015

New jsonb features for 9.5.

Yesterday I committed some new features for jsonb. These are:
  • 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.
This was based Dmitry Dolgov's jsonbx extension for 9.4, which in turn was based on earlier work by Oleg Bartunov, Teodor Sigaev and Alexander Korotkov on nested hstore. Petr Jelinek also did some useful review and tidy up work on the core features. So, a good team effort.
at

15 comments:

  1. UnknownMay 14, 2015 at 10:30 PM

    How to increment/decrement values?

    ReplyDelete
  2. dorianMay 15, 2015 at 8:14 AM

    Yeah 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.

    ReplyDelete
  3. Andrew DunstanMay 15, 2015 at 8:30 AM

    Mongo 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?

    ReplyDelete
  4. UnknownMay 15, 2015 at 5:56 PM

    I'm not ะก developer, but would very much like to use PostgreSQL instead of MongoDB, but it still inferior MongoDB in JSON functionality

    ReplyDelete
  5. Andrew DunstanMay 15, 2015 at 6:04 PM

    There are lots of ways to contribute to the project besides writing code. For example, you can contribute funding to people who DO write C.

    ReplyDelete
  6. UnknownMay 16, 2015 at 7:32 AM

    I understand you, but I'm only rich ideas.
    There 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'

    ReplyDelete
  7. Andrew DunstanMay 16, 2015 at 9:02 AM

    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.

    ReplyDelete
  8. UnknownMay 16, 2015 at 9:09 AM

    PostgreSQL users like to work with keys JSON as the to the columns of the table.

    ReplyDelete
  9. Andrew DunstanMay 16, 2015 at 9:50 AM

    You 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.

    ReplyDelete
  10. Foo BarMay 20, 2015 at 11:46 AM

    Hi Andrew!

    Would 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!

    ReplyDelete
  11. Andrew DunstanMay 20, 2015 at 12:05 PM

    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.

    ReplyDelete
  12. Peter GeogheganMay 24, 2015 at 2:55 PM

    Andrew: An array *can* be updated by a single element: http://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-MODIFYING

    ReplyDelete
  13. Andrew DunstanMay 24, 2015 at 3:24 PM

    Oh, yes, Peter is right of course. Apologies to all, I must have been having a bit of brain fade.

    ReplyDelete
  14. cc youngJune 1, 2015 at 6:32 AM

    similar to getting pk back, is there any way to use "returning"? eg, "insert into t1(...) values(...) returning *" - this solves a lot of problems.

    ReplyDelete