Documentation Source Text

  regsub -all {(value[1-9]?|path|label[1-9]?)} $fx "<i>\\1</i>" fx
  regsub -all {\((json)} $fx "(<i>\\1</i>" fx
  hd_puts $fx\n
  hd_puts "</td><td valign='top'>\n"
  hd_puts [string trim $desc]\n
  hd_puts "</td></tr>\n\n"
}











tabentry {json_array(value1,value2,...)} {
  Return a JSON array holding the function arguments.
} jarray

tabentry {json_array_length(json)<br>json_array_length(json,path)} {
  Return a JSON array holding the function arguments.  The optional
................................................................................
<p>In the current implementation, if an argument to json_array() is
text that looks like JSON, it is quoted and interpreted as a single
JSON string value.  In future enhancements in which a SQLite text 
value can have a sub-type of "JSON", this routine will insert 
substructure instead of a single string value.  Please beware of this
future incompatibility and plan accordingly.












<tcl>hd_fragment jarraylen {json_array_length SQL function} \
         {json_array_length}</tcl>
<h3>2.2 The json_array_length() function</h3>

<p>The json_array_length(J) function returns the number of elements
in the JSON array J, or 0 if J is some kind of JSON value other
than an array.  The json_array_length(J,P) locates the array at path P
within J and returns the length of that array, or 0 if path P does not
locate an array within J.  Errors are thrown if either J is not 
well-formed JSON or if P is not a well-formed path.











<tcl>hd_fragment jex {json_extract SQL function} {json_extract}</tcl>
<h3>2.3 The json_extract() function</h3>

<p>The json_extract(J,P1,P2,...) extracts and returns one or more 
values from the
well-formed JSON at J.  If only a single path P1 is provided, then the
................................................................................
SQLite type of the value returned is NULL for a JSON null, INTEGER or REAL
for a JSON numeric value, INTEGER 0 for a JSON false value, INTEGER 1
for a JSON true value, the dequoted text for a JSON string value, and
a text representation for JSON object and array values.
If there are multiple path arguments (P1, P2, and so forth) then this
routine returns an SQLite text which is a well-formed JSON array holding
the various values.
















<tcl>hd_fragment jins {json_insert SQL function} {json_insert}</tcl>
<tcl>hd_fragment jrepl {json_replace SQL function} {json_replace}</tcl>
<tcl>hd_fragment jset {json_set SQL function} {json_set}</tcl>
<h3>2.4 The json_insert(), json_replace, and json_set() functions</h3>

<p>The json_insert(), json_replace, and json_set() functions all take
................................................................................
a sub-type of JSON and will only quote and insert values as JSON
strings if the input value is not of sub-type JSON.

<p>These routines throw an error if the first JSON argument is not
well-formed or if any PATH argument is not well-formed or if any
argument is of type BLOB.













<tcl>hd_fragment jobj {json_object SQL function} {json_object}</tcl>
<h3>2.5 The json_object() function</h3>

<p>The json_object() SQL function accepts zero or more pairs of arguments
and returns a well-formed JSON object that is composed from those arguments.
The first argument of each pair is the label and the second argument of
................................................................................

<p>In the current implementation, if a value argument to json_object() is
text that looks like JSON, it is quoted and interpreted as a single
JSON string value.  In future enhancements in which a SQLite text 
value can have a sub-type of "JSON", this routine will insert 
substructure instead of a single string value.  Please beware of this
future incompatibility and plan accordingly.










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

<p>The json_remove(J,P,...) function takes a single JSON value as its
first argument followed by zero or more path arguments in the second
................................................................................
then it returns the input J reformatted, with excess whitespace
removed.

<p>The json_remove() function throws an error if the first argument
is not well-formed JSON or if any later argument is not a well-formed
path, or if any argument is a BLOB.














<tcl>hd_fragment jtype {json_type SQL function} {json_type}</tcl>
<h3>2.7 The json_type() function</h3>

<p>The json_type(J) function returns the "type" of the outermost element
of J.  The json_type(J,P) function returns the "type" of the element
in J that is selected by path P.  The "type" returned by json_type() is
................................................................................
an SQL text value which is one of the following:
'null', 'true', 'false', 'integer', 'real', 'text', 'array', or 'object'.
If the path P in json_type(J,P) selects a element that does not exist
in J, then the function returns NULL.

<p>The json_type() function throws an error if any of its arguments are
not well-formed or are a BLOB.

















<tcl>hd_fragment jvalid {json_valid SQL function} {json_valid}</tcl>
<h3>2.8 The json_valid() function</h3>

<p>The json_valid(J) function return 1 if the argument J is well-formed
JSON and return 0 if the argument J is not well-formed JSON.










<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>2.9 The json_each() and json_tree() table-valued functions</h3>

<p>The json_each(J) and json_tree(J) [table-valued functions] walk the
JSON value provided as their first argument and return one row for each
................................................................................
The "parent" column is the "id" integer for the parent of the current
element, or NULL for the top-level JSON element.

<p>
The "fullkey" column is a text path that uniquely identifies the current
row element within the original JSON string.



<h2>3.0 Examples</h2>

  regsub -all {(value[1-9]?|path|label[1-9]?)} $fx "<i>\\1</i>" fx
  regsub -all {\((json)} $fx "(<i>\\1</i>" fx
  hd_puts $fx\n
  hd_puts "</td><td valign='top'>\n"
  hd_puts [string trim $desc]\n
  hd_puts "</td></tr>\n\n"
}
proc jexample {args} {
  hd_puts "<blockquote><table border=0 cellpadding=0>\n"
  foreach {sql res} $args {
    # puts "SELECT [string trim $sql];\n-- [string trim $res]"
    hd_puts "<tr><td>[string trim $sql]</td>\n"
    hd_puts "<td width='50' align='center'><b>&rarr;</b></td>\n"
    hd_puts "<td>[string trim $res]</td></tr>\n"
  }
  hd_puts "</table></blockquote>\n"
}

tabentry {json_array(value1,value2,...)} {
  Return a JSON array holding the function arguments.
} jarray

tabentry {json_array_length(json)<br>json_array_length(json,path)} {
  Return a JSON array holding the function arguments.  The optional
................................................................................
<p>In the current implementation, if an argument to json_array() is
text that looks like JSON, it is quoted and interpreted as a single
JSON string value.  In future enhancements in which a SQLite text 
value can have a sub-type of "JSON", this routine will insert 
substructure instead of a single string value.  Please beware of this
future incompatibility and plan accordingly.

<p>Examples:

<tcl>
jexample \
  {json_array(1,2,'3',4)} {'[1,2,"3",4]'} \
  {json_array('[1,2]')} {'["[1,2]"]'} \
  {json_array(1,null,'3','[4,5]','{"six":7.7}')} \
      {'[1,null,"3","[4,5]","{\"six\":7.7}"]'}
</tcl>


<tcl>hd_fragment jarraylen {json_array_length SQL function} \
         {json_array_length}</tcl>
<h3>2.2 The json_array_length() function</h3>

<p>The json_array_length(J) function returns the number of elements
in the JSON array J, or 0 if J is some kind of JSON value other
than an array.  The json_array_length(J,P) locates the array at path P
within J and returns the length of that array, or 0 if path P does not
locate an array within J.  Errors are thrown if either J is not 
well-formed JSON or if P is not a well-formed path.

<p>Examples:

<tcl>
jexample \
  {json_array_length('[1,2,3,4]')} {4} \
  {json_array_length('{"one":[1,2,3]}')} {0} \
  {json_array_length('{"one":[1,2,3]}', '$.one')} {3}
</tcl>


<tcl>hd_fragment jex {json_extract SQL function} {json_extract}</tcl>
<h3>2.3 The json_extract() function</h3>

<p>The json_extract(J,P1,P2,...) extracts and returns one or more 
values from the
well-formed JSON at J.  If only a single path P1 is provided, then the
................................................................................
SQLite type of the value returned is NULL for a JSON null, INTEGER or REAL
for a JSON numeric value, INTEGER 0 for a JSON false value, INTEGER 1
for a JSON true value, the dequoted text for a JSON string value, and
a text representation for JSON object and array values.
If there are multiple path arguments (P1, P2, and so forth) then this
routine returns an SQLite text which is a well-formed JSON array holding
the various values.

<p>Examples:

<tcl>
jexample \
  {json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$')} \
      {'{"a":2,"c":[4,5,{"f":7}]}'} \
  {json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c')} \
      {'[4,5,{"f":7}]'} \
  {json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]')} \
      {'{"f":7}'} \
  {json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f')} {7} \
  {json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a')} {'[[4,5],2]'} \
  {json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x')} NULL
