JSON and extensions
json against jsonb, containment, GIN indexes and trigrams.
Relational tables suit data with a fixed shape. Some data varies from row to row: a burglary file lists stolen items, a windscreen file none. PostgreSQL stores such data as JSON inside a column, and can still search and index it.
json and jsonb
json | JSONB | |
|---|---|---|
| Stored as | the text as written | a parsed, binary form |
| Keeps | spacing, key order and duplicate keys | one value per key; order not kept |
| Querying | re-parsed each time | fast |
| Indexing | no | GIN indexes |
Use JSONB unless you must keep the document exactly as received.
Paths and containment
->returns a JSON value,->>returns text:doc -> 'items',doc ->> 'adjuster'.@>asks whether a document contains another:doc @> '{"channel": "web"}'.?asks whether an object has a key, or an array a string element.JSONB_ARRAY_ELEMENTSturns an array into rows, so its elements can be filtered and summed.
GIN indexes
A B-tree sorts whole values, which does not help to find documents that contain a key. A GIN index (generalised inverted index) works like a book's index: for each key and value, the list of rows that contain it. It serves @>, ? and full-text search. Its operator class jsonb_path_ops is smaller and faster, but serves only @>.
Extensions
An extension adds types, functions and index methods to a database with CREATE EXTENSION. pg_trgm compares texts by their trigrams, the sets of three-letter sequences they contain. Similarity is the share of trigrams two texts have in common:
a % b is true when the similarity is at least 0.3, the default threshold, so misspellings and missing accents still match.
SELECT doc ->> 'channel' AS channel, COUNT(*) AS files
FROM claim_documents
GROUP BY 1
ORDER BY files DESC;->> makes a JSON value into text that can be grouped like a column.
Check your understanding
Answer every question, then check.