---

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

Back To Blogs

---