Documentation Source Text

<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 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
  incr tabcnt
  hd_puts "<tr><td width=30 valign='top'>$tabcnt.</td>"
  hd_puts "<td valign='top' width='30%'>\n"
  set fx [string trim $fx]
  set hlink "<a href='#$lnk'>"
  regsub -all {^json\(} $fx "${hlink}json</a>(" fx
  regsub -all {(json_[a-z_]+)} $fx "$hlink\\1</a>" fx
  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"
}
set tsctr 100
proc jexample {args} {
  hd_puts "<blockquote><table border=0 cellpadding=0>\n"
  foreach {sql res} $args {
    ### uncomment to generate test cases from the examples
    # global tsctr
    # puts "do_execsql_test json102-$tsctr \173"
    # incr tsctr 10
    # puts "  SELECT [string trim $sql];"
    # set x \173[string trim $res ']\175
    # puts "\175 \173$x\175"
    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(json)} {
  Validate and minify a JSON string
} jmini

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

tabentry {json_quote(value)} {
  Convert an SQL value (a number or a string) into its corresponding JSON
  representation.
} 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>
tabentry {json_each(json)<br>json_each(json,path)} {
  Return one row describing each element in an array or object
  at the top-level or at "path" within the input JSON.
} jeach

tabentry {json_tree(json)<br>json_tree(json,path)} {
  Walk the JSON recursively starting at the top-level or at the
  specified "path" and return one row for each element.
} jtree
</tcl>
</table></center></blockquote>


























<tcl>hd_fragment howtocompile</tcl>
<h1>Compiling the JSON1 Extension</h1>

<p>
The [loadable extensions] documentation describes
how to [compile loadable extensions] as shared libraries.  The
................................................................................
come directly from another json1 function.

<h2>Compatibility</h2>

<p>
The json1 extension uses the [sqlite3_value_subtype()] and
[sqlite3_result_subtype()] interfaces that were introduced with

SQLite version 3.9.0.  Therefore the json1 extension will not work
in earlier versions of SQLite.

<h1>Function Details</h1>

<p>The following sections provide additional detail on the operation of
the various functions that are part of the json1 extension.

................................................................................
a single JSON value as their first argument followed by zero or more
pairs of path and value arguments, and return a new JSON string formed
by updating the input JSON by the path/value pairs.  The functions
differ only in how they deal with creating new values and overwriting
preexisting values.

<center>
<table border=1 cellpadding=3>
<tr>
<th>Function<th>Overwrite if already exists?<th>Create if does not exist?
<tr>
<td>json_insert()<td align='center'>No<td align='center'>Yes
<tr>
<td>json_replace()<td align='center'>Yes<td align='center'>No
<tr>

<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 fourteen [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
proc tabentry {fx desc lnk} {
  global tabcnt
  incr tabcnt
  hd_puts "<li value='$tabcnt'>\n"

  set fx [string trim $fx]
  set hlink "<a href='#$lnk'>"
  regsub -all {^json\(} $fx "${hlink}json</a>(" fx
  regsub -all {(json_[a-z_]+)} $fx "$hlink\\1</a>" fx
  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 "[string trim $desc]\n"
  hd_puts "</li>\n\n"
}
set tsctr 100

















tabentry {json(json)} {
  Validate and minify a JSON string
} jmini

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

tabentry {json_quote(value)} {
  Convert an SQL value (a number or a string) into its corresponding JSON
  representation.
} jvalid
</tcl>
</ol>

<p>There are two aggregate SQL functions:

<ol>
<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>
</ol>

<p>The two [table-valued functions] are:

<ol>
<tcl>
tabentry {json_each(json)<br>json_each(json,path)} {
  Return one row describing each element in an array or object
  at the top-level or at "path" within the input JSON.
} jeach

tabentry {json_tree(json)<br>json_tree(json,path)} {
  Walk the JSON recursively starting at the top-level or at the
  specified "path" and return one row for each element.
} jtree
</tcl>
</ol>


<tcl>
hd_puts {
<style>
.jans {color: #050;}
.jex {color: #025;}
</style>
}
proc jexample {args} {
  hd_puts "<ul>\n"
  foreach {sql res} $args {
    ### uncomment to generate test cases from the examples
    # global tsctr
    # puts "do_execsql_test json102-$tsctr \173"
    # incr tsctr 10
    # puts "  SELECT [string trim $sql];"
    # set x \173[string trim $res ']\175
    # puts "\175 \173$x\175"
    hd_putsnl "<li><span class='jex'>[string trim $sql]</span>"
    hd_putsnl "<span class='jans'>&rarr; [string trim $res]</span></li>\n"
  }
  hd_puts "</ul>\n"
}
</tcl>

<tcl>hd_fragment howtocompile</tcl>
<h1>Compiling the JSON1 Extension</h1>

<p>
The [loadable extensions] documentation describes
how to [compile loadable extensions] as shared libraries.  The
................................................................................
come directly from another json1 function.

<h2>Compatibility</h2>

<p>
The json1 extension uses the [sqlite3_value_subtype()] and
[sqlite3_result_subtype()] interfaces that were introduced with
SQLite version 3.9.0 (circa 2015-10-14).
The json1 extension will not work
in earlier versions of SQLite.

<h1>Function Details</h1>

<p>The following sections provide additional detail on the operation of
the various functions that are part of the json1 extension.

................................................................................
a single JSON value as their first argument followed by zero or more
pairs of path and value arguments, and return a new JSON string formed
by updating the input JSON by the path/value pairs.  The functions
differ only in how they deal with creating new values and overwriting
preexisting values.

<center>
<table border=1 cellpadding=3 cellspacing=0>
<tr>
<th>Function<th>Overwrite if already exists?<th>Create if does not exist?
<tr>
<td>json_insert()<td align='center'>No<td align='center'>Yes
<tr>
<td>json_replace()<td align='center'>Yes<td align='center'>No
<tr>