Available on crate feature
__sqlite-shared only.Expand description
SQLite specific functions
Functions§
- json
- Verifies that its argument is a valid JSON string or JSONB blob and returns a minified version of that JSON string with all unnecessary whitespace removed.
- json_
array_ 0 - The
json_array()SQL function accepts zero or more arguments and returns a well-formed JSON array that is composed from those arguments. Note that arguments of type BLOB will not be accepted by this function. - json_
array_ 1 - The
json_array()SQL function accepts zero or more arguments and returns a well-formed JSON array that is composed from those arguments. Note that arguments of type BLOB will not be accepted by this function. - json_
array_ 2 - The
json_array()SQL function accepts zero or more arguments and returns a well-formed JSON array that is composed from those arguments. Note that arguments of type BLOB will not be accepted by this function. - json_
array_ length - The json_array_length(X) function returns the number of elements in the JSON array X, or 0 if X is some kind of JSON value other than an array. Errors are thrown if either X is not well-formed JSON or if P is not a well-formed path.
- json_
array_ length_ with_ path - The json_array_length(X) function returns the number of elements in the JSON array X, or 0 if X is some kind of JSON value other than an array. The json_array_length(X,P) locates the array at path P within X and returns the length of that array, or 0 if path P locates an element in X that is not a JSON array, and NULL if path P does not locate any element of X. Errors are thrown if either X is not well-formed JSON or if P is not a well-formed path.
- json_
error_ position - The json_error_position(X) function returns 0 if the input X is a well-formed JSON or JSON5 string. If the input X contains one or more syntax errors, then this function returns the character position of the first syntax error. The left-most character is position 1.
- json_
extract_ double - Extracts a floating-point value from a well-formed JSON document at the given path.
- json_
extract_ integer - Extracts an integer value from a well-formed JSON document at the given path.
- json_
extract_ json_ 1 - Extracts a JSON object or array from a well-formed JSON document at the given path.
- json_
extract_ json_ 2 - Extracts a JSON object or array from a well-formed JSON document at the given path.
- json_
extract_ string - Extracts a text value from a well-formed JSON document at the given path.
- json_
group_ array - The
json_group_array(X)function is an aggregate SQL function that returns a JSON array comprised of all X values in the aggregation. - json_
group_ object - The json_group_object(NAME,VALUE) function returns a JSON object comprised of all NAME/VALUE pairs in the aggregation.
- json_
insert_ 0 - The
json_insert(X,P,V,...)SQL function takes a single JSON value as its first argument followed by zero or more pairs of path and value arguments. It returns a copy of the X argument with the values V inserted at the paths P, but only where nothing already exists at - json_
insert_ 1 - The
json_insert(X,P,V,...)SQL function takes a single JSON value as its first argument followed by zero or more pairs of path and value arguments. It returns a copy of the X argument with the values V inserted at the paths P, but only where nothing already exists at - json_
insert_ 2 - The
json_insert(X,P,V,...)SQL function takes a single JSON value as its first argument followed by zero or more pairs of path and value arguments. It returns a copy of the X argument with the values V inserted at the paths P, but only where nothing already exists at - json_
object_ 0 - The
json_object()SQL function accepts zero or more pairs of arguments and returns a well-formed JSON object composed from those pairs. The first argument of each pair is the label (key) and the second argument is the value. If any argument tojson_object()is a - json_
object_ 1 - The
json_object()SQL function accepts zero or more pairs of arguments and returns a well-formed JSON object composed from those pairs. The first argument of each pair is the label (key) and the second argument is the value. If any argument tojson_object()is a - json_
object_ 2 - The
json_object()SQL function accepts zero or more pairs of arguments and returns a well-formed JSON object composed from those pairs. The first argument of each pair is the label (key) and the second argument is the value. If any argument tojson_object()is a - json_
patch - Applies an RFC 7396 MergePatch
patchto the input JSONtargetand returns the patched JSON value. - json_
pretty - Converts the given json value to pretty-printed, indented text
- json_
pretty_ with_ indentation - Converts the given json value to pretty-printed, indented text
- json_
quote - The json_quote(X) function converts the SQL value X (a number or a string) into its corresponding JSON representation. If X is a JSON value returned by another JSON function, then this function is a no-op.
- json_
remove_ 0 - The
json_remove(X,P,...)SQL function takes a single JSON value as its first argument followed by zero or more path arguments. Thejson_remove(X,P,...)function returns a copy of the X parameter with all the elements identified by path arguments removed. Paths that select elements not found in X - json_
remove_ 1 - The
json_remove(X,P,...)SQL function takes a single JSON value as its first argument followed by zero or more path arguments. Thejson_remove(X,P,...)function returns a copy of the X parameter with all the elements identified by path arguments removed. Paths that select elements not found in X - json_
remove_ 2 - The
json_remove(X,P,...)SQL function takes a single JSON value as its first argument followed by zero or more path arguments. Thejson_remove(X,P,...)function returns a copy of the X parameter with all the elements identified by path arguments removed. Paths that select elements not found in X - json_
replace_ 0 - The
json_replace(X,P,V,...)SQL function takes a single JSON value as its first argument followed by zero or more pairs of path and value arguments. It returns a copy of the X argument with values V substituted at the paths P, but only where something already exists - json_
replace_ 1 - The
json_replace(X,P,V,...)SQL function takes a single JSON value as its first argument followed by zero or more pairs of path and value arguments. It returns a copy of the X argument with values V substituted at the paths P, but only where something already exists - json_
replace_ 2 - The
json_replace(X,P,V,...)SQL function takes a single JSON value as its first argument followed by zero or more pairs of path and value arguments. It returns a copy of the X argument with values V substituted at the paths P, but only where something already exists - json_
set_ 0 - The
json_set(X,P,V,...)SQL function takes a single JSON value as its first argument followed by zero or more pairs of path and value arguments. It returns a copy of the X argument with values V inserted or replaced at the paths P. Unlikejson_replace(), - json_
set_ 1 - The
json_set(X,P,V,...)SQL function takes a single JSON value as its first argument followed by zero or more pairs of path and value arguments. It returns a copy of the X argument with values V inserted or replaced at the paths P. Unlikejson_replace(), - json_
set_ 2 - The
json_set(X,P,V,...)SQL function takes a single JSON value as its first argument followed by zero or more pairs of path and value arguments. It returns a copy of the X argument with values V inserted or replaced at the paths P. Unlikejson_replace(), - json_
type - The json_type(X) function returns the “type” of the outermost element of X. The “type” returned by json_type() is one of the following SQL text values: ‘null’, ‘true’, ‘false’, ‘integer’, ‘real’, ‘text’, ‘array’, or ‘object’.
- json_
type_ with_ path - The json_type(X,P) function returns the “type” of the element in X that is selected by path P. If the path P in json_type(X,P) selects an element that does not exist in X, then this function returns NULL.
- json_
valid - Returns
trueif the argument is well-formed JSON, or returnsfalseif is not well-formed. - json_
valid_ with_ flags - The json_valid(X,Y) function returns 1 if the argument X is well-formed JSON, or returns 0 if X is not well-formed. The Y parameter is an integer bitmask that defines what is meant by “well-formed”.
- jsonb
- The jsonb(X) function returns the binary JSONB representation of the JSON provided as argument X.
- jsonb_
array_ 0 - The
jsonb_array()SQL function accepts zero or more arguments and returns a well-formed JSON array that is composed from those arguments. Note that arguments of type BLOB will not be accepted by this function. - jsonb_
array_ 1 - The
jsonb_array()SQL function accepts zero or more arguments and returns a well-formed JSON array that is composed from those arguments. Note that arguments of type BLOB will not be accepted by this function. - jsonb_
array_ 2 - The
jsonb_array()SQL function accepts zero or more arguments and returns a well-formed JSON array that is composed from those arguments. Note that arguments of type BLOB will not be accepted by this function. - jsonb_
extract_ double - Extracts a floating-point value from a well-formed JSON or JSONB document at the given path.
- jsonb_
extract_ integer - Extracts an integer value from a well-formed JSON or JSONB document at the given path.
- jsonb_
extract_ jsonb_ 1 - Extracts a JSON object or array from a well-formed JSON or JSONB document at the given path, returning it in JSONB format.
- jsonb_
extract_ jsonb_ 2 - Extracts a JSON object or array from a well-formed JSON or JSONB document at the given path, returning it in JSONB format.
- jsonb_
extract_ string - Extracts a text value from a well-formed JSON or JSONB document at the given path.
- jsonb_
group_ array - The
jsonb_group_array(X)function is an aggregate SQL function that returns a JSONB array comprised of all X values in the aggregation. - jsonb_
group_ object - The jsonb_group_object(NAME,VALUE) function returns a JSONB object comprised of all NAME/VALUE pairs in the aggregation.
- jsonb_
insert_ 0 - The
jsonb_insert(X,P,V,...)SQL function works just like thejson_insert()function except that the result is returned in SQLite’s private binary JSONB format rather than in the standard RFC 8259 text format. - jsonb_
insert_ 1 - The
jsonb_insert(X,P,V,...)SQL function works just like thejson_insert()function except that the result is returned in SQLite’s private binary JSONB format rather than in the standard RFC 8259 text format. - jsonb_
insert_ 2 - The
jsonb_insert(X,P,V,...)SQL function works just like thejson_insert()function except that the result is returned in SQLite’s private binary JSONB format rather than in the standard RFC 8259 text format. - jsonb_
object_ 0 - The
jsonb_object()SQL function works just like thejson_object()function except that the generated object is returned in SQLite’s private binary JSONB format rather than in the standard RFC 8259 text format. - jsonb_
object_ 1 - The
jsonb_object()SQL function works just like thejson_object()function except that the generated object is returned in SQLite’s private binary JSONB format rather than in the standard RFC 8259 text format. - jsonb_
object_ 2 - The
jsonb_object()SQL function works just like thejson_object()function except that the generated object is returned in SQLite’s private binary JSONB format rather than in the standard RFC 8259 text format. - jsonb_
patch - Applies an RFC 7396 MergePatch
patchto the input JSONtargetand returns the patched JSON value in SQLite’s binary JSONB format. - jsonb_
remove_ 0 - The
jsonb_remove(X,P,...)SQL function takes a single JSON value as its first argument followed by zero or more path arguments. Thejsonb_remove(X,P,...)function returns a copy of the X parameter with all the elements identified by path arguments removed. Paths that select elements not found in X - jsonb_
remove_ 1 - The
jsonb_remove(X,P,...)SQL function takes a single JSON value as its first argument followed by zero or more path arguments. Thejsonb_remove(X,P,...)function returns a copy of the X parameter with all the elements identified by path arguments removed. Paths that select elements not found in X - jsonb_
remove_ 2 - The
jsonb_remove(X,P,...)SQL function takes a single JSON value as its first argument followed by zero or more path arguments. Thejsonb_remove(X,P,...)function returns a copy of the X parameter with all the elements identified by path arguments removed. Paths that select elements not found in X - jsonb_
replace_ 0 - The
jsonb_replace(X,P,V,...)SQL function works just like thejson_replace()function except that the result is returned in SQLite’s private binary JSONB format rather than in the standard RFC 8259 text format. - jsonb_
replace_ 1 - The
jsonb_replace(X,P,V,...)SQL function works just like thejson_replace()function except that the result is returned in SQLite’s private binary JSONB format rather than in the standard RFC 8259 text format. - jsonb_
replace_ 2 - The
jsonb_replace(X,P,V,...)SQL function works just like thejson_replace()function except that the result is returned in SQLite’s private binary JSONB format rather than in the standard RFC 8259 text format. - jsonb_
set_ 0 - The
jsonb_set(X,P,V,...)SQL function works just like thejson_set()function except that the result is returned in SQLite’s private binary JSONB format rather than in the standard RFC 8259 text format. - jsonb_
set_ 1 - The
jsonb_set(X,P,V,...)SQL function works just like thejson_set()function except that the result is returned in SQLite’s private binary JSONB format rather than in the standard RFC 8259 text format. - jsonb_
set_ 2 - The
jsonb_set(X,P,V,...)SQL function works just like thejson_set()function except that the result is returned in SQLite’s private binary JSONB format rather than in the standard RFC 8259 text format.
Type Aliases§
- json
- The return type of
json() - json_
array_ 0 - The return type of
json_array_0() - json_
array_ 1 - The return type of
json_array_1() - json_
array_ 2 - The return type of
json_array_2() - json_
array_ length - The return type of
json_array_length() - json_
array_ length_ with_ path - The return type of
json_array_length_with_path() - json_
error_ position - The return type of
json_error_position() - json_
extract_ double - The return type of
json_extract_double() - json_
extract_ integer - The return type of
json_extract_integer() - json_
extract_ json_ 1 - The return type of
json_extract_json_1() - json_
extract_ json_ 2 - The return type of
json_extract_json_2() - json_
extract_ string - The return type of
json_extract_string() - json_
group_ array - The return type of
json_group_array() - json_
group_ object - The return type of
json_group_object() - json_
insert_ 0 - The return type of
json_insert_0() - json_
insert_ 1 - The return type of
json_insert_1() - json_
insert_ 2 - The return type of
json_insert_2() - json_
object_ 0 - The return type of
json_object_0() - json_
object_ 1 - The return type of
json_object_1() - json_
object_ 2 - The return type of
json_object_2() - json_
patch - The return type of
json_patch() - json_
pretty - The return type of
json_pretty() - json_
pretty_ with_ indentation - The return type of
json_pretty_with_indentation() - json_
quote - The return type of
json_quote() - json_
remove_ 0 - The return type of
json_remove_0() - json_
remove_ 1 - The return type of
json_remove_1() - json_
remove_ 2 - The return type of
json_remove_2() - json_
replace_ 0 - The return type of
json_replace_0() - json_
replace_ 1 - The return type of
json_replace_1() - json_
replace_ 2 - The return type of
json_replace_2() - json_
set_ 0 - The return type of
json_set_0() - json_
set_ 1 - The return type of
json_set_1() - json_
set_ 2 - The return type of
json_set_2() - json_
type - The return type of
json_type() - json_
type_ with_ path - The return type of
json_type_with_path() - json_
valid - The return type of
json_valid() - json_
valid_ with_ flags - The return type of
json_valid_with_flags() - jsonb
- The return type of
jsonb() - jsonb_
array_ 0 - The return type of
jsonb_array_0() - jsonb_
array_ 1 - The return type of
jsonb_array_1() - jsonb_
array_ 2 - The return type of
jsonb_array_2() - jsonb_
extract_ double - The return type of
jsonb_extract_double() - jsonb_
extract_ integer - The return type of
jsonb_extract_integer() - jsonb_
extract_ jsonb_ 1 - The return type of
jsonb_extract_jsonb_1() - jsonb_
extract_ jsonb_ 2 - The return type of
jsonb_extract_jsonb_2() - jsonb_
extract_ string - The return type of
jsonb_extract_string() - jsonb_
group_ array - The return type of
jsonb_group_array() - jsonb_
group_ object - The return type of
jsonb_group_object() - jsonb_
insert_ 0 - The return type of
jsonb_insert_0() - jsonb_
insert_ 1 - The return type of
jsonb_insert_1() - jsonb_
insert_ 2 - The return type of
jsonb_insert_2() - jsonb_
object_ 0 - The return type of
jsonb_object_0() - jsonb_
object_ 1 - The return type of
jsonb_object_1() - jsonb_
object_ 2 - The return type of
jsonb_object_2() - jsonb_
patch - The return type of
jsonb_patch() - jsonb_
remove_ 0 - The return type of
jsonb_remove_0() - jsonb_
remove_ 1 - The return type of
jsonb_remove_1() - jsonb_
remove_ 2 - The return type of
jsonb_remove_2() - jsonb_
replace_ 0 - The return type of
jsonb_replace_0() - jsonb_
replace_ 1 - The return type of
jsonb_replace_1() - jsonb_
replace_ 2 - The return type of
jsonb_replace_2() - jsonb_
set_ 0 - The return type of
jsonb_set_0() - jsonb_
set_ 1 - The return type of
jsonb_set_1() - jsonb_
set_ 2 - The return type of
jsonb_set_2()