JSON (Javascript Object Notation)

SQLite provides extensive functionality for working with JSON. APSW complements that.

What is JSON?

Javascript Object Notation (RFC 8259) is a TEXT based format for representing data, encoded in UTF-8. It is deliberately constrained in what can be represented, and as a result is very widely supported across languages and platforms.

Types

Javascript

Python

Example

null

None

 
null

boolean

bool

 
true

string - surrounding double quotes with some characters backslash escaped such as double quote and forward slash, while Unicode codepoints can be backslash-u escaped allowing for the JSON text to be all ASCII. Codepoint U+2603 is snowman ☃

str

 
"hello \" world \/ \u2603"

number

int and float

 
72
-3.14E7

array - array contents can be any JSON types

list

 
[null, 3.2, "hello"]

object - keys must be strings, but values can be any JSON types

dict

 
{
  "description": "Orange",
  "price": 3.07,
  "varieties": ["fall",
                  {"long": "...",
                   "short": 3443}
               ]
}

What is not in JSON

Versioning and extension mechanisms

There is no version number, nor is there a way to extend the format to add additional functionality within the standard.

Comments

There is deliberately no syntax for comments.

Whitespace sensitivity

Although the examples above use indents and spacing for readability, JSON ignores all whitespace. Machine generated JSON usually omits whitespace, while JSON intended for human consumption has whitespace added.

Dates and times

There is no native date or time representation. The most common approach is to use ISO8601 formatted strings which look like 2025-09-30T09:45:00Z for UTC and 2025-09-30T01:15:00-07:00 with a timezone.

Binary data

Binary data can’t be included in a text format. The most common approach is to use base 64 strings which look like bGlnaHQgd29yaw==

Infinity and NaN

There is no explicit representation for infinity or for Not A Number which arise from floating point calculations. For example 1e500 (1 with 500 zeroes) multiplied by itself is too large to be represented in the most common 64 bit floating point, while subtracting infinity from itself produces NaN.

Because infinity and NaN can occur in calculations, many JSON libraries will produce and accept them despite not being in the JSON standard. See below for how SQLite deals with infinity and NaN.

String normalization

Unicode allows different representations of what will appear as the same text. For example an e with an accent can be represented as a single codepoint, or as two with the e and the combining accent separately. JSON makes no requirements on strings, with all implementations usually producing and accepting the strings as is. You can read about the subject here.

Trailing commas

Trailing commas will not be accepted or produced by JSON libraries. For example a list like [1, 2, 3,] or an object like {"one": 1, "two": 2,} are not valid.

Explicit limits

The standard does not say how long strings can be, or how many items can be in an array or object. There is no limit on how many digits can be used in numbers nor a minimum or maximum precision. It is common for implementations to have limits especially 64 bits for numbers. String limits may be 1 or 2 billion characters, and arrays / objects be limited to a similar number of members.

Python has a 64 bit limit on floating point numbers when using float but decimal is unlimited, and has no limit on integers, strings, arrays, or objects other than available memory.

SQLite has an upper limit of 2GB for strings, uses signed 64 bit integers, and standard 64 bit floating point.

If data is large, then other representations are more appropriate.

Object (dict) key order or duplicates

While arrays (list) are ordered, there is no specification for what order object keys are in, or that duplicates are not allowed. This usually doesn’t matter, but there are security attacks where one component may use the first occurrence of a duplicate key, while another component uses the last occurrence. For example the SQLite function extracting values will use the first occurrence of a key, while a dict created from the object will use the last occurrence.

The Python json module, and APSW both let you see objects as lists of keys and values so you can do your own validation or other processing.

JSON5

JSON5 is a superset of JSON intended to be more human readable and writable. SQLite will accept JSON5 encoded text, but will never produce it. While SQLite parses JSON5, you can’t get back JSON5 output from a JSON5 input.

For example JSON5 allows comments, hexadecimal numbers, trailing commas, infinity and NaN, and omitting some quoting,

Using JSON

Python

The standard library json module provides all necessary functionality, including turning Python objects into JSON text, and JSON text into Python objects. You can read and write JSON text, or a file object.

It deviates from the standard:

  • Infinity and NaN are produced and consumed by default, although there is a keyword argument to turn it off

  • When producing JSON objects, keys that are numbers, None, or boolean are turned into their corresponding JSON text representation. When reading an object back, the reverse transformation is not done since there is no way to know if that is intended,

  • Various corner cases in Unicode / UTF8 are accepted such as unpaired surrogates and UTF8 encoded surrogates. This was done because other implementations at the time could produce this kind of encoding. Attempting to encode the resulting strings as UTF-8 again will result in exceptions.

