Select
There are many tokens to tweak queries returning sets or aggregators.
Table Expressions
These always have a FROM clause.
SELECT
FROM
WHERE
LATERAL
I really battled with using jsonb_each(utility) until I discovered
SELECT key, value
FROM tictactoe_states, LATERAL jsonb_each(utility)
WHERE id < 10;
LATERAL is primarily useful when the cross-referenced column is necessary for computing the row(s) to be joined. A common application is providing an argument value for a set-returning function.
It turns out LATERAL is implied, so the above can be abbreviated to:
SELECT key, value
FROM tictactoe_states, jsonb_each(utility)
WHERE id < 10;
Implicitly stating LATERAL is good style.
The LATERAL key word is unnecessary in this example, but we use it for clarity.
The order of the tables after FROM is important because I initially tried
SELECT key, value
FROM jsonb_each(utility), tictactoe_states
WHERE id < 10;
Which resulted in
ERROR: column "utility" does not exist
LINE 2: FROM jsonb_each(utility), tictactoe_states
Something that also wasted a couple of hours was learning settof record returning functions such as jsonb_each have to be in the FROM part of the query because
SELECT jsonb_each(utility) FROM tictactoe_states WHERE id < 10;
results in
jsonb_each
------------
(o,50)
(x,50)
(o,50)
(x,50)
(o,50)
(x,50)
(o,50)
(x,50)
(o,50)
(x,50)
(o,50)
(x,50)
(o,50)
(x,50)
(o,50)
(x,50)
(o,50)
(x,50)
(18 rows)
and I’ve yet to figure out how to retrieve elements from these records.
Aggregate Functions
Aggregate functions are not allowed in WHERE