JSON builders, DISTINCT ON, collation & generated columns

# ======================================================================
# Odds and ends unique to PostgreSQL: `JSONObject`/`JSONArray` build JSON from
# columns; `.distinct('author')` is SQL `DISTINCT ON` (one row per author); a
# non-deterministic `db_collation` makes `nickname` comparisons case-
# insensitive; and a `GeneratedField` (`title_upper`) is computed and stored by
# the database on every write.
# ======================================================================

# ----------------------------------------------------------------------
# JSONObject builds a JSON object per row
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(obj=JSONObject(title="title", price="price")).values_list("title", "obj")

# SQL:
#   SELECT "examples_book"."title" AS "title", JSON_OBJECT((('title')::text) VALUE "examples_book"."title", (('price')::text) VALUE "examples_book"."price"
#   RETURNING JSONB) AS "obj"
#   FROM "examples_book"

# Result:
#   Harry Potter: {'price': 19.99, 'title': 'Harry Potter'}
#   The Hobbit: {'price': 14.5, 'title': 'The Hobbit'}
#   The Lord of the Rings: {'price': 29.0, 'title': 'The Lord of the Rings'}
#   Postgres for Authors: {'price': 0.0, 'title': 'Postgres for Authors'}

# ----------------------------------------------------------------------
# JSONArray builds a JSON array per row
# ----------------------------------------------------------------------

# Django:
Author.objects.annotate(arr=JSONArray("name", "rating")).values_list("name", "arr")

# SQL:
#   SELECT "examples_author"."name" AS "name", JSON_ARRAY(("examples_author"."name")::varchar(100), ("examples_author"."rating")::double precision NULL ON NULL
#   RETURNING JSONB) AS "arr"
#   FROM "examples_author"

# Result:
#   J. K. Rowling: ['J. K. Rowling', 4.7]
#   J. R. R. Tolkien: ['J. R. R. Tolkien', 4.9]
#   Karl Müller: ['Karl Müller', 3.8]

# ----------------------------------------------------------------------
# DISTINCT ON: highest-priced book per author
# ----------------------------------------------------------------------

# Django:
Book.objects.order_by("author", "-price").distinct("author") \
    .values_list("author__name", "title", "price")

# SQL:
#   SELECT DISTINCT ON ("examples_book"."author_id") "examples_author"."name" AS "author__name", "examples_book"."title" AS "title", "examples_book"."price" AS "price"
#   FROM "examples_book"
#   INNER JOIN "examples_author" ON ("examples_book"."author_id" = "examples_author"."id")
#   ORDER BY "examples_book"."author_id" ASC, 3 DESC

# Result:
#   J. K. Rowling: Harry Potter ($19.99)
#   J. R. R. Tolkien: The Lord of the Rings ($29.00)
#   Karl Müller: Postgres for Authors ($0.00)

# ----------------------------------------------------------------------
# case-insensitive db_collation match
# ----------------------------------------------------------------------

# Django:
Author.objects.filter(nickname="JO")   # matches stored "Jo"

# SQL:
#   SELECT "examples_author"."id", "examples_author"."name", "examples_author"."bio", "examples_author"."born", "examples_author"."rating", "examples_author"."nickname"
#   FROM "examples_author"
#   WHERE "examples_author"."nickname" = 'JO'

# Result:
#   nickname='JO' matched J. K. Rowling (stored as 'Jo')

# ----------------------------------------------------------------------
# GeneratedField title_upper (stored, computed by PG)
# ----------------------------------------------------------------------

# Django:
Book.objects.values_list("title", "title_upper")

# SQL:
#   SELECT "examples_book"."title" AS "title", "examples_book"."title_upper" AS "title_upper"
#   FROM "examples_book"

# Result:
#   Harry Potter -> HARRY POTTER
#   The Hobbit -> THE HOBBIT
#   The Lord of the Rings -> THE LORD OF THE RINGS
#   Postgres for Authors -> POSTGRES FOR AUTHORS