JSON functions and operators

The following table describes the JavaScript Object Notation (JSON) functions supported by Devart ODBC Driver for SQLite.

Scalar functions and operators

Function	Description
json(X)	Verifies X as a valid JSON string or JSONB BLOB, returning a minified version of the JSON with unnecessary whitespace removed. JSON5 text is converted to canonical RFC-8259 text before returning.
jsonb(X)	Returns the JSONB representation of JSON X. If X is a BLOB resembling JSONB, this function returns a copy of X. The structure of the JSONB isn’t validated, only the outermost element is examined.
json_array(X1,X2,...,XN)	Returns a well-formed JSON array created from the provided arguments. Text arguments are converted to quoted JSON strings. If an argument is a result of another JSON function, it’s inserted as JSON.
jsonb_array(X1,X2,...,XN)	Works similarly to json_array(), but returns the result in JSONB format.
json_array_length(X[,P])	Returns the number of elements in the JSON array X; 0 if X isn’t an array. If path P is provided, this function returns the number of elements in the JSON array at that path in X; 0 if the element at path P isn’t an array; NULL if no element at path P exists.
json_error_position(X)	Returns the position of the first syntax error in X if X is malformed; 0 if X is a valid JSON or JSON5 string.
json_extract(X,P1,P2,...)	Extracts and returns one or more values from a well-formed JSON at X using specified paths P1, P2, and so on. If a single path is provided, this function returns the corresponding SQL value. Multiple paths return a JSON array holding the values.
jsonb_extract(X,P1,P2,...)	Works similarly to json_extract(), but returns the result in JSONB format when necessary.
json_insert(X,P1,V1,P2,V2,...)	Inserts value V into X if path P doesn’t exist.
jsonb_insert(X,P1,V1,P2,V2,...)	Works similarly to json_insert(), but returns the result in JSONB format.
json_object(L1,V1,L1,V1,...,LN,VN)	Creates a well-formed JSON object from pairs of arguments (labels L and values V). A BLOB argument results in an error.
jsonb_object(L1,V1,L1,V1,...,LN,VN)	Works similarly to json_object(), but returns the result in JSONB format.
json_patch(T,P)	Returns a copy of T with patch P applied following an RFC-7396 MergePatch algorithm.
jsonb_patch(T,P)	Works similarly to json_patch(), but returns the result in JSONB format.
json_pretty(X[,indent])	Works similarly to json(), but adds extra whitespace for readability. If the indent text string is provided, it’s used for indentation; if omitted or NULL, the indentation is four spaces per level.
json_remove(X,P,...)	Removes elements from X at given paths P.
jsonb_remove(X,P,...)	Works similarly to json_remove(), but returns the result in JSONB format.
json_replace(X,P1,V1,P2,V2,...)	Replaces the value in X with V if path P exists.
jsonb_replace(X,P1,V1,P2,V2,...)	Works similarly to json_replace(), but returns the result in JSONB format.
json_set(X,P1,V1,P2,V2,...)	Replaces a value in X with V or inserts value V into X depending on whether path P exists.
jsonb_set(X,P1,V1,P2,V2,...)	Works similarly to json_set(), but returns the result in JSONB format.
json_type(X[,P])	Returns the type of the outermost element of X. If path P is provided, this function returns the element selected by that path; NULL if no such element exists in X.
json_valid(X[,Y])	Returns 1 if X is well-formed JSON; otherwise 0. The optional Y bitmask defines what “well-formed” means, supporting various values for different JSON formats. If omitted, Y defaults to 1, which checks if X is strictly RFC-8259 JSON text.
json_quote(X)	Converts the SQL value X (a number or string) to its corresponding JSON representation. If X is already a JSON value, no changes are made.
-> and ->>	Are used for extracting subcomponents of JSON. The -> operator returns a text JSON representation, or NULL if the subcomponent doesn’t exist. The ->> operator returns a SQL representation (TEXT, INTEGER, REAL, or NULL), or NULL if the subcomponent doesn’t exist.

Aggregate functions

Function	Description
json_group_array(X)	Aggregates X values into a single JSON array.
jsonb_group_array(X)	Works similarly to json_group_array(), but returns the result in JSONB format.
json_group_object(name,value)	Aggregates name-value pairs into a single JSON object.
jsonb_group_object(name,value)	Works similarly to json_group_object(), but returns the result in JSONB format.

Table-valued functions

Function	Description
json_each(X[,P])	Walks the immediate children of the top-level JSON element, or just the top-level element itself if it’s a primitive value, and returns a row for each element. If path P is provided, the element identified by that path is treated as the top-level element.
jsonb_each(X[,P])	Works similarly to json_each(), but returns the result in JSONB format.
json_tree(X[,P])	Recursively walks the JSON structure starting from the top-level element and returns a row for each element. If path P is provided, the element identified by that path is treated as the top-level element.
jsonb_tree(X[,P])	Works similarly to json_tree(), but returns the result in JSONB format.

For more information, see the SQLite documentation: JSON Functions And Operators.