WIP Patch: Add a function that returns binary JSONB as a bytea

database=# SELECT jsonb_raw_bytes('{"b":12345}'::jsonb);

                     jsonb_raw_bytes                      

----------------------------------------------------------

 \x0101000020010000800d0000106200000028000000018001002909

(1 row)

database=# SELECT jsonb_raw_bytes('{"a":{"b":12345}}'::jsonb->'a');

                     jsonb_raw_bytes                      

----------------------------------------------------------

 \x0101000020010000800d0000106200000028000000018001002909

(1 row)

Some preliminary testing on my own machine shows me that this change has a significant impact on performance.

I used psql to select a jsonb column from all the rows in a table (about 1 million rows) where the json data was roughly 400-500 bytes per record.

database=# \timing

Timing is on.

database=# \o /tmp/raw_bytes_out.txt;

database=# SELECT jsonb_raw_bytes(data) FROM datatable;

Time: 2582.545 ms (00:02.583)

database=# \o /tmp/json_out.txt;

database=# SELECT data FROM datatable;

Time: 5653.235 ms (00:05.653)

Of note is that the size of raw_bytes_out.txt in the example is roughly twice that of json_out.txt so the timing difference is not due to less data being transferred.