JSON Functions & Operators in SQLite (Full List)

Below is a full list of JSON functions and JSON operators available in SQLite.

Scalar Functions

FunctionDescription
json()Returns a minified version of its (JSON string) argument as actual JSON. Basically, it converts raw text that looks like JSON into actual JSON.
json_array()Returns a well-formed JSON array based on its arguments.
json_array_length()Returns the number of elements in a given JSON array.
json_extract()Extracts and returns one or more values from well-formed JSON.
json_insert()Inserts a new value into a JSON document.
json_object()Returns a well-formed JSON object based on its arguments.
json_patch()Adds, modifies, and/or deletes elements of a JSON Object (it runs the RFC-7396 MergePatch algorithm to apply a given patch against the given JSON input).
json_remove()Removes one or more elements from a JSON object or array.
json_replace()Replaces an existing value in a JSON document with another value.
json_set()Inserts into, or replaces, a value in a JSON document.
json_type()Returns the type of the outermost element of the given JSON.
json_valid()Checks whether or not its argument is well-formed JSON.
json_quote()Converts a number or string into its corresponding JSON representation.

Aggregate Functions

FunctionDescription
json_group_array()Returns a JSON array comprised of all values in the aggregation.
json_group_object()Returns a JSON object comprised of all name/value pairs in the aggregation.

Table-Valued Functions

FunctionDescription
json_each()Walks the JSON value provided as its first argument and returns a table consisting of one row for each array element or object member. It only walks the immediate children of the top-level array or object, or just the top-level element itself if the top-level element is a primitive value.
json_tree()Walks the JSON value provided as its first argument and returns a table consisting of one row for each array element or object member. It recursively walks through the JSON substructure starting with the top-level element.

JSON Operators

SQLite also includes the following operators for working with JSON:

OperatorDescription
->Extracts a subcomponent from a JSON document and returns a JSON representation of that subcomponent.
->>Extracts a subcomponent from a JSON document and returns an SQL representation of that subcomponent.

JSON Support in SQLite

Starting from SQLite 3.38.0 (released on 22 February 2022), JSON functions and operators are built into SQLite. Prior to SQLite 3.38.0, you would need to use the -DSQLITE_ENABLE_JSON1 compile-time option to enable JSON support.