JSON vs JSONB Storage, Operators, SQL/JSON, Construction, and Extraction

Learning outcomes

ServiceHub receives equipment telemetry and third-party work-order metadata whose optional fields change faster than the relational core schema. PostgreSQL can store that boundary data as JSON, but the modeling decision is not “JSON everywhere.” Stable identifiers, foreign keys, status, money, and frequently filtered attributes still belong in typed columns when relational constraints and statistics matter.

1. Build a small ServiceHub document lab

DROP TABLE IF EXISTS app.ch17_json_lab;CREATE TABLE app.ch17_json_lab (    work_order_id bigint PRIMARY KEY,    raw_payload json NOT NULL,    attributes  jsonb NOT NULL);INSERT INTO app.ch17_json_lab VALUES(  17001,  '{ "sensor":"pump-A", "reading":41, "reading":42, "note": null }',  '{ "sensor":"pump-A", "reading":41, "reading":42, "note": null,     "parts":[{"sku":"P-10","qty":2},{"sku":"V-20","qty":1}] }');

The json type validates JSON syntax and stores the original text representation, including insignificant whitespace, key order, and duplicate object keys. Processing must reparse that text. jsonb stores a decomposed binary representation, does not preserve whitespace/object-key order, and keeps only the last value for a duplicate object key. Most indexing/search operators target jsonb.

SELECT raw_payload,       raw_payload -> 'reading' AS json_reading,       attributes,       attributes -> 'reading' AS jsonb_readingFROM app.ch17_json_labWHERE work_order_id = 17001;

Both extraction expressions resolve the duplicate reading to the last value, but the stored json text can still display both duplicate keys while jsonb has normalized the object. Never use object key order as application semantics.

2. Extraction operators return different SQL types

SELECT  attributes ->  'sensor' AS sensor_jsonb,  pg_typeof(attributes -> 'sensor') AS sensor_jsonb_type,  attributes ->> 'sensor' AS sensor_text,  pg_typeof(attributes ->> 'sensor') AS sensor_text_type,  attributes #>  '{parts,0}' AS first_part_jsonb,  attributes #>> '{parts,0,sku}' AS first_sku_textFROM app.ch17_json_labWHERE work_order_id = 17001;

-> and #> preserve JSON/JSONB values. ->> and #>> extract SQL text. That distinction matters for comparison, sorting, casts, and whether nested structure is preserved.

Extraction is forgiving about missing structure: if a requested key/element/path does not exist, these operators return SQL NULL rather than throwing an error.

3. Missing key, JSON null, and SQL NULL are not one state

SELECT  attributes ? 'note'                    AS note_key_exists,  attributes -> 'note'                   AS note_jsonb,  jsonb_typeof(attributes -> 'note')     AS note_json_type,  attributes ? 'missing'                 AS missing_key_exists,  attributes -> 'missing'                AS missing_jsonb,  jsonb_typeof(attributes -> 'missing')  AS missing_json_typeFROM app.ch17_json_labWHERE work_order_id = 17001;

For note, key existence is true and the JSONB value is JSON null; jsonb_typeof returns the text 'null'. For missing, the key-existence operator is false and the extraction itself is SQL NULL, so jsonb_typeof also receives SQL NULL. The existence test is what preserves the distinction.

SELECT  JSON_VALUE(attributes, '$.sensor') AS sensor,  JSON_VALUE(attributes, '$.reading' RETURNING integer) AS reading,  JSON_VALUE(attributes, '$.note') AS note_as_sql_null,  JSON_VALUE(attributes, '$.missing' DEFAULT 'absent' ON EMPTY) AS missing_valueFROM app.ch17_json_labWHERE work_order_id = 17001;

In PostgreSQL 18, JSON_VALUE expects one scalar result. A JSON null result becomes SQL NULL. Missing path can be handled with ON EMPTY, while conversion/path errors can be handled separately with ON ERROR.

4. JSON_QUERY preserves objects and arrays

SELECT  JSON_QUERY(attributes, '$.parts' RETURNING jsonb) AS parts,  JSON_QUERY(attributes, '$.parts[*].sku'             RETURNING jsonb WITH ARRAY WRAPPER) AS skusFROM app.ch17_json_labWHERE work_order_id = 17001;

Use JSON_QUERY when the result can be an object/array or a sequence that needs wrapping. Use JSON_VALUE for one scalar. This is clearer than guessing whether a legacy extraction operator returns text or structure in a complex query.

5. JSON_TABLE turns document arrays into relational rows

SELECT j.work_order_id, part.ordinality, part.sku, part.qtyFROM app.ch17_json_lab AS jCROSS JOIN LATERAL JSON_TABLE(  j.attributes,  '$.parts[*]'  COLUMNS (    ordinality FOR ORDINALITY,    sku text PATH '$.sku',    qty integer PATH '$.qty'  )) AS partORDER BY j.work_order_id, part.ordinality;

JSON_TABLE is lateral to the source row and exposes typed columns. It is valuable at ingestion/analysis boundaries, but repeatedly shredding the same high-volume document fields can be a signal that those fields deserve ordinary relational storage.

6. Construct JSON with SQL-aware constructors

SELECT JSON_OBJECT(         'work_order_id' VALUE 17001,         'status' VALUE 'assigned',         'optional_note' VALUE NULL         ABSENT ON NULL         WITH UNIQUE KEYS         RETURNING jsonb       ) AS document;SELECT jsonb_build_object(         'work_order_id', 17001,         'started_at', clock_timestamp(),         'parts', jsonb_build_array(            jsonb_build_object('sku','P-10','qty',2),            jsonb_build_object('sku','V-20','qty',1)         )       ) AS document;

Constructors escape strings and convert SQL scalars correctly. SQL/JSON WITH UNIQUE KEYS rejects duplicate keys; ABSENT ON NULL omits SQL-null-valued pairs instead of emitting JSON null.

-- Do not do this with application/user values:SELECT '{"note":"' || 'He said "stop"' || '"}' AS broken_json_text;-- Correct:SELECT jsonb_build_object('note', 'He said "stop"') AS safe_jsonb;

Manual concatenation confuses SQL quoting, JSON quoting, numeric/boolean typing, and injection boundaries. Build JSON through constructors or parameterized application libraries.

7. Validate untyped JSON text before storage when contracts require it

SELECT  '{"a":1}' IS JSON OBJECT AS is_object,  '{"a":1,"a":2}' IS JSON OBJECT WITH UNIQUE KEYS AS unique_keys,  '[1,2,3]' IS JSON ARRAY AS is_array,  'not json' IS JSON AS valid_json;

The predicate validates textual JSON without first forcing a cast that would throw. This is useful in staging pipelines where invalid input must be classified instead of aborting the whole batch.

8. Cleanup and checkpoint

Authoritative references

These data types and index/operator contracts are version-sensitive. The lesson uses the PostgreSQL 18 primary documentation below.

• PostgreSQL 18 — JSON Types
• PostgreSQL 18 — JSON Functions and Operators
• PostgreSQL 18 — SQL/JSON JSON_TABLE