Menu

SQLite JSON Functions

SQLite has no separate JSON storage class: JSON is commonly stored as TEXT. Since SQLite 3.45.0, it can also be stored as SQLite’s JSONB representation in a BLOB. SQLite JSONB is an internal SQLite format and is not binary-compatible with PostgreSQL JSONB.

Use the references below to create and aggregate JSON, extract or update values by path, validate JSON, and expand arrays and objects into rows. See the SQLite JSON documentation for supported formats, version requirements, and full function behavior.

For practical guidance on choosing JSON text or JSONB, see SQLite JSONB: When and How to Use It.

  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

    SQLite json_each() returns rows for the immediate children of a JSON object or array; primitive input produces one row.

  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

    Use SQLite json_tree() to recursively query JSON values and inspect each row’s key, value, type, atom, fullkey, and parent.

  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.

  18. jsonb

    Convert JSON text to SQLite’s binary JSONB BLOB with jsonb(), or pass through an input that appears to be JSONB.

  19. jsonb_each

    Use SQLite jsonb_each() to expand one JSON object or array level, returning nested objects and arrays as JSONB values.

  20. jsonb_tree

    Use SQLite jsonb_tree() to recursively expand JSON, returning nested objects and arrays as JSONB values.