Evolution of JSONB - PostgreSQL
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
Output -

Let us understand the jsonb data type by analyzing the following the
output
1. 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}.
2. 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}.
3. 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].
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
1. Using subscripting
2. Using path syntax
3. Via -> and ->>
1. Navigation using subscripting
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
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)
{ "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
1. jsonb_each(o) - separate key and values
2. jsonb_array_elements(a) - get array elements into a table
OUTPUT
(a, 0)
(b, 2)
(c, 3)
OUTPUT

Table to JSON Convert single SQL row into SQL object
1.row_to_json(・)::jsonb - Convert a single table row to a json
value
2.array_to_json(array_agg(・))::jsonb
OUTPUT

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