What's New in PostgreSQL 11: JSONB Transforms

In previous posts you could read about INCLUDE indexes and built-in web search . These features will appear in PostgreSQL 11, the release of which, I recall, is scheduled for October. Today I would like to continue the review of new features of the upcoming release, talking about JSONB transforms for PL / Python ( 3f44e3db ) and PL / Perl ( 341e1661 ) languages . Both patches were written by Anton Bykov.

As you may know, PostgreSQL allows you to write stored procedures in various languages, including C , PL / pgSQL , PL / Perl, PL / Python, and others. PostgreSQL also allows you to store JSON, build indexes on it and perform various manipulations with documents. There are two built-in types for storing documents: JSON, which is essentially a regular string, and JSONB, which implements a more efficient binary representation.

However, on the border of these mechanisms there is a small joint. The easiest way to explain it is by example:
')

-- !  `create language`   9.1 create extension plpython3u; CREATE OR REPLACE FUNCTION foo(val jsonb) RETURNS int LANGUAGE plpython3u AS $$ assert(isinstance(val, str)) return len(val) $$; select foo('{"aaa":123, "":456}' :: jsonb); 

As a result, the code will run without errors and return the result of 24. That is, when transferred to a stored procedure, JSONB was encoded into a string, from which we calculated the length.

Fun fact! In fact, the length of the original document is 22 characters, but for some reason we got 24. Oddly enough, there is no bug here, as one would expect. Try as a homework to find out what caused this discrepancy.

So, but we wanted to send a document, not a string. Needless to say, the string can be decoded back:

 CREATE OR REPLACE FUNCTION bar(val_str jsonb) RETURNS int LANGUAGE plpython3u AS $$ import json val = json.loads(val_str) assert(isinstance(val, dict)) plpy.info(sorted(val.items())) return len(val) $$; select bar('{"aaa": 123, "": 456}' :: jsonb); 

Result:

INFO: [('aaa', 123), ('', 456)]
bar
-----
2
(1 row)


It seems to be true. But if you think about it, we are doing a lot of useless work here - first we encode the document and then decode it right there. If the document is large, and the stored procedure is often called, the overhead of this encoding-decoding can be quite large.

The question is, is it possible to simply pass JSONB in ​​Python or Perl in the form of objects that they understand (arrays, dictionaries / hashes, and so on)? It turns out that you can, and these patches do just that. Here is how to use it:

 create extension jsonb_plpython3u; CREATE OR REPLACE FUNCTION baz(val jsonb) RETURNS int LANGUAGE plpython3u TRANSFORM FOR TYPE jsonb AS $$ assert isinstance(val, dict) plpy.info(sorted(val.items())) return len(val) $$; 

By the way, in the opposite direction, that is, to encode the returned documents, you can also:

 CREATE OR REPLACE FUNCTION qux(val int) RETURNS jsonb LANGUAGE plpython3u TRANSFORM FOR TYPE jsonb AS $$ obj = { "val": val } return obj $$; 

For Perl and Python 2, everything is done in a similar way, so I don’t provide the corresponding code. In addition, I will not load you with the subtleties of code operation in various boundary cases, for example, what happens if the returned document contains complex numbers, NaN, ± Inf, and this is all. Interested readers I urge to get acquainted with the code of the patches. They are abundantly smeared with tests that just check for similar boundary cases.

Such is an interesting feature. Of course, the situations in which it is useful, do not arise often. But knowing about the existence of JSONB-transforms, nevertheless, does not hurt.