PostgreSQL JSONB: Evolution and Benefits

SQL is a vibrant and versatile declarative database and programming
language. Development of the language started in 1972, first as
SQUARE, and from 1973 on as SEQUEL (Structured English Query
Language). In 1977, SEQUEL became SQL because of a trademark
dispute.

Why do we need SQL?

A single-line answer to this question is “Moving Computation Close
to the Data”.

• Let the database system operate over (high-volume) data in native DBMS format
• Fetch the—typically few or even single—result row(s) into the Programming Language heap, perform
  lightweight in-heap
  postprocessing (only if needed)

Introduction to JSONB and JSON data types.

In 2016 Postgres introduced JSON-SQL interoperability. JSON values
may be constructed/traversed and held in table cells thereby
justifying 1NF to be intact.

There are 2 types of JSON data types supported by Postgres.

• Jsonb – The binary format of JSON
• Json – JavaScript Object Notation

This is what official docs say:

‘There are two JSON data types: json and jsonb. They accept almost
identical sets of values as input. The major practical difference is
one of efficiency. The json data type stores an exact copy of the
input text, which processing functions must reparse on each
execution; while jsonb data is stored in a decomposed binary format
that makes it slightly slower to input due to added conversion
overhead, but significantly faster to process, since no reparsing is
needed. jsonb also supports indexing, which can be a significant
advantage.’

Let us see the difference between JSONB and JSON data types by looking at an example

JSONB

 
    VALUES ( 1, '{ "b":1, "a":2 }' ::jsonb),
    (2, '{ "a":1, "b":2, "a":3 }' ),
    (3, '[ 0, false,null ]' );
             

Output –

Let us understand the jsonb data type by analyzing the following the
Output

• The data stored is ordered: In the first row, the JSON value present in the query is ‘{
  “b”:1, “a”:2 }’ but the data stored in
  the cell is {“a”: 2, “b”: 1}.
• Duplicate keys are not retained: In the second row, the JSON value present in the query
  is { “a” :1, “b” :2, “a” :3 } but the data stored in the cell {“a”: 3, “b” : 2}.
• The extra spaces or paddings in the strings are stripped off: In the third row, the JSON
  value present in the query is ‘[ 0,false,null ]” but the data stored in the cell [0, false,
  null].

JSON

 
    VALUES ( 1, '{ "b":1, "a":2 }' ::json),
    (2, '{ "a":1, "b":2, "a":3 }' ),
    (3, '[ 0, false,null ]' );
             

Output –

Let us understand the json data type by analyzing the following the
Output

• The data stored is not ordered: The values in the query and output are the same ‘{“b”: 1,
  “a”: 2}’.
• Duplicate keys are retained: The values in the query and output are the same ‘{“a”: 1,
  “b”: 2, “a”:3}’
• The extra spaces or paddings in the strings are not stripped off: In The values in the
  query and output are the same ‘[ 0, false, null ]’

NOTE: jsonb is widely and globally used. This article will mainly
concentrate on jsonb, hereby just referred to as json

Navigating the JSON.

There are 2 ways to navigate the JSON values

• Using subscripting
• Using path syntax
• Via -> and ->>

1. Navigation using subscripting

 
    SELECT ('{ "a":0, "b": { "b1":[1,2], "b2":3 } }' :: jsonb)['b']['b1'][1] :: int + 40;
             

OUTPUT – 42

WORKING –

The input json value is { “a”: 0, “b”: { “b1”: [ 1, 2 ], “b2”: 3 } }

Accessing the element b1 – json[‘b’][‘b1’]

2. Navigation using path syntax

 
    SELECT j
    FROM   jsonb_path_query('{ "a":0, "b": { "c":[1,2], "d":3 } }' :: jsonb,
    '$') AS j;
             

Output – “{“a”: 0, “b”: {“c”: [1, 2], “d”: 3}}”

Consider the json value

3. Navigation using -> and ->>

-> yields back a jsonb value, permits further navigation using -> or
->> ->> yields a text value (cast to atomic type for further
computation)

 
    SELECT ('{ "a":0, "b": { "b1":[1,2], "b2":3 } }' :: jsonb -> 'b' ->> 'b2') :: int + 40;
             

{ “a”:0, “b”: { “b1”:[1,2], “b2”:3 } } -> ‘b’ // yields {
“b1”:[1,2], “b2”:3 } of type jsonb

{ “a”:0, “b”: { “b1”:[1,2], “b2”:3 } } -> ‘b’ ->> ‘b3’ // yields
atomic value – 3

Bridge between JSON and SQL

JSON to Table Turn the fields and/or nested values inside JSON
object into tables which we can query

• jsonb_each(o) – separate key and values
• jsonb_array_elements(a) – get array elements into a table

 
    SELECT jsonb_each('{ "a":0, "b":2, "c":3 }' :: jsonb)
             

OUTPUT
(a, 0)
(b, 2)
(c, 3)

 
    SELECT jsonb_array_elements('[ 0, false,null ]' :: jsonb)
             

OUTPUT

Table to JSON Convert single SQL row into SQL object

• row_to_json(･)::jsonb – Convert a single table row to a json value
• array_to_json(array_agg(･))::jsonb

 
    SELECT row_to_json(t):: jsonb
    FROM (VALUES (false,   0),
            (true,	1),
                (true,	2)) AS t(a, b)
             

OUTPUT

 
    SELECT array_to_json(array_agg(t.a))
    FROM (VALUES ('{ "b":1}' ::jsonb),
   		  ('{ "a":2}' )) AS t(a)
             

OUTPUT
[{“b” : 1}, {“a”, 2} ]

Conclusion

It’s really exciting to learn about the increasing support of JSON
in Postgres. It will definitely make developers more familiar with
the benefits of storing data as JSON and consequently make Document
Databases also more popular. Many tools and frameworks in the market
already offer support for JSON data, and as the adoption of Postgres
JSONB increases, it should become a standard feature The Evolution
of JSONB has just started. The sky’s the limit