JSON with jsonb

Storing, querying, updating and indexing JSON documents inside relational tables.

jsonb stores JSON in a decomposed binary form that can be queried and indexed. It lets a table keep flexible attributes — device specs, settings, API payloads — next to normal columns, without a separate document database.

Querying the project specs

-> returns jsonb (note the quotes around "STM32G4"), ->> returns plain text; cast ->> to numeric for arithmetic.

A key that is missing gives NULL: the inverter has no mcu, the ECU has no power_kw.

@> asks 'does the document contain this JSON?'; ? asks 'does it have this key?'.

jsonb_set changes one value and || merges in new keys; the rest of the document is kept.

jsonb_build_object and jsonb_agg build JSON from rows, ready for an API.

json vs jsonb, and indexing

WHERE specs -> 'can' = 'true' WHERE specs @> '{"can": true}' -- or (specs ->> 'can')::boolean -> returns jsonb, so comparing with a text literal is error-prone; containment with @> is clear and can use a GIN index.

What is the difference between -> and ->>?

-> returns the value as jsonb (keeps JSON types and quotes); ->> returns it as text.

Which index type supports @> on a jsonb column?

GIN (the default jsonb_ops operator class, or jsonb_path_ops for smaller indexes that only support @>).