</tcl>  

<tcl>hd_fragment jins {json_insert SQL function} {json_insert}</tcl>
<tcl>hd_fragment jrepl {json_replace SQL function} {json_replace}</tcl>
<tcl>hd_fragment jset {json_set SQL function} {json_set}</tcl>
<h3>2.4 The json_insert(), json_replace, and json_set() functions</h3>

<p>The json_insert(), json_replace, and json_set() functions all take
................................................................................
a sub-type of JSON and will only quote and insert values as JSON
strings if the input value is not of sub-type JSON.

<p>These routines throw an error if the first JSON argument is not
well-formed or if any PATH argument is not well-formed or if any
argument is of type BLOB.

<p>Examples:

<tcl>
jexample \
  {json_insert('{"a":2,"c":4}', '$.a', 99)} {'{"a":2,"c":4}'} \
  {json_insert('{"a":2,"c":4}', '$.e', 99)} {'{"a":2,"c":4,"e":99}'} \
  {json_replace('{"a":2,"c":4}', '$.a', 99)} {'{"a":99,"c":4}'} \
  {json_replace('{"a":2,"c":4}', '$.e', 99)} {'{"a":2,"c":4}'} \
  {json_set('{"a":2,"c":4}', '$.a', 99)} {'{"a":99,"c":4}'} \
  {json_set('{"a":2,"c":4}', '$.e', 99)} {'{"a":2,"c":4,"e":99}'}
