Documentation Source Text

}

chng {2017-04-00 (3.18.0)} {
<li>Added the [PRAGMA optimize] command
<li>The SQLite version identifier returned by the [sqlite_source_id()] SQL function
    and the [sqlite3_sourceid()] C API and found in the [SQLITE_SOURCE_ID] macro is
    now a 64-digit SHA3-256 hash instead of a 40-digit SHA1 hash.
<li>Added the [json_patch()] SQL function to the [json1|JSON1 extension].
<li>Enhance the [LIKE optimization] so that it works for arbitrary expressions on 
    the left-hand side as long as the LIKE pattern on the right-hand side does not
    begin with a digit or minus sign.
<li>Added the [sqlite3_set_last_insert_rowid()] interface and use the new interface in 
    the [FTS3], [FTS4], and [FTS5] extensions to ensure that the [sqlite3_last_insert_rowid()]
    interface always returns reasonable values.
<li>Enhance [PRAGMA integrity_check] and [PRAGMA quick_check] so that they verify

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

<table_of_contents>

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

<ol>
<tcl>
set tabcnt 0

tabentry {json_insert(json,path,value,...)} {
  Insert values into a JSON string without overwriting existing values.
} jins

tabentry {json_object(label1,value1,...)} {
  Construct and return a new JSON object based on the arguments.
} jobj

tabentry {json_patch(json1,json2)} {
  Apply the [https://tools.ietf.org/html/rfc7396|RFC-7396] MergePatch
  algorithm.
} jpatch

tabentry {json_remove(json,path,...)} {
  Remove the specified values from a JSON string.
} jrm

tabentry {json_replace(json,path,value,...)} {
  Update existing values within a JSON string.

<tcl>
jexample \
  {json_object('a',2,'c',4)} {'{"a":2,"c":4}'} \
  {json_object('a',2,'c','{e:5}')} {'{"a":2,"c":"{e:5}"}'} \
  {json_object('a',2,'c',json_object('e',5))} {'{"a":2,"c":{"e":5}}'}
</tcl>

<tcl>hd_fragment jpatch {json_patch SQL function} {json_patch}</tcl>
<h2>The json_patch() function</h2>

<p>The json_patch(T,P) SQL function runs the
[https://tools.ietf.org/html/rfc7396|RFC-7396] MergePatch algorithm
to apply patch P against input T.  The patched copy of T is returned.

<p>MergePatch can add, modify, or delete elements of a JSON Object,
and so for JSON Objects, the json_patch() routine is a generalized
replacement for [json_set()] and [json_remove()].  However, MergePatch
treats JSON Array objects as atomic.  MergePatch cannot append to an
Array nor modify individual elements of an Array.  It can only insert,
replace, or delete the whole Array as a single unit.  Hence, json_patch()
is not as useful when dealing with JSON that includes Arrays,
especially Arrays with lots of substructure.

<p>Examples:

<tcl>
jexample \
 {json_patch('{"a":1,"b":2}','{"c":3,"d":4}')} \
         {'{"a":1,"b":2,"c":3,"d":4}'} \
 {json_patch('{"a":[1,2],"b":2}','{"a":9}')} \
         {'{"a":9,"b":2}'} \
 {json_patch('{"a":[1,2],"b":2}','{"a":null}')} \
         {'{"b":2}'} \
 {json_patch('{"a":1,"b":2}','{"a":9,"b":null,"c":8}')} \
         {'{"a":9,"c":8}'} \
 {json_patch('{"a":{"x":1,"y":2},"b":3}','{"a":{"y":9},"c":8}')} \
         {'{"a":{"x":1,"y":9},"b":3,"c":8}'}
</tcl>

<tcl>hd_fragment jrm {json_remove SQL function} {json_remove}</tcl>
<h2>The json_remove() function</h2>

<p>The json_remove(X,P,...) function takes a single JSON value as its
first argument followed by zero or more path arguments.
The json_remove(X,P,...) function returns