You can see a full list of JSON issues.

SQLite

SQLite has over 30 functions for consuming, extracting, iterating, and producing JSON. You will need to ensure that what you get back is what is intended. You can usually get back the JSON text representation of values, or the SQLite value. For example a SQLite string is the same as a Python string, while the JSON text representation includes double quotes around it and various quoting inside. (Example)

You can store JSON text directly in the database, but there is no way to differentiate it from any other text value. For example the number 2 in JSON is text 2. The json_valid function may help - for example as a CHECK constraint on a column.

Infinity and NaN

SQLite accepts infinity but represents it as the floating point value 9e999 and accepts NaN representing it as null (None). Unfortunately 9e999 is a valid value for decimal.Decimal as well as numpy.float128, so you won’t be able to tell if infinity was the original value

JSONB

SQLite has a binary format for representing JSON - named JSONB. It is specified here. It is significantly faster to use because JSON text requires finding matching quotes around strings, square brackets around arrays, curly braces around objects, and ensuring numeric values are valid. JSONB has already done all that processing so accessing and extracting members is a lot quicker. It also saves some space.

In most cases using SQLite JSON text functions results in SQLite doing an internal conversion to JSONB (which is cached) and then operating on that. JSONB internally stores values as a binary tag and length, then the UTF8 text so producing JSON text again is quick.

You can store JSONB to the database, and again can use the json_valid function as CHECK constraint with the value 8.

Note

JSONB does not have a version number or any header explicitly identifying binary data as JSONB. There is no checksum or similar validation. As an example a single byte whose value is 0 through 12 is valid JSONB.

As byte sequences get longer, the likelihood they are valid JSONB decreases. This table lists what proportion of all byte sequences of each length are also valid JSONB.

Valid JSONB sequences

Sequence length

Proportion valid JSONB

1 byte

3.52%

2 bytes

0.71%

3 bytes

0.35%

4 bytes

0.18%

5 bytes

0.10%

6 bytes

0.05%

7 bytes

0.03%

8 bytes

0.02%

9 bytes

0.01%

10+ bytes

very low

APSW

APSW provides 2 functions for working directly with JSONB, and a validation function. This is for performance reasons so that there is no need for an intermediate step representing objects as JSON text. The validation function is stricter than SQLite’s equivalent to avoid false positives.

Performance testing was done using SQLite’s randomjson code to create a large object with many nested values- your objects will be different.

jsonb_encode()

Converts a Python object directly to JSONB. The alternative is two steps using json to convert to JSON text and then SQLite’s internal JSON text to JSONB.

Test results (CPU time)

0.13 seconds

APSW Python object direct to JSONB

1.20 seconds

json same Python object to JSON text

0.80 seconds

SQLite that JSON text to JSONB

The same parameters as json.dumps() are available, with more providing control over how non-string object keys are converted, type matching, and direct conversion to JSONB types can be done.

jsonb_decode()

Converts JSONB directly back to a Python object. The alternative is two steps using SQLite’s internal JSONB to JSON text and then json to convert the JSON text to a Python object.

Test results (CPU time)

0.48 seconds

APSW JSONB direct to Python object

0.22 seconds

SQLite same JSONB to JSON text

1.35 seconds

json that JSON text to Python object

The same parameters as json.loads() are available, with an additional hook for arrays (lists).

jsonb_detect()

Returns a boolean if some binary data is valid JSONB. If this returns True then SQLite will always produce valid JSON from the JSONB.

SQLite’s json_valid only checks the various internal type and length fields are consistent and items seem reasonable. It does not check all corner cases, or the UTF8 encoding, and so can produce invalid JSON even if json_valid said it was valid JSONB.

Notes

Because SQLite has a 2GB limit on text or blobs (binary data), it can’t work with individual JSON text or JSONB data over that size.

JSON as a SQLite value type

You can make SQLite automatically support JSON as though it was a natively supported type. Example code that does these steps.

Store JSONB

SQLite’s binary JSON representation is stored as a binary blob in the database. This is necessary because JSON text can’t easily be distinguished from other text, while a blob is far less ambiguous.

SQLite often operates on JSONB internally when using the JSON functions, and its json function can turn JSONB into JSON text format if needed.

SQLite’s functions provide full access to all the values inside JSON and JSONB for reading, iterating, qnd modifying.

Convert bindings

The convert_binding callback for SQLite unknown types can encode them as JSONB.

Convert JSONB