</tcl>

<tcl>hd_fragment jobj {json_object SQL function} {json_object}</tcl>
<h3>2.5 The json_object() function</h3>

<p>The json_object() SQL function accepts zero or more pairs of arguments
and returns a well-formed JSON object that is composed from those arguments.
The first argument of each pair is the label and the second argument of
................................................................................

<p>In the current implementation, if a value argument to json_object() is
text that looks like JSON, it is quoted and interpreted as a single
JSON string value.  In future enhancements in which a SQLite text 
value can have a sub-type of "JSON", this routine will insert 
substructure instead of a single string value.  Please beware of this
future incompatibility and plan accordingly.

<p>Examples:

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


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

<p>The json_remove(J,P,...) function takes a single JSON value as its
first argument followed by zero or more path arguments in the second
................................................................................
then it returns the input J reformatted, with excess whitespace
removed.

<p>The json_remove() function throws an error if the first argument
is not well-formed JSON or if any later argument is not a well-formed
path, or if any argument is a BLOB.

<p>Examples:

<tcl>
jexample \
  {json_remove('[0,1,2,3,4]','$[2]')} {'[0,1,3,4]'} \
  {json_remove('[0,1,2,3,4]','$[2]',$[0])} {'[1,3,4]'} \
  {json_remove('[0,1,2,3,4]','$[0]',$[2])} {'[1,2,4]'} \
  {json_remove('{"x":25,"y":42}')} {'{"x":25,"y":42}'} \
  {json_remove('{"x":25,"y":42}','$.z')} {'{"x":25,"y":42}'} \
  {json_remove('{"x":25,"y":42}','$.y')} {'{"x":25}'} \
  {json_remove('{"x":25,"y":42}','$')} NULL
