Database Reference
In-Depth Information
fact, you might come across their versatility by accident when making a typo in an SQL
statement. Try the following query:
SELECT
x
FROM
census
.
lu_fact_types
As
x
LIMIT
2
;
At first glance, you might think that we left out a
.*
by accident, but check out the result:
x
------------------------------------------------------------------
(86,Population,"{D001,Total:}",d001)
(87,Population,"{D002,Total:,""Not Hispanic or Latino:""}",d002)
Instead of erroring out, the preceding example returns the canonical representation of
a
lu_fact_type
data type. Looking at the first record:
86
is the
fact_type_id
,
Popula
tion
is the
category
, and
{D001,Total:}
is the
fact_subcats
property, which happens
to be an array. Composites can serve as input to several useful functions, among which
are
array_agg
and
hstore
(a function packaged with the hstore extension that converts
a row into a key-value
hstore
object).
If you are using version 9.2 or higher and are building Ajax applications, you can take
ray_to_json
to output a query as a single JSON object. We demonstrate this in
Example 7-16
.
Example 7-16. Query to JSON output
SELECT
array_to_json
(
array_agg
(
f
))
As
cat
FROM
(
SELECT
MAX
(
fact_type_id
)
As
max_type
,
category
FROM
census
.
lu_fact_types
GROUP
BY
category
)
As
f
;
This will give you an output of:
cats
----------------------------------------------------
[{"max_type":102,"category":"Population"},
{"max_type":153,"category":"Housing"}]
Collects all these
f
rows into one composite array of
f
s.
Defines a subquery with name
f
.
f
can then be used to reference each row in
the subquery.
In version 9.3, the
json_agg
function chains together
array_to_json
and
array_agg
,
offering both convenience and speed. In
Example 7-17
, we repeat
Example 7-16
using
json_agg
.
Example 7-17
will have the same output as
Example 7-16
.
