doc/json-enhancements.md

   1 # JSON Functions Enhancements (2022)
   2
   3 This document summaries enhancements to the SQLite JSON support added in
   4 early 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
  16 The SQLite language adds two new binary operators **->** and **->>**.
  17 Both operators are similar to json_extract().  The left operand is
  18 JSON and the right operand is a JSON path expression (possibly abbreviated
  19 for compatibility with PG - see below).  So they are similar to a
  20 two-argument call to json_extract().
  21
  22 The 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
  39 In MySQL and PG, the ->> operator always returns TEXT (or NULL) and never
  40 INTEGER or REAL.  This is due to limitations in the type handling capabilities
  41 of those systems.  In MySQL and PG, the result type a function or operator
  42 may only depend on the type of its arguments, never the value of its arguments.
  43 But the underlying JSON type depends on the value of the JSON path
  44 expression, not the type of the JSON path expression (which is always TEXT).
  45 Hence, the result type of ->> in MySQL and PG is unable to vary according
  46 to the type of the JSON value being extracted.
  47
  48 The type system in SQLite is more general.  Functions in SQLite are able
  49 to return different datatypes depending on the value of their arguments.
  50 So the ->> operator in SQLite is able to return TEXT, INTEGER, REAL, or NULL
  51 depending on the JSON type of the value being extracted.  This means that
  52 the behavior of the ->> is slightly different in SQLite versus MySQL and PG
  53 in that it will sometimes return INTEGER and REAL values, depending on its
  54 inputs.  It is possible to implement the ->> operator in SQLite so that it
  55 always operates exactly like MySQL and PG and always returns TEXT or NULL,
  56 but I have been unable to think of any situations where returning the
  57 actual JSON value this would cause problems, so I'm including the enhanced
  58 functionality in SQLite.
  59
  60 The table below attempts to summarize the differences between the
  61 -> and ->> operators and the json_extract() function, for SQLite, MySQL,
  62 and PG.  JSON values are shown using their SQL text representation but
  63 in a bold font.
  64
  65
  66 <table border=1 cellpadding=5 cellspacing=0>
  67 <tr><th>JSON<th>PATH<th>-&gt; operator<br>(all)<th>-&gt;&gt; operator<br>(MySQL/PG)
  68     <th>-&gt;&gt; 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
  78 Important 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
 100 The table above always shows the full JSON path expression: '$.a'.  But
 101 PG does not accept this syntax.  PG only allows a single JSON object label
 102 name or a single integer array index.  In order to provide compatibility
 103 with PG, The -> and ->> operators in SQLite are extended to also support
 104 a JSON object label or an integer array index for the right-hand side
 105 operand, in addition to a full JSON path expression.
 106
 107 Thus, a -> or ->> operator that works on MySQL will work in
 108 SQLite.  And a -> or ->> operator that works in PG will work in SQLite.
 109 But because SQLite supports the union of the disjoint capabilities of
 110 MySQL and PG, there will always be -> and ->> operators that work in
 111 SQLite that do not work in one of MySQL and PG.  This is an unavoidable
 112 consequence of the different syntax for -> and ->> in MySQL and PG.
 113
 114 In 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-&gt;'$.a'   <td> yes           <td>  no        <td> yes
 120 <tr><td>value1-&gt;'a'     <td> no            <td>  yes       <td> yes
 121 <tr><td>value2-&gt;'$[2]'  <td> yes           <td>  no        <td> yes
 122 <tr><td>value2-&gt;2       <td> no            <td>  yes       <td> yes
 123 </table>
 124
 125 The abbreviated JSON path expressions only work for the -> and ->> operators
 126 in SQLite.  The json_extract() function, and all other built-in SQLite
 127 JSON functions, continue to require complete JSON path expressions for their
 128 PATH arguments.
 129
 130 ## 3.0 JSON moved into the core
 131
 132 The JSON interface is now moved into the SQLite core.
 133
 134 When originally written in 2015, the JSON functions were an extension
 135 that could be optionally included at compile-time, or loaded at run-time.
 136 The implementation was in a source file named ext/misc/json1.c in the
 137 source tree.  JSON functions were only compiled in if the
 138 -DSQLITE_ENABLE_JSON1 compile-time option was used.
 139
 140 After these enhancements, the JSON functions are now built-ins.
 141 The source file that implements the JSON functions is moved to src/json.c.
 142 No special compile-time options are needed to load JSON into the build.
 143 Instead, there is a new -DSQLITE_OMIT_JSON compile-time option to leave
 144 them out.