JSONType

JSONType enum for JSONB column ordering

Audience: Programmer

The JSONType enum specifies the type casting for extracted JSONB values when using JSONB column ordering in pagination queries. This ensures that values are properly compared and sorted according to their intended data type.

Definition

enum JSONType {
  NUMBER
  STRING
  BOOLEAN
}

Values

NUMBER

Cast the extracted JSONB value to numeric for number comparisons.

Use when: Sorting by numeric fields like quantities, weights, prices, or any numerical values stored in JSON.

Example:

query {
  inventoryItems(
    orderBy: {
      direction: DESC,
      jsonPath: "attributes.weight",
      jsonType: NUMBER
    }
  ) {
    edges {
      node {
        id
        data
      }
    }
  }
}

STRING

Cast the extracted JSONB value to text for string comparisons.

Use when: Sorting by text fields like names, descriptions, categories, or any string values stored in JSON.

Example:

query {
  customers(
    orderBy: {
      direction: ASC,
      jsonPath: "company.name",
      jsonType: STRING
    }
  ) {
    edges {
      node {
        id
        data
      }
    }
  }
}

BOOLEAN

Cast the extracted JSONB value to boolean for boolean comparisons.

Use when: Sorting by boolean flags like active/inactive status, feature flags, or any true/false values stored in JSON.

Example:

query {
  inventoryItems(
    orderBy: {
      direction: DESC,
      jsonPath: "flags.isPriority",
      jsonType: BOOLEAN
    }
  ) {
    edges {
      node {
        id
        data
      }
    }
  }
}

Usage Context

The JSONType enum is used exclusively with the jsonType field in OrderByInput types when performing JSONB column ordering. It works in conjunction with:

• jsonPath: Specifies the dot-notation path to the nested JSON field

• jsonType: Specifies how to cast the value (this enum)

• direction: Specifies sort direction (ASC or DESC)

Type Casting Behavior

When you specify a JSONType, the database extracts the value at the specified jsonPath and casts it to the appropriate type for comparison:

JSONType	Database Cast	Example Values
NUMBER	numeric	1.5, 42, -10
STRING	text	"apple", "zebra", "Product A"
BOOLEAN	boolean	true, false

Important Notes

1. Type Mismatch: If the actual JSON value doesn't match the specified JSONType, the sorting behavior may be unexpected

2. Null Values: Missing or null values in the JSON path will be sorted as null regardless of JSONType

3. Required with jsonPath: The jsonType parameter is required when using jsonPath for JSONB ordering

Complete Example

query GetProductsByPriority {
  inventoryItems(
    first: 50,
    orderBy: {
      direction: DESC,
      jsonPath: "metadata.priority",
      jsonType: NUMBER
    },
    where: {
      dataHasKey: "metadata.priority"
    }
  ) {
    edges {
      node {
        id
        sku
        data
      }
      cursor
    }
    pageInfo {
      hasNextPage
      endCursor
    }
    totalCount
  }
}

See Also

• OrderByInput Types for complete ordering documentation

• WithCustomData Interface for entities with the data field

• Map Scalar for JSON data storage

• Advanced JSON Field Queries for filtering by JSON fields