1# JSON Functions Enhancements (2022) 2 3This document summaries enhancements to the SQLite JSON support added in 4early 2022. 5 6## 1.0 Change summary: 7 8 1. New **->** and **->>** operators that work like MySQL and PostgreSQL (PG). 9 2. JSON functions are built-in rather than being an extension. They 10 are included by default, but can be omitted using the 11 -DSQLITE_OMIT_JSON compile-time option. 12 13 14## 2.0 New operators **->** and **->>** 15 16The SQLite language adds two new binary operators **->** and **->>**. 17Both operators are similar to json_extract(). The left operand is 18JSON and the right operand is a JSON path expression (possibly abbreviated 19for compatibility with PG - see below). So they are similar to a 20two-argument call to json_extract(). 21 22The difference between -> and ->> (and json_extract()) is as follows: 23 24 * The -> operator always returns JSON. 25 26 * The ->> operator converts the answer into a primitive SQL datatype 27 such as TEXT, INTEGER, REAL, or NULL. If a JSON object or array 28 is selected, that object or array is rendered as text. If a JSON 29 value is selected, that value is converted into its corresponding 30 SQL type 31 32 * The json_extract() interface returns JSON when a JSON object or 33 array is selected, or a primitive SQL datatype when a JSON value 34 is selected. This is different from MySQL, in which json_extract() 35 always returns JSON, but the difference is retained because it has 36 worked that way for 6 years and changing it now would likely break 37 a lot of legacy code. 38 39In MySQL and PG, the ->> operator always returns TEXT (or NULL) and never 40INTEGER or REAL. This is due to limitations in the type handling capabilities 41of those systems. In MySQL and PG, the result type a function or operator 42may only depend on the type of its arguments, never the value of its arguments. 43But the underlying JSON type depends on the value of the JSON path 44expression, not the type of the JSON path expression (which is always TEXT). 45Hence, the result type of ->> in MySQL and PG is unable to vary according 46to the type of the JSON value being extracted. 47 48The type system in SQLite is more general. Functions in SQLite are able 49to return different datatypes depending on the value of their arguments. 50So the ->> operator in SQLite is able to return TEXT, INTEGER, REAL, or NULL 51depending on the JSON type of the value being extracted. This means that 52the behavior of the ->> is slightly different in SQLite versus MySQL and PG 53in that it will sometimes return INTEGER and REAL values, depending on its 54inputs. It is possible to implement the ->> operator in SQLite so that it 55always operates exactly like MySQL and PG and always returns TEXT or NULL, 56but I have been unable to think of any situations where returning the 57actual JSON value this would cause problems, so I'm including the enhanced 58functionality in SQLite. 59 60The table below attempts to summarize the differences between the 61-> and ->> operators and the json_extract() function, for SQLite, MySQL, 62and PG. JSON values are shown using their SQL text representation but 63in a bold font. 64 65 66<table border=1 cellpadding=5 cellspacing=0> 67<tr><th>JSON<th>PATH<th>-> operator<br>(all)<th>->> operator<br>(MySQL/PG) 68 <th>->> operator<br>(SQLite)<th>json_extract()<br>(SQLite) 69<tr><td> **'{"a":123}'** <td>'$.a'<td> **'123'** <td> '123' <td> 123 <td> 123 70<tr><td> **'{"a":4.5}'** <td>'$.a'<td> **'4.5'** <td> '4.5' <td> 4.5 <td> 4.5 71<tr><td> **'{"a":"xyz"}'** <td>'$.a'<td> **'"xyz"'** <td> 'xyz' <td> 'xyz' <td> 'xyz' 72<tr><td> **'{"a":null}'** <td>'$.a'<td> **'null'** <td> NULL <td> NULL <td> NULL 73<tr><td> **'{"a":[6,7,8]}'** <td>'$.a'<td> **'[6,7,8]'** <td> '[6,7,8]' <td> '[6,7,8]' <td> **'[6,7,8]'** 74<tr><td> **'{"a":{"x":9}}'** <td>'$.a'<td> **'{"x":9}'** <td> '{"x":9}' <td> '{"x":9}' <td> **'{"x":9}'** 75<tr><td> **'{"b":999}'** <td>'$.a'<td> NULL <td> NULL <td> NULL <td> NULL 76</table> 77 78Important points about the table above: 79 80 * The -> operator always returns either JSON or NULL. 81 82 * The ->> operator never returns JSON. It always returns TEXT or NULL, or in the 83 case of SQLite, INTEGER or REAL. 84 85 * The MySQL json_extract() function works exactly the same 86 as the MySQL -> operator. 87 88 * The SQLite json_extract() operator works like -> for JSON objects and 89 arrays, and like ->> for JSON values. 90 91 * The -> operator works the same for all systems. 92 93 * The only difference in ->> between SQLite and other systems is that 94 when the JSON value is numeric, SQLite returns a numeric SQL value, 95 whereas the other systems return a text representation of the numeric 96 value. 97 98### 2.1 Abbreviated JSON path expressions for PG compatibility 99 100The table above always shows the full JSON path expression: '$.a'. But 101PG does not accept this syntax. PG only allows a single JSON object label 102name or a single integer array index. In order to provide compatibility 103with PG, The -> and ->> operators in SQLite are extended to also support 104a JSON object label or an integer array index for the right-hand side 105operand, in addition to a full JSON path expression. 106 107Thus, a -> or ->> operator that works on MySQL will work in 108SQLite. And a -> or ->> operator that works in PG will work in SQLite. 109But because SQLite supports the union of the disjoint capabilities of 110MySQL and PG, there will always be -> and ->> operators that work in 111SQLite that do not work in one of MySQL and PG. This is an unavoidable 112consequence of the different syntax for -> and ->> in MySQL and PG. 113 114In the following table, assume that "value1" is a JSON object and 115"value2" is a JSON array. 116 117<table border=1 cellpadding=5 cellspacing=0> 118<tr><th>SQL expression <th>Works in MySQL?<th>Works in PG?<th>Works in SQLite 119<tr><td>value1->'$.a' <td> yes <td> no <td> yes 120<tr><td>value1->'a' <td> no <td> yes <td> yes 121<tr><td>value2->'$[2]' <td> yes <td> no <td> yes 122<tr><td>value2->2 <td> no <td> yes <td> yes 123</table> 124 125The abbreviated JSON path expressions only work for the -> and ->> operators 126in SQLite. The json_extract() function, and all other built-in SQLite 127JSON functions, continue to require complete JSON path expressions for their 128PATH arguments. 129 130## 3.0 JSON moved into the core 131 132The JSON interface is now moved into the SQLite core. 133 134When originally written in 2015, the JSON functions were an extension 135that could be optionally included at compile-time, or loaded at run-time. 136The implementation was in a source file named ext/misc/json1.c in the 137source tree. JSON functions were only compiled in if the 138-DSQLITE_ENABLE_JSON1 compile-time option was used. 139 140After these enhancements, the JSON functions are now built-ins. 141The source file that implements the JSON functions is moved to src/json.c. 142No special compile-time options are needed to load JSON into the build. 143Instead, there is a new -DSQLITE_OMIT_JSON compile-time option to leave 144them out. 145