Documentation Source Text

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

<h2>1.0 Ground Rules</h2>

<p>

<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
element.  The json_each(J) 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.
The json_tree(J) function recursively walks through the
JSON substructure starting with the top-level element.  

<p>The json_each(J,P) and json_tree(J,P) functions work just like
their one-argument counterparts except that they treat the element
identified by path P as the top-level element.

<p>The schema for the table returned by json_each() and json_tree() is
as follows:

<blockquote><pre>
CREATE TABLE json_tree(
    key ANY,             -- key for current element relative to its parent

<p>
The "id" column is an integer that identifies a specific JSON element
within the complete JSON string.  The "id" integer is an internal housekeeping
number, the computation of which might change in future releases.  The
only guarantee is that the "id" column will be different for every row.

<p>
The "parent" column is always NULL for json_each().
For json_tree(),
the "parent" column is the "id" integer for the parent of the current
element, or NULL for the top-level JSON element or the element identified
by the path in the second argument.

<p>
The "fullkey" column is a text path that uniquely identifies the current
row element within the original JSON string.  The complete key to the
true top-level element is returned even if an alternative starting point
is provided by the path argument.

<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: