Documentation Source Text

<li>Enhance the [PRAGMA cache_spill] statement to accept a 32-bit integer
    parameter which is the threshold below which cache spilling is prohibited.
<li>On unix, if a symlink to a database file is opened, then the corresponding
    journal files are based on the actual filename, not the symlink name.
<li>Added the "--transaction" option to [sqldiff].
<li>Added the [sqlite3_db_cacheflush()] interface.
<li>Added the [sqlite3_strlike()] interface.
<li>When using [memory-mapped I/O] map the database file read-only so that stray pointers
    and/or array overruns in the application cannot accidently modify the database file.
<li>Added the <em>experimental</em> [sqlite3_snapshot_get()], [sqlite3_snapshot_open()],
    and [sqlite3_snapshot_free()] interfaces.  These are subject to change or removal in
    a subsequent release.
<li>Enhance the ['utc' modifier] in the [date and time functions] so that it is a no-op if
    the date/time is known to already be in UTC.  (This is not a compatibility break since
    the behavior has long been documented as "undefined" in that case.)
<li>Added the [json_group_array()] and [json_group_object()] SQL functions in the
    
<li>Many small performance optimizations.
<p><b>Portability enhancements:</b>
<li>Work around a sign-exension bug in the optimizer of the HP C compiler on HP/UX.
<p><b>Enhancements to the [command-line shell]:</b>
<li>Added the ".changes ON|OFF" and ".vfsinfo dot-commands.
<li>Translate between MBCS and UTF8 when
    running in [https://en.wikipedia.org/wiki/Cmd.exe|cmd.exe] on Windows.
<p><b>Enhancements to makefiles:</b>
<li>Added the --enable-editline and --enable-static-shell options
    to the various autoconf-generated configure scripts.
<li>Omit all use of "awk" in the makefiles, to make building easier for MSVC users.

<p><b>Important fixes:</b>
<li>Fix inconsistent integer to floating-point comparison operations that
    could result in a corrupt index if the index is created on a table
    column that contains both large integers and floating point values
    of similar magnitude.  Ticket 
    [https://www.sqlite.org/src/tktview?name=38a97a87a6|38a97a87a6].
<li>Fix an infinite-loop in the query planner that could occur on

<title>The JSON1 Extension</title>
<tcl>hd_keywords json1 {the json1 extension} {JSON SQL functions}</tcl>
<h2>The JSON1 Extension</h2>

<p>
The <b>json1</b> extension is a [loadable extension] that
implements thirteen [application-defined SQL functions] and
two [table-valued functions] that are useful for
managing [http://json.org/ | JSON] content stored in an SQLite database.
These are the scalar SQL functions implemented by json1:

<blockquote>
<center><table border=0 cellpadding=5>
<tcl>
set tabcnt 0
proc tabentry {fx desc lnk} {
  global tabcnt

tabentry {json_type(json)<br>json_type(json,path)} {
  Return the type of a JSON string or subcomponent.
} jtype

tabentry {json_valid(json)} {
  Return true (1) if the input text is a valid JSON string
} jvalid
</tcl>
</table></center></blockquote>

<p>There are two aggregate SQL functions:

<blockquote><center><table border=0 cellpadding=5>
<tcl>
tabentry {json_group_array(value)} {
  Return a JSON array composed of all <i>value</i> elements 
  in the aggregation.
} jgrouparray

tabentry {json_group_object(name,value)} {
  Return a JSON object composed of all <i>name</i> and <i>value</i> pairs
  in the aggregation.
} jgroupobject
</tcl>
</table></center></blockquote>

<p>The [table-valued functions] implemented by this routine are:

<blockquote><center><table border=0 cellpadding=5>
<tcl>

<tcl>
jexample \
  {json_valid('{"x":35}')} 1 \
  "json_valid('\173\"x\":35')" 0
</tcl>

<tcl>
hd_fragment jgrouparray {json_group_array SQL function} \
   {json_group_array}
hd_fragment jgroupobject {json_group_object SQL function} \
   {json_group_object}
</tcl>
<h3>3.10 The json_group_array() and json_group_object()
aggregate SQL functions</h3>

<p>The json_group_array(X) function is an 
[Aggregate Functions|aggregate SQL function] that returns a JSON array
comprised of all X values in the aggregation.
Similarly, the json_group_object(NAME,VALUE) function returns a JSON object
comprised of all NAME/VALUE pairs in the aggregation.


<tcl>hd_fragment jeach {json_each table-valued function} {json_each}</tcl>
<tcl>hd_fragment jtree {json_tree table-valued function} {json_tree}</tcl>
<h3>3.11 The json_each() and json_tree() table-valued functions</h3>

<p>The json_each(X) and json_tree(X) [table-valued functions] walk the
JSON value provided as their first argument and return one row for each
element.  The json_each(X) function only walks the immediate children
of the top-level array or object or 
or just the top-level element itself if the top-level
element is a primitive value.

<p>
The "path" column is the path to the array or object container the holds 
the current row, or the path to the current row in the case where the 
iteration starts on a primitive type and thus only provides a single
row of output.

<h4>3.11.1 Examples using json_each() and json_tree()</h4>

<p>Suppose the table "CREATE TABLE user(name,phone)" stores zero or
more phone numbers as a JSON array object in the user.phone field.
To find all users who have any phone number with a 704 area code:

<blockquote><pre>
SELECT DISTINCT user.name

separate the "unixepoch" modifier from prior DDDDDDDDDD then the
behavior is undefined.
Due to precision limitations imposed by the implementations use
of 64-bit integers, the "unixepoch" modifier only works for
dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times
of -62167219200 through 10675199167).</p>

<tcl>hd_fragment localtime {localtime modifier} {'utc' modifier}</tcl>
<p>^The "localtime" modifier (12) assumes the time string to its left is in
Universal Coordinated Time (UTC) and adjusts the time
string so that it displays localtime.  If "localtime"
follows a time that is not UTC, then the behavior is undefined.
^(The "utc" modifier is the opposite of "localtime".  
"utc" assumes that the string
to its left is in the local timezone and adjusts that string to be in UTC.)^
If the prior string is not in localtime, then the result of "utc" is
undefined.</p>

<h3>Examples</h3>

^(<p>Compute the current date.<p>