Had some fun exploring JSON and JSONB features in PostgreSQL recently.
First task was to flatten two tables’ rows into a single field of JSON data. Nice intro to json_agg() and row_to_json().
Where it got real fun was extracting that data back out from JSON to rows & columns with the help of jsonb_path_query().
SELECT
-- Outputs a name on first record, NULL on remaining:
jsonb_path_query(body, '$.first_name') AS fname,
-- Outputs a name on every record:
d1.body->'last_name' AS lname,
-- Generates a record for each movie (ALL array elements via [*]):
jsonb_path_query(d1.body, '$.movies[*].movie_name') AS movies,
-- Extract "movies" object, 1st array element's "movie_name" object:
d1.body #> '{movies,1,movie_name}' AS first_movie
FROM
-- Schema "json":
json.directors_docs AS d1
WHERE
body->>'director_id' = '13'
;
-[ RECORD 1 ]---------------------
fname | "Stanley"
lname | "Kubrick"
movies | "A Clockwork Orange"
first_movie | "Eyes Wide Shut"
-[ RECORD 2 ]---------------------
fname | (null)
lname | "Kubrick"
movies | "Eyes Wide Shut"
first_movie | "Eyes Wide Shut"
-[ RECORD 3 ]---------------------
fname | (null)
lname | "Kubrick"
movies | "The Shining"
first_movie | "Eyes Wide Shut"
The #> operator is interesting, the '$.key' notation is something I always forget to add the ‘$’ to, but all-in-all, I am utterly enamored by PostgreSQL.
