JSONB_TYPEOF in PostgreSQL: Guarding Dynamic JSON Safely

Semi-structured data in JSONB is convenient right up until you call jsonb_array_length on an object or try to add a string to a number. jsonb_typeof returns a JSON value's type as text and lets you branch your logic before PostgreSQL throws an error.

What jsonb_typeof returns

The function takes a single jsonb value and returns text: object, array, string, number, boolean, or null. Take users(id, email, name, country, created_at) with an extra profile jsonb column that an external service fills with arbitrary fields.

SELECT
  u.id,
  jsonb_typeof(u.profile -> 'tags')    AS tags_type,
  jsonb_typeof(u.profile -> 'age')     AS age_type,
  jsonb_typeof(u.profile -> 'address') AS address_type
FROM users u;

Key points:

• -> extracts a nested jsonb, while ->> casts straight to text; jsonb_typeof needs the -> form.
• If the key is missing, -> returns SQL NULL, and jsonb_typeof(NULL) returns SQL NULL too (not the string 'null').
• The string 'null' appears only when the JSON actually holds the literal null.

Guarding jsonb_array_length and arithmetic

jsonb_array_length fails with cannot get array length of a non-array when the value is not an array. Because in semi-structured data tags might arrive as a string or be absent entirely, check the type first.

SELECT
  u.id,
  CASE
    WHEN jsonb_typeof(u.profile -> 'tags') = 'array'
      THEN jsonb_array_length(u.profile -> 'tags')
    ELSE 0
  END AS tag_count
FROM users u;

The same applies to numbers. Suppose orders(id, user_id, amount, status, created_at) gained a meta jsonb column where a discount is sometimes a number and sometimes the string "10%":

SELECT
  o.id,
  o.amount,
  CASE
    WHEN jsonb_typeof(o.meta -> 'discount') = 'number'
      THEN o.amount * (1 - (o.meta ->> 'discount')::numeric)
    ELSE o.amount
  END AS net_amount
FROM orders o;

Without the guard, (o.meta ->> 'discount')::numeric on the value "10%" raises a cast error and takes the whole query down with it.

JSON null vs SQL NULL

This is the most common trap. JSON has its own null, and it does not mean a missing key.

SELECT
  u.id,
  u.profile -> 'phone'               AS raw_value,
  jsonb_typeof(u.profile -> 'phone') AS typ,
  (u.profile -> 'phone') IS NULL     AS sql_is_null
FROM users u;

Three cases for the key phone:

• Key absent: raw_value = SQL NULL, typ = SQL NULL, sql_is_null = true.
• JSON null value: raw_value = 'null'::jsonb, typ = 'null', sql_is_null = false.
• Value "+1...": typ = 'string'.

Gotcha: (u.profile -> 'phone') IS NULL is true ONLY when the key is missing. If the JSON holds the literal null, the expression is false even though there is "no phone". To catch both, test jsonb_typeof(...) IS DISTINCT FROM 'string' or explicitly = 'null'.

Validating semi-structured input

jsonb_typeof fits naturally into CHECK constraints and data-quality filters. Say employees(id, name, manager_id, dept, salary) gained a payload jsonb column and we want to guarantee that skills is an array and level is a number.

ALTER TABLE employees
  ADD CONSTRAINT payload_skills_is_array
  CHECK (
    payload -> 'skills' IS NULL
    OR jsonb_typeof(payload -> 'skills') = 'array'
  );

SELECT id, name
FROM employees
WHERE jsonb_typeof(payload -> 'level') IS DISTINCT FROM 'number';

The second query finds the broken rows where level did not arrive as a number, including JSON null and missing-key cases.

Engine differences:

• MySQL uses JSON_TYPE(col->'$.path') and returns different labels: OBJECT, ARRAY, STRING, INTEGER, DOUBLE, DECIMAL, BOOLEAN, NULL. Numbers split into integer, floating point, and exact decimal (DECIMAL), a level of detail PostgreSQL does not have.
• ClickHouse historically parses JSON with functions like JSONType(json, 'key') (the simdjson extension) or the newer JSON type; there is no single jsonb_typeof, and behavior depends on the version.

Bottom line: jsonb_typeof is a cheap fuse. One CASE or CHECK saves a query from crashing on dirty JSON and makes the difference between JSON null and SQL NULL explicit.