Working with JSON Data in PostgreSQL.
In this article i am going to show the use of JSON data type for storing data in PostgresSQL and thereby querying data using different function and operators in simple and efficient manner.
What is PostgreSQL?
PostgreSQL (pronounced as post-gress-Q-L) is an open source relational database management system (DBMS) developed by a worldwide team of volunteers.PostgreSQL supports native JSON data type since version 9.2.
It provides many functions and operators for manipulating JSON data.In traditional relational Database we would had a hectic task of maintaining multiple table and records for large sets of data, Using JSON data type in PostgreSQL we can store unstructured data in the form of JSON and use in chunk of data in single store and query data efficiently using in-built JSON Operators & Functions.
Creating Table Querying JSON data in PostgreSQL.
First Let’s Create a test table “Employees” with two columns “Id” of type int and “Employee_data” as Jsonb.
create table employees
(
id serial not null
constraint employee1_pk
primary key,
employee_data jsonb
);
Now Lets populate the table with test data.
insert into employees values (1,’{“name”: “Steve”, “Salary”: “30000”,”Allowance”:[{“Food”:”1200"},{“Travel”:2500}], “Department”: [“HR”]}’);
insert into employees values (2,’{“name”: “Mark”, “Salary”: “25000”,”Allowance”:[{“Food”:”1000"},{“Travel”:1800}], “Department”: [“HR”, “Marketing”]}’);
insert into employees values (3,’{“name”: “Robson”, “Salary”: “40000”,”Allowance”:[{“Food”:”600"},{“Travel”:2000}], “Department”: [“HR”, “Billing”,”Finance”]}’);
The Employee_data column is populated with object containing name,salary,Allowance which is list of object,and Department which is a List os string.
Querying JSON data
Querying JSON data is same like other native data type where we use SELECT keyword.
PostgreSQL provides two native operators -> and ->> to help you query JSON data.
The operator -> returns JSON object field by key.
The operator ->> returns JSON object field by text.
Query to Select Name Field from records
select employee_data->’name’ from employees
‘->’ Operator will return the value as JSON object.
rewriting the same query with ‘->>’ operator
select employee_data->>’name’ from employees
‘->>’ operator will return value as a string as the table shows.
Now lets implement the JSON operator inside the where cluse.To search the record with specific name we can do,
select employee_data from employees where employee_data->>’name’=’Steve’
this will return the record with name equals to steve.
Querying JSON Data with object inside the list
select * from employees
where employee_data->’Allowance’ @> ‘[{“Food”:”1200"}]’ or employee_data->’Allowance’ @> ‘[{“Travel”:2500}]’
or employee_data->’Allowance’ @> ‘[{“Travel”:2000}]’
This query will return record with Allowance List containing object with the above condition.
output:
Querying Min,Max and sum of salary
SELECT MIN (CAST ( employee.employee_data->>’Salary’ as integer)) from employee
this query will return minimum salary of employee i.e 25000
SELECT
Max (CAST ( employee.employee_data->>’Salary’ as integer)) from employee
this query will return maximum salary of employee i.e 40000.
SELECT ,SUM( CAST ( employee.employee_data->>’Salary’ as integer) )
this query will return sum of salaries i.e 95000.
Listing all the Department From the table
SELECT jsonb_array_elements(e.employee_data>’Department’) as “Depts” from employees e
Listing distinct department from the Employee Table
select distinct ( jsonb_array_elements(e.employee_data->’Department’)) as “Depts” from employees e.
Retriving Name and salary of employee who has ‘Finance’ department
select employees.employee_data->>’name’ as “Name”,employee_data->>’Salary’ as “Salary” from employees where (employee_data ->’Department’)::jsonb ? ‘Finance’;
output:
RetrivingEmployee who has more than one department
SELECT * FROM employee WHERE JSONB_ARRAY_LENGTH(employee_data->’Department’) > 1;
output:
Retriving Employee whose Allowance Travel cost is greater or equal to 2000
select * from “employees” u, jsonb_array_elements(u.employee_data->’Allowance’) as obj where cast( obj->>’Travel’ as integer)>= 2000;
output:
Querying Employee Name and salary based on allowance value
SELECT employee_data->’name’ as “Name”,employee_data->’Salary’ “Salary”FROM employees WHERE employees.employee_data->’Allowance’ @> ANY (ARRAY [‘[{“Food”: “600”}]’, ‘[{“Travel”: 1800}]’]::jsonb[]);
This query will return record containing Allowance of food having value 600 or travel having value 1800.
output:
So, this was all about querying JSON data using PostgreSQL in a simple and efficient manner which gives us relief from traditional relational based multiple table data storage system.In this article i showed the basic JSON data querying technique, hope this is beneficial for those thinking about using PostgreSQL in their Project.
HAPPY CONTRIBUTING!!!
