JSONB_TYPEOF en PostgreSQL: trabajar con JSON dinamico sin romper

Los datos semiestructurados en JSONB son comodos hasta que llamas a jsonb_array_length sobre un objeto o intentas sumar una cadena con un numero. jsonb_typeof devuelve el tipo de un valor JSON como texto y te deja ramificar la logica antes de que PostgreSQL lance un error.

Que devuelve jsonb_typeof

La funcion recibe un unico valor jsonb y devuelve texto: object, array, string, number, boolean o null. Partimos de users(id, email, name, country, created_at) con una columna extra profile jsonb que un servicio externo rellena con campos arbitrarios.

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;

Puntos clave:

• -> extrae un jsonb anidado, mientras que ->> convierte directamente a text; jsonb_typeof necesita la forma ->.
• Si la clave no existe, -> devuelve NULL de SQL, y jsonb_typeof(NULL) tambien devuelve NULL de SQL (no la cadena 'null').
• La cadena 'null' aparece solo cuando el JSON contiene de verdad el literal null.

Proteger jsonb_array_length y la aritmetica

jsonb_array_length falla con cannot get array length of a non-array cuando el valor no es un array. Como en datos semiestructurados tags puede llegar como cadena o faltar por completo, comprueba el tipo primero.

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;

Lo mismo vale para los numeros. Supongamos que orders(id, user_id, amount, status, created_at) gano una columna meta jsonb donde el descuento a veces es un numero y a veces la cadena "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;

Sin la proteccion, (o.meta ->> 'discount')::numeric sobre el valor "10%" lanza un error de conversion y tumba toda la consulta.

JSON null frente a SQL NULL

Esta es la trampa mas comun. JSON tiene su propio null, y no equivale a una clave ausente.

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;

Tres casos para la clave phone:

• Clave ausente: raw_value = NULL de SQL, typ = NULL de SQL, sql_is_null = true.
• Valor JSON null: raw_value = 'null'::jsonb, typ = 'null', sql_is_null = false.
• Valor "+34...": typ = 'string'.

Gotcha: (u.profile -> 'phone') IS NULL es cierto SOLO cuando falta la clave. Si el JSON contiene el literal null, la expresion es falsa aunque "no haya telefono". Para capturar ambos casos, prueba jsonb_typeof(...) IS DISTINCT FROM 'string' o explicitamente = 'null'.

Validar entradas semiestructuradas

jsonb_typeof encaja de forma natural en restricciones CHECK y filtros de calidad de datos. Supongamos que employees(id, name, manager_id, dept, salary) gano una columna payload jsonb y queremos garantizar que skills sea un array y level un numero.

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';

La segunda consulta encuentra las filas rotas donde level no llego como numero, incluyendo los casos de JSON null y de clave ausente.

Diferencias entre motores:

• MySQL usa JSON_TYPE(col->'$.path') y devuelve etiquetas distintas: OBJECT, ARRAY, STRING, INTEGER, DOUBLE, DECIMAL, BOOLEAN, NULL. Los numeros se dividen en enteros, de coma flotante y decimales exactos (DECIMAL), un nivel de detalle que PostgreSQL no tiene.
• ClickHouse historicamente analiza JSON con funciones como JSONType(json, 'key') (la extension simdjson) o el nuevo tipo JSON; no hay un unico jsonb_typeof, y el comportamiento depende de la version.

En resumen: jsonb_typeof es un fusible barato. Un CASE o un CHECK salva una consulta de caer con JSON sucio y hace explicita la diferencia entre JSON null y SQL NULL.