JSON Functions And Operators Small. Fast. Reliable. Choose any three. Home Menu About Documentation Download License Support Purchase Search About Documentation Download Support Purchase Search Documentation Search Changelog JSON Functions And Operators Table Of Contents 1. Overview 2. Compiling in JSON Support 3. Interface Overview 3.1. JSON arguments 3.2. JSONB 3.2.1. The JSONB format 3.2.2. Handling of malformed JSONB 3.3. PATH arguments 3.4. VALUE arguments 3.5. Compatibility 3.6. JSON5 Extensions 3.7. Performance Considerations 3.8. The JSON BLOB Input Bug 3.9. Quirks 4. Function Details 4.1. The json() function 4.2. The jsonb() function 4.3. The json_array() function 4.4. The jsonb_array() function 4.5. The json_array_insert() and jsonb_array_insert() functions 4.6. The json_array_length() function 4.7. The json_error_position() function 4.8. The json_extract() function 4.9. The jsonb_extract() function 4.10. The -> and ->> operators 4.11. The json_insert(), json_replace, and json_set() functions 4.12. The jsonb_insert(), jsonb_replace, and jsonb_set() functions 4.13. The json_object() function 4.14. The jsonb_object() function 4.15. The json_patch() function 4.16. The jsonb_patch() function 4.17. The json_pretty() function 4.18. The json_remove() function 4.19. The jsonb_remove() function 4.20. The json_type() function 4.21. The json_valid() function 4.22. The json_quote() function 4.23. Array and object aggregate functions 4.24. Table valued functions for parsing JSON: json_each(), jsonb_each(), json_tree(), and jsonb_tree() 4.24.1. Examples using json_each() and json_tree() 1. Overview By default, SQLite supports thirty functions and two operators for dealing with JSON values. There are also four table-valued functions that can be used to decompose a JSON string. All of the functions listed below have the SQLITE_INNOCUOUS and SQLITE_DETERMINISTIC flags. There are twenty-eight scalar functions and operators: json(json) jsonb(json) json_array(value1,value2,...) jsonb_array(value1,value2,...) jsonb_array_insert(json,path,value,...) json_array_insert(json,path,value,...) json_array_length(json) json_array_length(json,path) json_error_position(json) json_extract(json,path,...) jsonb_extract(json,path,...) json -> path json ->> path json_insert(json,path,value,...) jsonb_insert(json,path,value,...) json_object(label1,value1,...) jsonb_object(label1,value1,...) json_patch(json1,json2) jsonb_patch(json1,json2) json_pretty(json) json_quote(value) json_remove(json,path,...) jsonb_remove(json,path,...) json_replace(json,path,value,...) jsonb_replace(json,path,value,...) json_set(json,path,value,...) jsonb_set(json,path,value,...) json_type(json) json_type(json,path) json_valid(json) json_valid(json,flags) There are four aggregate SQL functions: json_group_array(value) jsonb_group_array(value) json_group_object(label,value) jsonb_group_object(label,value) The four table-valued functions are: json_each(json) json_each(json,path) json_tree(json) json_tree(json,path) jsonb_each(json) jsonb_each(json,path) jsonb_tree(json) jsonb_tree(json,path) 2. Compiling in JSON Support The JSON functions and operators are built into SQLite by default, as of SQLite version 3.38.0 (2022-02-22). They can be omitted by adding the -DSQLITE_OMIT_JSON compile-time option. Prior to version 3.38.0, the JSON functions were an extension that would only be included in builds if the -DSQLITE_ENABLE_JSON1 compile-time option was included. In other words, the JSON functions went from being opt-in with SQLite version 3.37.2 and earlier to opt-out with SQLite version 3.38.0 and later. 3. Interface Overview SQLite stores JSON as ordinary text. Backwards compatibility constraints mean that SQLite is only able to store values that are NULL, integers, floating-point numbers, text, and BLOBs. It is not possible to add a new "JSON" type. 3.1. JSON arguments For functions that accept JSON as their first argument, that argument can be a JSON object, array, number, string, or null. SQLite numeric values and NULL values are interpreted as JSON numbers and nulls, respectively. SQLite text values can be understood as JSON objects, arrays, or strings. If an SQLite text value that is not a well-formed JSON object, array, or string is passed into a JSON function, that function will usually throw an error. (Exceptions to this rule are json_valid(), json_quote(), and json_error_position().) These routines understand all rfc-8259 JSON syntax and also JSON5 extensions. JSON text generated by these routines always strictly conforms to the canonical JSON definition and does not contain any JSON5 or other extensions. The ability to read and understand JSON5 was added in version 3.42.0 (2023-05-16). Prior versions of SQLite would only read canonical JSON. 3.2. JSONB Beginning with version 3.45.0 (2024-01-15), SQLite allows its internal "parse tree" representation of JSON to be stored on disk, as a BLOB, in a format that we call "JSONB". By storing SQLite's internal binary representation of JSON directly in the database, applications can bypass the overhead of parsing and rendering JSON when reading and updating JSON values. The internal JSONB format also uses slightly less disk space than text JSON. Any SQL function parameter that accepts text JSON as an input will also accept a BLOB in the JSONB format. The function will operate the same in either case, except that it will run faster when the input is JSONB, since it does not need to run the JSON parser. Most SQL functions that return JSON text have a corresponding function that returns the equivalent JSONB. The functions that return JSON in the text format begin with "json_" and functions that return the binary JSONB format begin with "jsonb_". 3.2.1. The JSONB format JSONB is a binary representation of JSON used by SQLite and is intended for internal use by SQLite only. Applications should not use JSONB outside of SQLite nor try to reverse-engineer the JSONB format. The "JSONB" name is inspired by PostgreSQL, but the on-disk format for SQLite's JSONB is not the same as PostgreSQL's. The two formats have the same name, but are not binary compatible. The PostgreSQL JSONB format claims to offer O(1) lookup of elements in objects and arrays. SQLite's JSONB format makes no such claim. SQLite's JSONB has O(N) time complexity for most operations in SQLite, just like text JSON. The advantage of JSONB in SQLite is that it is smaller and faster than text JSON - potentially several times faster. There is space in the on-disk JSONB format to add enhancements and future versions of SQLite might include options to provide O(1) lookup of elements in JSONB, but no such capability is currently available. 3.2.2. Handling of malformed JSONB The JSONB that is generated by SQLite will always be well-formed. If you follow recommended practice and treat JSONB as an opaque BLOB, then you will not have any problems. But JSONB is just a BLOB, so a mischievous programmer could devise BLOBs that are similar to JSONB but that are technically malformed. When misformatted JSONB is feed into JSON functions, any of the following might happen: The SQL statement might abort with a "malformed JSON" error. The correct answer might be returned, if the malformed parts of the JSONB blob do not impact the answer. A goofy or nonsensical answer might be returned. The way in which SQLite handles invalid JSONB might change from one version of SQLite to the next. The system follows the garbage-in/garbage-out rule: If you feed the JSON functions invalid JSONB, you get back an invalid answer. If you are in doubt about the validity of our JSONB, use the json_valid() function to verify it. We do make this one promise: Malformed JSONB will never cause a memory error or similar problem that might lead to a vulnerability. Invalid JSONB might lead to crazy answers, or it might cause queries to abort, but it won't cause a crash. 3.3. PATH arguments For functions that accept PATH arguments, that PATH must be well-formed or else the function will throw an error. A well-formed PATH is a text value that begins with exactly one '$' character followed by zero or more instances of ".objectlabel" or "[arrayindex]". The arrayindex is usually a non-negative integer N. In that case, the array element selected is the N-th element of the array, starting with zero on the left. The arrayindex can also be of the form "#-N" in which case the element selected is the N-th from the right. The last element of the array is "#-1". Think of the "#" characters as the "number of elements in the array". Then the expression "#-1" evaluates to the integer that corresponds to the last entry in the array. It is sometimes useful for the array index to be just the # character, for example when appending a value to an existing JSON array: json_set('[0,1,2]','$[#]','new') → '[0,1,2,"new"]' 3.4. VALUE arguments For functions that accept "value" arguments (also shown as "value1" and "value2"), those arguments are usually understood to be literal strings that are quoted and become JSON string values in the result. Even if the input value strings look like well-formed JSON, they are still interpreted as literal strings in the result. However, if a value argument comes directly from the result of another JSON function or from the -> operator (but not the ->> operator), then the argument is understood to be actual JSON and the complete JSON is inserted rather than a quoted string. For example, in the following call to json_object(), the value argument looks like a well-formed JSON array. However, because it is just ordinary SQL text, it is interpreted as a literal string and added to the result as a quoted string: json_object('ex','[52,3.14159]') → '{"ex":"[52,3.14159]"}' json_object('ex',('[52,3.14159]'->>'$')) → '{"ex":"[52,3.14159]"}' But if the value argument in the outer json_object() call is the result of another JSON function like json() or json_array(), then the value is understood to be actual JSON and is inserted as such: json_object('ex',json('[52,3.14159]')) → '{"ex":[52,3.14159]}' json_object('ex',json_array(52,3.14159)) → '{"ex":[52,3.14159]}' json_object('ex','[52,3.14159]'->'$') → '{"ex":[52,3.14159]}' To be clear: "json" arguments are always interpreted as JSON regardless of where the value for that argument comes from. But "value" arguments are only interpreted as JSON if those arguments come directly from another JSON function or the -> operator. Within JSON value arguments interpreted as JSON strings, Unicode escape sequences are not treated as equivalent to the characters or escaped control characters represented by the expressed Unicode code point. Such escape sequences are not translated or specially treated; they are treated as plain text by SQLite's JSON functions. 3.5. Compatibility The current implementation of this JSON library uses a recursive descent parser. In order to avoid using excess stack space, any JSON input that has more than 1000 levels of nesting is considered invalid. Limits on nesting depth are allowed for compatible implementations of JSON by RFC-8259 section 9. 3.6. JSON5 Extensions Beginning in version 3.42.0 (2023-05-16), these routines will read and interpret input JSON text that includes JSON5 extensions. However, JSON text generated by these routines will always be strictly conforming to the canonical definition of JSON. Here is a synopsis of JSON5 extensions (adapted from the JSON5 specification): Object keys may be unquoted identifiers. Objects may have a single trailing comma. Arrays may have a single trailing comma. Strings may be single quoted. Strings may span multiple lines by escaping new line characters. Strings may include new character escapes. Numbers may be hexadecimal. Numbers may have a leading or trailing decimal point. Numbers may be "Infinity", "-Infinity", and "NaN". Numbers may begin with an explicit plus sign. Single (//...) and multi-line (/*...*/) comments are allowed.…