/*
psql -v ON_ERROR_STOP=1 -U postgres portal
*/

-- OBS: Deletes everything!
\c portal
\set QUIT true
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
\set QUIET false


---------------------- Tables and data ------------------------

-- Tables
CREATE TABLE Users (
  uname TEXT PRIMARY KEY,
  email TEXT UNIQUE);

CREATE TABLE Posts (
  id SERIAL PRIMARY KEY, 
  author TEXT NOT NULL REFERENCES Users,
  created TIMESTAMP NOT NULL,
  content JSONB NOT NULL);


-- Set up some test data
INSERT INTO Users VALUES ('AnaBove', 'ana.bove@chalmers.se');
INSERT INTO Users VALUES ('Nobody', 'nobody@example.com');
INSERT INTO Users VALUES ('Somebody', 'somebody@databases.com');


INSERT INTO Posts VALUES (
    DEFAULT, 
    'AnaBove', 
    CURRENT_TIMESTAMP,
    '{"link" : "https://xkcd.com/327/", "preview":true}' :: JSONB);

INSERT INTO Posts VALUES (
    DEFAULT, 
    'AnaBove', 
    CURRENT_TIMESTAMP,
    '{"link" : "https://en.wikipedia.org/", "preview":false, "size":35}' :: JSONB);

INSERT INTO Posts VALUES (
    DEFAULT, 
    'Nobody', 
    CURRENT_TIMESTAMP,
    '{"link" : "https://example.com/", "preview":false}' :: JSONB
  );

INSERT INTO Posts VALUES (
    DEFAULT, 
    'AnaBove', 
    CURRENT_TIMESTAMP,
    '{"picture" : "funnycat.gif", "prop":{"size":15434}}' :: JSONB);


---------------------- Queries Involving  JSON Documents ------------------------

-- Checks what is in the tables
SELECT * FROM Users;
SELECT * FROM Posts;


-- Returns the value of the link keys in content if any, gives NULL otherwise
SELECT id, content -> 'link' AS url FROM Posts;


-- One way of finding all posts with pictures
SELECT id, content FROM Posts WHERE content->'picture' IS NOT NULL;

-- Another way of finding all posts with pictures
SELECT id, content FROM Posts WHERE content ? 'picture';


-- Finds all posts with enabled preview: compares JSON values
SELECT id, content FROM Posts WHERE (content->'preview') = 'true';

-- Another way of finding all posts with enabled previews: converts to SQL Boolean
SELECT id, content FROM Posts WHERE (content->'preview') :: BOOLEAN ;


-- Selects all post sizes from 'prop', returns a JSONB type, gives NULL otherwise
SELECT id, content, content->'prop'->'size' AS postsize FROM Posts;

-- Can be combined with other SQL stuff:
-- returns 0 if missing size
SELECT id, content, COALESCE(content->'prop'->'size','0') AS postsize 
FROM Posts;

-- converts the resulting value to an SQL number.
-- Will give an error if content.prop.size is not a JSON number
SELECT id, content, COALESCE(content->'prop'->'size','0') :: NUMERIC AS postsize 
FROM Posts;


---------------------- Building JSON/JSONB documents in query results ------------------
-- Turns the non-JSON(B) data in the tables into JSON(B)

-- Use json_build_object to build a JSON object for each user
SELECT created, json_build_object('postid', id, 'user', author) AS jsondata
FROM Posts;

-- Use jsonb_build_object to build a JSONB object for each user
SELECT created, jsonb_build_object('postid', id, 'user', author) AS jsondata
FROM Posts;


-- Use json(b)_build_array to build a JSON(B) array for each user
SELECT created, json_build_array (id, author)  AS jsondata FROM Posts;
SELECT created, jsonb_build_array (id, author)  AS jsondata FROM Posts;


-- Builds a JSON/JSONB array with the authors of all posts
-- Always gives single row, could contain repetition!
SELECT json_agg (author) AS jsonarray FROM Posts;
SELECT jsonb_agg (author) AS jsonarray FROM Posts;


-- Collects some post information for each author
-- For each author, jsonb_agg creates a JSON  array
-- jsonb_build_object creates objects with the desired information in the array
SELECT author, jsonb_agg (jsonb_build_object ('postid', id, 'time', created))
       AS jsondata
FROM Posts
GROUP BY author;


-- Gives an array per user with all the user's posts and their id;
-- uses || to combine the object containing the post id with its content
SELECT author, json_agg (jsonb_build_object ('postid', id) || content)
FROM posts
GROUP BY author;


-- All posts and their information, as one JSON array
SELECT
  json_agg (jsonb_build_object ('id', id, 'author', author, 'created', created)
            || content)
FROM Posts;


-- Builds a JSON document with all information on each user,
-- including an array with the id and time of their posts
-- The list is constructed using a correlated query
-- Coalesce is used to replace SQL null values for users with no posts with JSON empty arrays
SELECT jsonb_build_object(
         'uid', uname,
         'email', email,
         'posts', (SELECT COALESCE (jsonb_agg (jsonb_build_object (
                                                'postid', id, 'time', created)
                                              ),'[]')
	           FROM Posts WHERE author = Users.uname) 
       ) AS user_data
FROM Users;


-------- Testing the different Postgres functions for JSONB Path queries --

-- Recall: returns the value of the link keys in content if any, gives NULL otherwise
SELECT id, content->'link' AS url FROM Posts;

-- Returns only the entries with a 'link' key in content:
SELECT id, jsonb_path_query (content,'$.link') FROM Posts; 

-- This query fails if not all posts contain a link
-- SELECT id, jsonb_path_query (content,'strict $.link') FROM Posts;


-- Other path queries

-- Gives the values of all keys
SELECT jsonb_path_query ('{"name":"foo", "size":500, "type":"jpg"}',
                         'strict $.*');

-- Gives just the first value
SELECT jsonb_path_query_first ('{"name":"foo", "size":500, "type":"jpg"}',
                               'strict $.*');

-- Returns an array with the values of all keys
SELECT jsonb_path_query_array ('{"name":"foo", "size":500, "type":"jpg"}',
                               'strict $.*');
