JSONB_TYPEOF no PostgreSQL: lidar com JSON dinamico sem quebrar

Dados semiestruturados em JSONB sao convenientes ate o momento em que voce chama jsonb_array_length sobre um objeto ou tenta somar uma string com um numero. jsonb_typeof retorna o tipo de um valor JSON como texto e permite ramificar a logica antes que o PostgreSQL lance um erro.

O que jsonb_typeof retorna

A funcao recebe um unico valor jsonb e retorna texto: object, array, string, number, boolean ou null. Vamos usar users(id, email, name, country, created_at) com uma coluna extra profile jsonb que um servico externo preenche com 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;

Pontos principais:

• -> extrai um jsonb aninhado, enquanto ->> converte direto para text; jsonb_typeof precisa da forma ->.
• Se a chave nao existe, -> retorna NULL do SQL, e jsonb_typeof(NULL) tambem retorna NULL do SQL (nao a string 'null').
• A string 'null' aparece somente quando o JSON realmente contem o literal null.

Protegendo jsonb_array_length e a aritmetica

jsonb_array_length falha com cannot get array length of a non-array quando o valor nao e um array. Como em dados semiestruturados tags pode chegar como string ou faltar por completo, verifique o tipo antes.

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;

O mesmo vale para numeros. Suponha que orders(id, user_id, amount, status, created_at) ganhou uma coluna meta jsonb onde o desconto as vezes e um numero e as vezes a 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;

Sem a protecao, (o.meta ->> 'discount')::numeric sobre o valor "10%" lanca um erro de conversao e derruba a consulta inteira.

JSON null versus SQL NULL

Esta e a armadilha mais comum. O JSON tem seu proprio null, e ele nao equivale a uma chave 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 a chave phone:

• Chave ausente: raw_value = NULL do SQL, typ = NULL do SQL, sql_is_null = true.
• Valor JSON null: raw_value = 'null'::jsonb, typ = 'null', sql_is_null = false.
• Valor "+55...": typ = 'string'.

Gotcha: (u.profile -> 'phone') IS NULL e verdadeiro SOMENTE quando a chave falta. Se o JSON contem o literal null, a expressao e falsa mesmo que "nao haja telefone". Para pegar os dois casos, teste jsonb_typeof(...) IS DISTINCT FROM 'string' ou explicitamente = 'null'.

Validando entradas semiestruturadas

jsonb_typeof encaixa naturalmente em restricoes CHECK e filtros de qualidade de dados. Digamos que employees(id, name, manager_id, dept, salary) ganhou uma coluna payload jsonb e queremos garantir que skills seja um array e level um 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';

A segunda consulta encontra as linhas quebradas onde level nao chegou como numero, incluindo os casos de JSON null e de chave ausente.

Diferencas entre engines:

• MySQL usa JSON_TYPE(col->'$.path') e retorna rotulos diferentes: OBJECT, ARRAY, STRING, INTEGER, DOUBLE, DECIMAL, BOOLEAN, NULL. Os numeros se dividem em inteiros, de ponto flutuante e decimais exatos (DECIMAL), um nivel de detalhe que o PostgreSQL nao tem.
• ClickHouse historicamente analisa JSON com funcoes como JSONType(json, 'key') (a extensao simdjson) ou o novo tipo JSON; nao existe um unico jsonb_typeof, e o comportamento depende da versao.

Resumindo: jsonb_typeof e um fusivel barato. Um CASE ou um CHECK salva uma consulta de quebrar com JSON sujo e torna explicita a diferenca entre JSON null e SQL NULL.