-- Just check that it works
SELECT * FROM Posts;

-- Gives null for rows that have no link property
SELECT id, content -> 'link' AS url FROM Posts;

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

-- Another way of finding all pictures
SELECT * FROM Posts WHERE content ? 'picture';

-- Find all posts with enabled preview
SELECT * FROM Posts WHERE (content->'preview') = 'true';

-- Another way of also finding all posts with enabled previews
SELECT * FROM Posts WHERE (content->'preview') :: BOOLEAN ;

-- Select all post sizes as JSONB documents
SELECT id, content, content->'prop'->'size' AS postsize FROM Posts;

-- Select all post ids and sizes as JSONB documents
-- substitutes 0 if missing
-- converts the resulting document to an number.
-- Will give an error if content.prop.size is not a JSON number
SELECT id,COALESCE(content->'prop'->'size','0') :: NUMERIC AS postsize
FROM Posts;

-- Use jsonb_build_object to build a JSON object from normal SQL values
SELECT id, jsonb_build_object('postid',id,'user',author) FROM Posts;

-- Aggregate the authors of all posts into a JSON array
SELECT jsonb_agg(author) FROM Posts;

-- Find all posts, grouped by authors, using || to combine the content
-- with the other columns into one json object
SELECT author,json_agg(jsonb_build_object('id',id,'created',created) || content)
FROM posts
GROUP BY author;

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

-- Testing the different Postgres functions for JSON Path queries

SELECT jsonb_path_query('{"name":"foo","size":500,"type":"jpg"}','strict $.*');

SELECT jsonb_path_query_array('{"name":"foo","size":500,"type":"jpg"}','strict $.*');

SELECT jsonb_path_query_first('{"name":"foo","size":500,"type":"jpg"}','strict $.*');

-- A simple JSON Path example
SELECT jsonb_path_query(content,'$.link') from Posts; -- 2 rows
SELECT content->'link' from Posts;                    -- 3 rows

-- This fails since not all posts contain a link
SELECT jsonb_path_query(content,'strict $.link') from posts;