JSONB and extensions

Not everything fits neatly in columns. An adjuster's file on a claim holds notes, a list of damaged items with their costs, the number of photos, and any fraud flags; a burglary file lists many items, a windscreen file none. Harbour Mutual keeps these files in claim_documents.doc, a JSONB column: JSON, stored in a binary form PostgreSQL can search and index.

SELECT doc
FROM claim_documents
LIMIT 3;

Reading JSONB

SELECT doc ->> 'adjuster' AS adjuster, COUNT(*) AS files
FROM claim_documents
GROUP BY 1
ORDER BY files DESC;

Arrays into rows

JSONB_ARRAY_ELEMENTS turns a JSON array into one row per element (and JSONB_ARRAY_ELEMENTS_TEXT into text), so the items in every file can be counted and summed. Used in FROM after the table, it runs once per row, like a join.

SELECT c.claim_number, i ->> 'item' AS item, (i ->> 'cost')::NUMERIC AS cost
FROM claim_documents d
JOIN claims c USING (claim_id),
     JSONB_ARRAY_ELEMENTS(d.doc -> 'items') i
LIMIT 10;

Changing JSONB

JSONB_SET(doc, '{key}', value) replaces one key's value; || joins two JSON arrays or merges two objects; - removes a key. The column is updated as a whole, with a normal UPDATE.

GIN indexes

A normal (B-tree) index sorts whole values, which does not help to find the documents that contain a key or value. A GIN index (generalised inverted index) records, for every key and value, the rows that contain it, like the index of a book listing every page a word appears on. It serves @> and ?.

CREATE INDEX claim_documents_doc ON claim_documents USING GIN (doc jsonb_path_ops);

SELECT COUNT(*)
FROM claim_documents
WHERE doc @> '{"channel": "broker"}';

Extensions

PostgreSQL grows through extensions, added to a database with CREATE EXTENSION. pg_trgm compares text by its trigrams (sequences of three letters), so it can find names that are spelt nearly the same: a caller's 'Schafer' still finds 'Schäfer'. SIMILARITY(a, b) scores two texts from 0 to 1, and a % b is true when the score is above 0.3.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT name, SIMILARITY(name, 'Sofia Schafer') AS score
FROM customers
WHERE name % 'Sofia Schafer'
ORDER BY score DESC
LIMIT 5;

Other extensions you will meet: pgcrypto for hashing and encryption, uuid-ossp for identifiers (plain GEN_RANDOM_UUID() is now built in), PostGIS for maps. In this lab pg_trgm is installed; managed cloud databases list the extensions they allow.