JSON - Java Script Object Notation , Postgresql support JSON Data type since 9.2 version. There are some predefined function and operators to access the JSON data. The -> operator returns the key of JSON column. The ->> operator returns the value of JSON Column.
Creating a pure JSON table
To create a pure JSON table you need to provide a single field with the type JSONB:
You should also create a basic index:
At this point you can insert data in to the table and query it efficiently.
Querying complex JSON documents
Taking a complex JSON document in a table:
Query for a top-level element:
Query for a simple item in an array:
Query for an object in an array:
Query for a nested object:
Performance of @> compared to -> and ->>
It is important to understand the performance difference between using @>, -> and ->> in the WHERE part of the query. Although these two queries appear to be broadly equivalent:
the first statement will use the index created above whereas the latter two will not, requiring a complete table scan.
It is still allowable to use the -> operator when obtaining resultant data, so the following queries will also use the index:
Using JSONb operators
Creating a DB and a Table
Populating the DB
Lets see everything inside the table books:
-> operator returns values out of JSON columns
Selecting 1 column:
Selecting 2 columns:
-> vs ->>
The -> operator returns the original JSON type (which might be an object), whereas ->> returns text.
Return NESTED objects
You can use the -> to return a nested object and thus chain the operators:
Select rows based on a value inside your JSON:
Notice WHERE uses -> so we must compare to JSON '"Dharma Bums"'
Or we could use ->> and compare to 'Dharma Bums'
Find rows based on the value of a nested JSON object:
A real world example
We’re going to store events in this table, like pageviews. Each event has properties, which could be anything (e.g. current page) and also sends information about the browser (like OS, screen resolution, etc). Both of these are completely free form and could change over time (as we think of extra stuff to track).
Now lets select everything:
JSON operators + PostgreSQL aggregate functions
Using the JSON operators, combined with traditional PostgreSQL aggregate functions, we can pull out whatever we want. You have the full might of an RDBMS at your disposal.