The convert_jsonb callback is called when a blob would be returned and is also valid JSONB. You can decode it or return the blob. The cursor is provided so you can examine the description to help decide.

JSONB API

apsw.jsonb_decode(data: Buffer, *, object_pairs_hook: Callable[[list[tuple[str, JSONBTypes | Any]]], Any] | None = None, object_hook: Callable[[dict[str, JSONBTypes | Any]], Any] | None = None, array_hook: Callable[[list[JSONBTypes | Any]], Any] | None = None, parse_int: Callable[[str], Any] | None = None, parse_float: Callable[[str], Any] | None = None) → Any

Decodes JSONB binary data into a Python object. It is like json.loads() but operating on JSONB binary source instead of a JSON text source.

Parameters:

  • data – Binary data to decode

  • object_pairs_hook – Called after a JSON object has been decoded with a list of tuples, each consisting of a str and corresponding value, and should return a replacement value to use instead.

  • object_hook – Called after a JSON object has been decoded into a Python dict and should return a replacement value to use instead.

  • array_hook – Called after a JSON array has been decoded into a list, and should return a replacement value to use instead.

  • parse_int – Called with a str of the integer, and should return a value to use. The default is int. If the integer is hexadecimal then it will be called with a second parameter of 16.

  • parse_float – Called with a str of the float, and should return a value to use. The default is float.

Only one of object_hook or object_pairs_hook can be provided. object_pairs_hook is useful when you want something other than a dict, care about the order of keys, want to convert them first (eg case, numbers, normalization), want to handle duplicate keys etc.

The array, int, and float hooks let you use alternate implementations. For example if you are using numpy then you could use numpy arrays instead of lists, or numpy’s float128 to get higher precision floating numbers with greater exponent range than the builtin float type.

If you use types.MappingProxyType as object_hook and tuple as array_hook then the overall returned value will be immutable (read only).

Note

The data is always validated during decode. There is no need to separately call jsonb_detect().

apsw.jsonb_detect(data: Buffer) → bool

Returns True if data is valid JSONB, otherwise False. If this returns True then SQLite will produce valid JSON from it.

SQLite’s json_valid only checks the various internal type and length fields are consistent and items seem reasonable. It does not check all corner cases, or the UTF8 encoding, and so can produce invalid JSON even if json_valid said it was valid JSONB.

Note

jsonb_decode() always validates the data as it decodes, so there is no need to call this function separately. This function is useful for determining if some data is valid, and not some other binary format such as an image.

apsw.jsonb_encode(obj: Any, *, skipkeys: bool = False, sort_keys: bool = False, check_circular: bool = True, exact_types: bool = False, default: Callable[[Any], JSONBTypes | Buffer] | None = None, default_key: Callable[[Any], str] | None = None, allow_nan: bool = True) → bytes

Encodes a Python object as JSONB. It is like json.dumps() except it produces JSONB bytes instead of JSON text.

Parameters:

  • obj – Object to encode

  • skipkeys – If True and a non-string dict key is encountered then it is skipped. Otherwise ValueError is raised. Default False. Like json.dumps() keys that are bool, int, float, and None are always converted to string.

  • sort_keys – If True then objects (dict) will be output with the keys sorted. This produces deterministic output. Default False.

  • check_circular – Detects if containers contain themselves (even indirectly) and raises ValueError. If False and there is a circular reference, you eventually get RecursionError (or run out of memory or similar).

  • default –

    Called if an object can’t be encoded, and should return an object that can be encoded. If not provided a TypeError is raised.

    It can also return binary data in JSONB format. For example decimal values can be encoded as a full precision JSONB float. apsw.ext.make_jsonb() can be used.

  • default_key – Objects (dict) must have string keys. If a non-string key is encountered, it is skipped if skipkeys is True. Otherwise this is called. If not supplied the default matches the standard library json which converts None, bool, int and float to their string JSON equivalents and uses those. This callback is useful if you want to raise an exception, or use a different way of generating the key string.

  • allow_nan – If True (default) then following SQLite practise, infinity is converted to float 9e999 and NaN is converted to None. If False a ValueError is raised.

  • exact_types – By default subclasses of int, float, list (including tuple), dict (including collections.abc.Mapping), and str are converted the same as the parent class. This is usually what you want. However sometimes you are using a subclass and want them converted by the default function with an example being enum.IntEnum. If this parameter is True then only the exact types are directly converted and subclasses will be passed to default or default_key.

You will get a TooBigError if the resulting JSONB will exceed 2GB because SQLite can’t handle it.