JSON In PostgreSQL: Questions and Answers

This blog post is written as a follow-up for the “Roadmap for JSON in PostgreSQL” webinar with Oleg Bartunov that we hosted in September. It took us a while to finally tackle these questions, but the proverb says ‘better late than never’. 

Q. JSONB sorts keys, doesn’t it?

A. JSONB keys are sorted first by length, which speeds up the comparison of different-length keys, and then by value. Sorting is necessary for binary search.

Q. When do people need textual JSON?

A. We are also wondering when they need it.

Q.​Is it possible to write an arrow operator to jsonpath converter and use it in query optimization?

A. Yes, it’s possible.

Q. Are there any array slicing operators other than [*]? Like take first or take last or take every Nth?

A. JSONPATH supports the following array accessors (slicing operators):

  [*]     -- extract all elements

  [index] -- extract element by index. This index can be a simple numeric literal or complex arithmetic expression. This expression may contain LAST which is equal to (array_size - 1), so [LAST] simply extracts the last element.

  [index1 TO index2] -- extracts the range of elements from index1 to index2.

  [index1, index2, ...] -- extracts several elements by indexes, that can be ranges. For example, [0, 2 TO LAST] will extract all elements excluding the second.

Q. Does JSONB use TOAST? 

A. Yes, of course.

Q. ​I think TOAST is something on a different level than types 🙂 If a tuple is big - it goes to TOAST.

A. Like other PostgreSQL data types, JSONB supports TOAST. If the tuple is big, PostgreSQL tries to TOAST its biggest attributes, so only attributes are TOASTed, not the whole tuple.

Q. If I change a small part of a very large JSONB value, does PostgreSQL replace the entire value, or does it update just the changed portion?

A. When JSONB is being updated, its current implementation iterates through all object keys or array elements creating an in-memory tree-like structure containing replaced values, and then encodes it into a binary JSONB. This JSONB value is stored in the new version of the row, so the other row fields are copied too. When the input JSONB is TOASTed, it should be deTOASTed before modification, and TOASTed back after modification. So the update of TOASTed JSONB for now is not as effective as we would like it to be.

Q. ​I found some cases in my applications where jsonb_array_elements() was quite a lot faster than equivalent jsonb_path_query() ops, is this expected for simpler queries? 

A. If more information is provided, we’ll be able to state whether this is a unique case or expected behavior. We do encourage Alastair McKinley who authored this question to contact us via info@postgrespro.com so we could look into this case and provide some findings.

Q. Is there an external C library to handle JSONB internal format, which makes possible writing drivers/clients receiving binary JSONB data?

A. There is no such a library now. Of course, it is possible to extract JSONB API like it was done already for JSON (see src/common/jsonapi.c). But without an ability to send/receive binary JSONB to/from clients such API extraction doesn't make much sense.

Q. Are the planning support functions (functions -> operators) you showed available in PG 12/13?

A. No, we hope it will be committed into PostgreSQL 14. The patch hasn't been publicated on commitfest yet.

Q. Will jsquery be integrated in PostgreSQL core soon?'

A. We have intention to integrate gin opclasses in PG14.

Q. Copy (format json) - that would be fantastic...

Such features have not even been implemented yet (we have no patches).

Q. I'm still hoping to see the fantastic JSONB compression work from PostgresPro eventually be added into Postgres core. Is this still in the future?

A. Yes, this is still in the future.

Q. Will binary format for JSON in client-server protocol be supported?

A. Not sure about that. There is some security-related issue: we obviously cannot use arbitrary user-supplied JSONB, and we always need to validate the structure of input JSONB and also the structure of the contained numerics. This validation is not implemented yet and it may not be accepted for inclusion into Postgres core.

All these questions were briefly answered by Oleg Bartunov and Ivan Panchenko during the webinar. I have provided a more detailed overview for some aspects within my area of competence. I also strongly recommend you to attend Oleg’s upcoming talk “JSON in Postgres - The Roadmap” at Postgres Build 2020. This presentation will cover our latest developments and priorities for JSON features to be implemented next.