Semi-structured data analysis
AnalyticDB for PostgreSQL supports two data types for semi-structured data: JSON and JSONB. Both enforce JSON syntax validation on write, rejecting malformed input before it reaches storage. JSON data can also be stored as the TEXT type; the dedicated JSON and JSONB types add validation and specialized functions for querying and constructing JSON values.
JSON vs JSONB
JSON stores an exact copy of the input text. Every query reparses the raw text from scratch.
JSONB stores data in a decomposed binary format. The binary conversion adds a small overhead on write, but eliminates reparsing on every read—making JSONB significantly faster to query.
The two types handle input text differently:
| Behavior | JSON | JSONB |
|---|---|---|
| Storage format | Input text (exact copy) | Binary format |
| Insignificant whitespace | Preserved | Discarded |
| Key order | Preserved | Not preserved |
| Duplicate keys | All stored; last value returned on query | Deduplicated; only last value retained |
The following example shows how duplicate keys behave differently between the two types:
-- JSON preserves all duplicate keys; the last value is returned on query
SELECT '{"a": 1, "a": 2}'::json;
-- json
-- -------------------
-- {"a": 1, "a": 2}
-- JSONB deduplicates; only the last value is retained
SELECT '{"a": 1, "a": 2}'::jsonb;
-- jsonb
-- --------
-- {"a": 2}When to use each type
Use JSONB in most cases. Reads are faster than with JSON, with no meaningful difference in application behavior.
Use JSON only when you need to preserve the exact original text—for example, when downstream systems or legacy applications depend on specific key ordering or whitespace.
How AnalyticDB for PostgreSQL stores JSON data
When you write data in JSON or JSONB format to an instance, AnalyticDB for PostgreSQL validates the input against JSON rules to reject invalid writes. AnalyticDB for PostgreSQL also provides functions to construct JSON values in columns targeted for JSON operations.