SQLite JSON Functions

SQLite supports JSON data type, and this page organizes JSON-related functions and operators in SQLite.

  1. json

    The SQLite json() function validates the string specified by the parameter and converts it to a minimal JSON string, with all unnecessary whitespace removed.

  2. json_array

    The SQLite json_array() function evaluates all the values ​​in the parameters list and returns a JSON array containing all the parameters.

  3. json_array_length

    The SQLite json_array_length() function returns the length of elements in the specified JSON array, that is the number of the top level child elements in the JSON array.

  4. json_each

    The SQLite json_each() function walks the specified JSON document, generates a row for each top child element, and returns a result set consisting of all the rows.

  5. json_extract

    The SQLite json_extract() function extracts the value specified by the path expression from the JSON document and returns it.

  6. json_group_array

    The SQLite json_group_array() function is an aggregate function that returns a JSON array containing all the values ​​in a group.

  7. json_group_object

    The SQLite json_group_object() function is an aggregate function that returns a JSON object containing the key-value pairs of the specified columns in a group.

  8. json_insert

    The SQLite json_insert() function inserts values into a JSON document and return a new JSON document.

  9. json_object

    The SQLite json_object() function returns a JSON object containing all the key-value pairs specified by the parameters.

  10. json_patch

    The SQLite json_patch() function merges and patchs the second JSON object to the original JSON object, and returns the patched original JSON object.

  11. json_quote

    The SQLite json_quote() function converts the SQL value specified by the parameter to the corresponding JSON representation.

  12. json_remove

    The SQLite json_remove() function removes the data specified by a path from a JSON document and returns the modified JSON document.

  13. json_replace

    The SQLite json_replace() function replaces existing data in a JSON document and return modified JSON document.

  14. json_set

    The SQLite json_set() function inserts or updates data in a JSON document and return the modified JSON document.

  15. json_tree

    The SQLite json_tree() function recursively walks the specified JSON document, generates a row for each child element (including the parameter itself), and returns a result set consisting of all the rows.

  16. json_type

    The SQLite json_type() returns the type of a JSON value or the value of the specified path in JSON document.

  17. json_valid

    The SQLite json_valid() unction returns 0 or 1 to indicate whether the given parameter is a valid JSON document.