PostgreSQL 9.4: The JSON data type is Awesome (Part 1/3)

This is one of the interesting topic for me, now with the new version of PostgreSQL 9.4 we can store JSON formatted data into RDBMS Products.

You can visit this article, on the overview of PostgreSQL 9.4 features.

Now We can say, we have PostgreSQL 9.4 with the RDBMS + NOSQL concepts.

We can also store JSON data as text, but the JSON Data types have the advantage of enforcing that each stored value is valid according to the JSON rules.

PostgreSQL 9.4 also introduced some set of JSON specific functions and operators.

Actually there are two JSON data types:

json: It stores an exact copy of the input json text, while processing functions muse reparse on each execution. Our insertion process may faster by using this json data type.
The json data type also preserves a white space between tokens and order of the keys because it stores an exact copy of json input text.

It stores the JSON formatted data in a decomposed binary format so input slightly slower because of the conversion overhead.
The jsonb data type does not preserve white space and order of object keys.
Automatically, It also removes a duplicate key from the input string and keep last value only.

If we do not have any problem with ordering of object key, we should use jsonb data type to store JSON formatted data.

Below is a small practical demonstration of this:
First, create sample table with jsonb data type:

Insert correct JSON formatted data:
Please also test and try to insert some incorrect JSON formatted data, you will get an error like: invalid input syntax for type json.

SELECT data by searching the key value using WHERE clause:

The Result: Return only one record.

Postgre SQL JSON Data Type

Anvesh Patel

Leave a Reply

1 Comment on "PostgreSQL 9.4: The JSON data type is Awesome (Part 1/3)"

Notify of
Sort by:   newest | oldest | most voted

Your place is valueble for me. Thanks!