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.


  1. How to increment/decrement values?

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

  3. 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?

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

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

  6. 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'

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

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

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

  10. 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?


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

  12. Andrew: An array *can* be updated by a single element:

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

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