Home » SQLite JSON Functions

SQLite JSON Functions

Savigo
You organize your data. Now organize your savings.
Set savings goals, track your progress, and stay on course.
Get Savigo for iPhone 

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.

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.

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.

Was this tutorial helpful?