Postgres New JSON data type Support of Static and Dynamic schemas

Now we can create static and dynamic both kind of schema relational table so a new kind of heterogeneous data structure can be created in Relational tables.  Relational database that is much more concerned with standards compliance and extensible than with giving you freedom over how you store data. It uses both dynamic and static schema and allows you to use it for relational data and normalized form storage.

Static Relation data structure in Postgres

CREATE TABLE Project (  id serial PRIMARY KEY,  name varchar,  mgr integer,  is_active boolean );

CREATE TABLE Task(  id serial PRIMARY KEY, name varchar, status boolean, project_id integer,

CONSTRAINT idx_project_id FOREIGN KEY (project_id) REFERENCES project (id) );

Dynamic Relation data structure in Postgres

CREATE TABLE IF NOT EXISTS project ( id serial PRIMARY KEY,  name varchar,  mgr integer,  tasks jsonb , is_active boolean );

Keeping JSON Document inside Postgres Table

INSERT INTO project VALUES (1,'YoungDBA',2,'{"id": 1, "name": "test1","status": "Done","date": "2018-01-18", "is_active": "True"}'); 

INSERT INTO project VALUES (2,'YoungDBA',2,'{"id": 1, "name": "test1","status": "Done","date": "2018-01-18", "is_active": "True"}'); 

INSERT INTO project VALUES (3,'YoungDBA',2,'{"id": 1, "name": "test1","status": "Done","date": "2018-01-18", "is_active": "True"}');


Now aggregation from static and dynamic data is easier than than documentdb 

SELECT visitor_id, SUM(CAST(properties->>'amount' AS integer)) AS total

FROM events WHERE CAST(properties->>'amount' AS integer) > 0 GROUP BY visitor_id;

SELECT browser->>'name' AS browser, count(browser)

FROM events GROUP BY browser->>'name';

SELECT AVG(CAST(browser->'resolution'->>'x' AS integer)) AS width,

AVG(CAST(browser->'resolution'->>'y' AS integer)) AS height FROM events;

Thanks for reading

Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757