Sorting JSON Numbers as Strings in SQLite: Causes and Solutions

JSON Number Values Sorted Lexicographically Instead of Numerically

When working with JSON data in SQLite, developers may encounter unexpected behavior when attempting to sort numeric values extracted from JSON documents. A common manifestation of this issue occurs when using the -> operator to access JSON properties, resulting in values being sorted alphabetically rather than numerically. Consider this example output from an SQLite session:

SELECT json -> '$."6"', json_type(json -> '$."6"') FROM testjson ORDER BY (json -> '$."6"') ASC;
-1|integer
-1000|integer
0|integer
534|integer

The numerical values are ordered as (-1, -1000, 0, 534) instead of the expected numerical sequence (-1000, -1, 0, 534). This occurs because SQLite treats the JSON-extracted values as text for sorting purposes when using the -> operator. The actual numeric types are preserved in storage (as shown by json_type() returning "integer"), but the sorting logic defaults to string comparison rules rather than numerical comparison.

This behavior contrasts with explicit numeric sorting achieved through alternative approaches:

SELECT json -> '$."6"' FROM testjson ORDER BY (json -> '$."6"') + 0 ASC;
-1000
-1
0
534

The discrepancy stems from fundamental differences in how SQLite handles JSON operators versus native numeric types. While the values are stored numerically within the JSON structure itself, the extraction method determines whether subsequent operations treat them as text or numbers. This creates a paradigm where two identical-looking values might sort differently depending on the extraction operator used, leading to confusion for developers expecting implicit numerical sorting.

JSON Operator Semantics and Type Conversion Pitfalls

The core issue arises from SQLite’s implementation of JSON operators and its dynamic type system. Unlike databases with rigid type systems like PostgreSQL, SQLite makes no distinction between JSON documents and regular text at the storage level. This design choice leads to three primary factors influencing the sorting behavior:

1. Operator Return Types
   The -> operator always returns valid JSON text, preserving the exact textual representation from the original document. For numeric values, this means numbers are returned as JSON-formatted strings rather than native SQLite numeric types. When sorting these text representations, SQLite uses lexicographical ordering where "-1" comes before "-1000" because the second character ‘1’ vs ‘0’ determines order.

2. Implicit Type Conversion
   SQLite’s flexible type system allows automatic conversion between text and numeric types in expressions. However, this conversion only triggers when the value is used in a numeric context. The ORDER BY clause performs pure value comparisons without context-driven type conversion, leading to text-based sorting unless explicitly instructed otherwise.

3. JSON Storage Format
   All JSON documents in SQLite are stored as ordinary text strings. When using -> to extract values, SQLite returns the JSON fragment as text that would be valid if inserted into another JSON document. For numbers, this means maintaining the original string representation including leading zeros, exponent notation, and decimal points without any normalization.

The interaction between these factors creates a situation where numeric values extracted via -> retain their textual JSON representation, causing sorting operations to compare them as strings rather than numbers. This behavior persists even when json_type() correctly identifies the underlying JSON type as numeric, because type identification and value representation are handled separately in SQLite’s JSON implementation.

Achieving Numeric Sorting Through Explicit Type Conversion

To resolve sorting inconsistencies, developers must explicitly convert JSON-extracted values to native SQLite numeric types before sorting. There are several reliable methods to achieve proper numerical ordering:

Method 1: Use the ->> Operator
The ->> operator extracts JSON values as native SQLite types instead of JSON text:

SELECT json ->> '$."6"' AS value
FROM testjson
ORDER BY value ASC;

This approach automatically converts JSON numbers to appropriate SQLite integer or real values. The ->> operator parses the JSON content and returns:

• INTEGER for JSON numbers without decimal points or exponents
• REAL for JSON numbers with decimal points or exponents
• TEXT for JSON strings
• NULL for JSON null

Method 2: Explicit Casting
When dealing with mixed-type data or needing specific numeric types, use explicit casting:

SELECT json -> '$."6"' AS json_value
FROM testjson
ORDER BY CAST(json -> '$."6"' AS INTEGER) ASC;

This forces conversion to a specific numeric type regardless of the JSON value’s original representation. Consider using REAL instead of INTEGER if dealing with floating-point numbers:

ORDER BY CAST(json -> '$."6"' AS REAL) ASC;

Method 3: Arithmetic Promotion
Trigger implicit type conversion by using the value in a numeric expression:

SELECT json -> '$."6"' AS json_value
FROM testjson
ORDER BY (json -> '$."6"') + 0 ASC;

Adding zero forces SQLite to treat the text value as a number while preserving the original value. This technique works for both integers and real numbers but may lose precision with extremely large numbers due to SQLite’s numeric handling.

Cross-Database Considerations
Developers working with both SQLite and PostgreSQL should note key differences in JSON handling:

1. PostgreSQL requires explicit type conversion when using ->> (equivalent to SQLite’s -> behavior):

   SELECT data ->> 'ord' FROM table ORDER BY (data ->> 'ord')::numeric ASC;
   

2. SQLite automatically handles type conversion with ->> but requires caution with mixed-type values in the same column.

Advanced Pattern: JSON Value Validation
When dealing with potentially inconsistent JSON data, combine type checking with conversion:

SELECT json ->> '$."6"' AS value
FROM testjson
WHERE json_type(json -> '$."6"') IN ('integer', 'real')
ORDER BY value ASC;

This ensures only numeric values participate in the sorting operation, avoiding conversion errors from unexpected text values.

Performance Optimization
For large datasets, create generated columns with persisted numeric conversions:

ALTER TABLE testjson ADD COLUMN numeric_value INTEGER
GENERATED ALWAYS AS (json ->> '$."6"') STORED;

CREATE INDEX idx_numeric_value ON testjson(numeric_value);

This approach moves the conversion cost to write operations while enabling efficient sorted queries through standard indexes.

Error Prevention Techniques
Handle non-numeric values gracefully using SQLite’s error-handling functions:

SELECT 
  json ->> '$."6"' AS value,
  CASE WHEN json_type(json -> '$."6"') IN ('integer', 'real')
       THEN json ->> '$."6"'
       ELSE NULL
  END AS safe_value
FROM testjson
ORDER BY safe_value ASC;

This pattern ensures non-numeric values don’t disrupt sorting operations while maintaining query execution.

Best Practice Recommendations

1. Always use ->> instead of -> when working with numeric comparisons or sorting
2. Validate JSON types with json_type() before conversion in mixed-type environments
3. Create persistent computed columns for frequently accessed numeric values
4. Use explicit CAST when dealing with columns containing both text and numeric JSON values
5. Consider SQLite’s type affinity rules when designing JSON schemas for numerical data

By understanding SQLite’s JSON implementation characteristics and applying these conversion strategies, developers can ensure consistent numerical sorting behavior while maintaining cross-database compatibility where required.