Skip to main content

PG.L6.JSON_NOT_JSONB — A json column where jsonb is almost always meant

  • Category: idiom
  • Level: 6
  • Confidence: deterministic
  • Downtime class: none — nothing here is a schema change
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL

Two types, one letter apart, and a real difference​

json stores the document as text, exactly as it arrived. Every read that looks inside it — every ->>, every containment test, every path expression — re-parses the whole value first. jsonb stores a decomposed binary form: it parses once, on write.

The consequence that actually decides projects is indexing. A GIN index over jsonb answers containment (@>) and key-existence questions directly. json has no operator class for them, so those queries have no index to use at all and read every row — and no amount of query tuning recovers that while the type stays json.

Flagged​

Schema::create('webhooks', function (Blueprint $table) {
    $table->json('payload');
});

Preferred​

Schema::create('webhooks', function (Blueprint $table) {
    $table->jsonb('payload');
});

Laravel's array and json casts work with both types, so the application side needs no change. Converting an existing column is a type change that rewrites the table:

ALTER TABLE webhooks ALTER COLUMN payload TYPE jsonb USING payload::jsonb;

That is a full rewrite under an exclusive lock — on a large table, plan it like any other rewrite rather than running it in the middle of a deploy.

The exception, which is real​

jsonb does not preserve the document as written. It drops insignificant whitespace, it does not keep key order, and it keeps only the last of duplicate keys. json keeps all three, byte for byte.

That matters when the stored document is evidence rather than data:

  • a webhook payload whose signature is computed over the exact bytes received,
  • an audit record that has to round-trip unmodified,
  • an API response kept for a dispute.

Re-serializing any of those from jsonb produces a different document, and for a signature check that means a mismatch. Where that is the case, json is the correct type — ignore the finding deliberately and keep the decision visible:

'audit' => [
    'ignore' => [
        'pairs' => [
            ['rule' => 'PG.L6.JSON_NOT_JSONB', 'objects' => ['public.webhooks']],
        ],
    ],
],

The finding stays counted and listed under what hid it, rather than disappearing from the report.

What this rule does not see​

A column typed as a domain over json reads as the domain's own name in the catalog, not as json, so it is not reported. That limit is stated here rather than left to be discovered: a rule that quietly covered part of its subject would leave you believing the whole of it was checked.

MySQL has no equivalent split — its JSON type is already a binary format — so there is no MySQL sister rule, by absence rather than by oversight.