preferJsonb¶

Group style · Since 0.15.0
Diagnostic lint/preferJsonb

Sources: inspired by eugene/E3

Description¶

Prefer JSONB over JSON types.

JSONB is the binary JSON data type in Postgres that is more efficient for most use cases. Unlike JSON, JSONB stores data in a decomposed binary format which provides several advantages:

Significantly faster query performance for operations like indexing and searching
Support for indexing (GIN indexes)
Duplicate keys are automatically removed
Keys are sorted

The only reasons to use JSON instead of JSONB are:

You need to preserve exact input formatting (whitespace, key order)
You need to preserve duplicate keys
You have very specific performance requirements where JSON's lack of parsing overhead matters

Examples¶

Invalid¶

CREATE TABLE users (
    data json
);

code-block.sql:1:1 lint/preferJsonb ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Prefer JSONB over JSON for better performance and functionality.

  > 1 │ CREATE TABLE users (
      │ ^^^^^^^^^^^^^^^^^^^^
  > 2 │     data json
  > 3 │ );
      │ ^^
    4 │ 

  i JSON stores exact text representation while JSONB stores parsed binary format. JSONB is faster for queries, supports indexing, and removes duplicate keys.

  i Consider using JSONB instead unless you specifically need to preserve formatting or duplicate keys.

ALTER TABLE users ADD COLUMN metadata json;

code-block.sql:1:1 lint/preferJsonb ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Prefer JSONB over JSON for better performance and functionality.

  > 1 │ ALTER TABLE users ADD COLUMN metadata json;
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i JSON stores exact text representation while JSONB stores parsed binary format. JSONB is faster for queries, supports indexing, and removes duplicate keys.

  i Consider using JSONB instead unless you specifically need to preserve formatting or duplicate keys.

ALTER TABLE users ALTER COLUMN data TYPE json;

code-block.sql:1:1 lint/preferJsonb ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Prefer JSONB over JSON for better performance and functionality.

  > 1 │ ALTER TABLE users ALTER COLUMN data TYPE json;
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i JSON stores exact text representation while JSONB stores parsed binary format. JSONB is faster for queries, supports indexing, and removes duplicate keys.

  i Consider using JSONB instead unless you specifically need to preserve formatting or duplicate keys.

Valid¶

CREATE TABLE users (
    data jsonb
);

ALTER TABLE users ADD COLUMN metadata jsonb;

ALTER TABLE users ALTER COLUMN data TYPE jsonb;

How to configure¶

{
  "linter": {
    "rules": {
      "preferJsonb": "error"
    }
  }
}

How to suppress¶

Suppress this diagnostic with a comment:

-- pgls-ignore preferJsonb