Relational Database 7 | JSON Type in SQL, Window Function, Common Table Expression, Function, and

Series: Relational Database

Relational Database 7 | JSON Type in SQL, Window Function, Common Table Expression, Function, and View

JSON Data Type For SQL

Create a table with JSON type

CREATE TABLE tablename (
        colname JSON
);

Get the data type of data in a Column

SELECT pg_typeof(colname) 
FROM tablename;

Get the data in JSON as JSON type

SELECT colname -> '_id'
FROM tablename;

Get the data in JSON as text type

SELECT colname ->> '_id'
FROM tablename;

Convert a table to JSON

SELECT TO_JSON(tablename) 
FROM tablename;

2. Window Function in PostgreSQL

A window function performs a calculation across a set of table rows that are somehow related to the current row. It makes things easier for us because we don’t have to do JOINs if we want to write some specific kinds of queries. By this window function, we don’t have to write GROUP BY any more.

SUM all the values in colname

SELECT SUM(colname) OVER ()
FROM tablename;

SUM the values in colname1 group by colname2

SELECT SUM(colname1) OVER (PARTITION BY colname2)
FROM tablename;

Accumulate the values in colname1 group by colname2

SELECT SUM(colname1) OVER (
     PARTITION BY colname2
     ORDER BY colname1)
FROM tablename;

Add the values in colname1 group by colname2 of the current row and the following row

SELECT SUM(colname1) OVER (
     PARTITION BY colname2
     ORDER BY colname1
     ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM tablename;

Add the values in colname1 group by colname2 between the current row and the following row

SELECT SUM(colname1) OVER (
     PARTITION BY colname2
     ORDER BY colname1
     ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM tablename;

Add the values in colname1 group by colname2 between the preceding row and the last row in this group

SELECT SUM(colname1) OVER (
     PARTITION BY colname2
     ORDER BY colname1
     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM tablename;

Add the values in colname1 group by colname2 between the very first row in this group and the current row (same to accumulate the values in colname1 group by colname2)

SELECT SUM(colname1) OVER (
     PARTITION BY colname2
     ORDER BY colname1
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM tablename;

Calculate the difference between every two lines (Useful for time series data)

SELECT ABS(LAG(colname1) 
   OVER (
      PARTITION BY colname2
      ORDER BY date, colname1) - colname1) AS diff,
FROM tablename;

3. Common Table Expression (CTE)

This is a way to make the table a variable for us to use in the new query!

Useless CTE (just to show how it can be used)

WITH tablename1 AS (SELECT * FROM tablename2)
SELECT *
FROM tablename1;

Grab values from 2 tables

WITH tablename1 AS (SELECT * FROM tablename3),
     tablename2 AS (SELECT * FROM tablename4)
SELECT tablename1.colname1, tablename2.colname2
FROM tablename1, tablename2;

4. Function in PostgreSQL

Create a function that returns a value.

CREATE OR REPLACE FUNCTION function_name (inputarg datatype) 
RETURNS type AS
$$
    BODY OF FUNCTION
$$
LANGUAGE SQL;

Create a function that increases the given number by 1,

CREATE OR REPLACE FUNCTION increment(num int) 
RETURNS int AS 
$$ 
    SELECT num + 1 
$$ 
LANGUAGE SQL;

To test the function,

SELECT * FROM increment(42);

Create a function that returns a table

CREATE OR REPLACE FUNCTION sid_by_name(name VARCHAR) 
RETURNS TABLE (sid INTEGER, sname VARCHAR) AS
$$
    SELECT sid, sname
    FROM students
    WHERE sname = name
$$
LANGUAGE SQL;

Delete a function

DROP FUNCTION IF EXISTS functname;

5. View in PostgreSQL

Views are the shortcuts of the existed or non-existed tables in the database. The view is not physically materialized and we can query a view just as we do with tables. If we conduct the UPDATE / INSERT / DELETE queries for a view, then the relative values in the tables will also be changed.

The difference between the views and the CTEs is that the view can be used many times until it is deleted but the CTE will only work in the defined query.

Create the view of a table

CREATE VIEW viewname AS
SELECT *
FROM tablename
WHERE colname = value;

Delete a view

DROP VIEW IF EXISTS viewname;

Delete a table with a view depending on it

DROP TABLE IF EXISTS tablename CASCADE;