</tcl>

<tcl>hd_fragment jtype {json_type SQL function} {json_type}</tcl>
<h3>2.7 The json_type() function</h3>

<p>The json_type(J) function returns the "type" of the outermost element
of J.  The json_type(J,P) function returns the "type" of the element
in J that is selected by path P.  The "type" returned by json_type() is
................................................................................
an SQL text value which is one of the following:
'null', 'true', 'false', 'integer', 'real', 'text', 'array', or 'object'.
If the path P in json_type(J,P) selects a element that does not exist
in J, then the function returns NULL.

<p>The json_type() function throws an error if any of its arguments are
not well-formed or are a BLOB.

<p>Examples:

<tcl>
jexample \
  {json_type('{"a":[2,3.5,true,false,null,"x"]}')} 'object' \
  {json_type('{"a":[2,3.5,true,false,null,"x"]}','$')} 'object' \
  {json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a')} 'array' \
  {json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]')} 'integer' \
  {json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]')} 'real' \
  {json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]')} 'true' \
  {json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]')} 'false' \
  {json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]')} 'null' \
  {json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]')} 'text' \
  {json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]')} NULL
</tcl>

<tcl>hd_fragment jvalid {json_valid SQL function} {json_valid}</tcl>
<h3>2.8 The json_valid() function</h3>

<p>The json_valid(J) function return 1 if the argument J is well-formed
JSON and return 0 if the argument J is not well-formed JSON.

<p>Examples:

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


<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>2.9 The json_each() and json_tree() table-valued functions</h3>

<p>The json_each(J) and json_tree(J) [table-valued functions] walk the
JSON value provided as their first argument and return one row for each
................................................................................
The "parent" column is the "id" integer for the parent of the current
element, or NULL for the top-level JSON element.

<p>
The "fullkey" column is a text path that uniquely identifies the current
row element within the original JSON string.

<h4>2.9.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
  FROM user, json_each(user.phone)
 WHERE json_each.value LIKE '704-%';
</pre></blockquote>

<p>Now suppose the user.phone field contains plain text if the user
has only a single phone number and a JSON array if the user has multiple
phone numbers.  We ask the same question: Which uses have a phone number
in the 704 area code.  But now the json_each() function can only be called
for those users that have two or more phone numbers:

<blockquote><pre>
SELECT name FROM user WHERE phone LIKE '704-%'
UNION
SELECT user.name
  FROM user, json_each(user.phone)
 WHERE json_valid(user.phone)
   AND json_each.value LIKE '704-%';
</pre></blockquote>

<p>Consider a different database with "CREATE TABLE big(json JSON)".
To see a complete line-by-line decomposition of the data:

<blockquote><pre>
SELECT big.rowid, fullkey, value
  FROM big, json_tree(big.json)
 WHERE json_tree.type NOT IN ('object','array');
</pre></blockquote>

<p>In the previous, the "type NOT IN ('object','array')" term of the
WHERE clause suppresses containers and only lets through leaf elements.
The same effect could be achieved this way:

<blockquote><pre>
SELECT big.rowid, fullkey, atom
  FROM big, json_tree(big.json)
 WHERE atom IS NOT NULL;
</pre></blockquote>

<p>Suppose each entry in the BIG table is an object 
with an '$.id' field that is a unique indentifier
and a '$.partlist' field that is a deeply nested object.
You want to find the id of every entry that contains one
or more references to uuid '6fa5181e-5721-11e5-a04e-57f3d7b32808' anywhere
in its '$.partlist'.

<blockquote><pre>
SELECT DISTINCT json_extract(big.json,'$.id')
  FROM big, json_tree(big.json, '$.partlist')
 WHERE json_tree.key='uuid'
   AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
</pre></blockquote>