This page covers the SQLite JSON functions and operators that allow you to manipulate JSON data effectively.
Section 1. Validating JSON Data
This section covers the functions related to validating JSON data and debugging JSON errors.
- json() – Validate JSON data and return the minified JSON data with unnecessary spaces removed.
- json_pretty() – Format a JSON value to make it more human-readable.
- json_valid() – Validate JSON data and return 1 if it is a well-formed JSON or zero if it is not.
- json_error_position() – Return the character position of the first syntax error if the input JSON is invalid.
Section 2. Creating JSON data
This section covers functions for creating JSON objects, and arrays, and converting a SQL value into its corresponding JSON values.
- json_array() – Create a JSON array from one or more values.
- json_object() – Create a JSON object from zero or more pairs of values.
- json_quote() – Convert an SQLite value (a number or a string) into its corresponding JSON representation.
Section 3. Extract JSON Data
This section introduces the functions and operators for extracting values from JSON data.
- json_extract() – Extract a value from JSON data based on a specified path.
- JSON operator -> and ->> – Extract a subcomponent from JSON based on a specified path as a JSON or SQL value.
Section 4. JSON CRUD
This section shows how to insert, update, remove, and merge JSON data.
- json_insert() – Insert one or more values into JSON data based on specified JSON paths.
- json_replace() – Update existing values in JSON data.
- json_set() – Modify existing value if it exists or create a new one if it does not.
- json_remove() – Remove a value from JSON data.
- json_patch() – Merge the original JSON object with a patched JSON object and return the patched version of the original JSON object.
Section 5. Aggregate functions
This section covers the function for aggregating values into a JSON array or object.
- json_group_array – Aggregate values into a JSON array.
- json_group_object – Aggregate name/value pairs into a JSON object.
Section 6. JSON table-valued functions
This section discusses the JSON table-valued functions that allow you to iterate and extract JSON data effectively.
- json_each() – Iterate the top-level elements of a JSON object or array and return one row for each element.
- json_tree() – Take a JSON value as input and return a table consisting of one row for each element (in an array) or member (in an object).
Section 7. JSON utility functions
This section covers the JSON utility functions such as checking the type of a JSON element or getting the array’s length of a JSON array.
- json_type() – Return the type of the top-level element of a JSON value.
- json_array_length() – Return the number of elements in a JSON array.
