Learning PostgreSQL 11 - Third Edition

Modifying and accessing JSON types

When casting text as a JSON type, the text is stored and rendered without any processing; it will preserve the whitespace, numeric formatting, and element's order details. JSONB doesn't preserve these details; the following example duplicates elimination in JSONB:

WITH test_data(pi) AS (SELECT '{"pi":"3.14", "pi":"3.14" }') SELECT pi::JSON, pi::JSONB FROM test_data;             pi              | pi -----------------------------+---------------- {"pi":"3.14", "pi":"3.14" } | {"pi": "3.14"}(1 row)

JSON objects can contain other nested JSON objects, arrays, nested arrays, arrays of JSON objects, and so on. JSON arrays and objects can be nested arbitrarily, allowing the developer to construct complex JSON documents. The array elements ...