Documentation Source Text

Check-in [4793854de9]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improvements and clarification for the json1 documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4793854de964ae9321e9278e23fbdd9351a06e3b
User & Date: drh 2015-09-10 03:43:08
Context
2015-09-10
17:21
Update the json1 documentation to describe the new "path" column of json_tree(). check-in: 5cc19499aa user: drh tags: trunk
03:43
Improvements and clarification for the json1 documentation. check-in: 4793854de9 user: drh tags: trunk
2015-09-09
20:26
Typo fixes in the examples of the json1 document. check-in: e5c50d4f24 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/json1.in.

97
98
99
100
101
102
103

104
105
106
107
108
109
110
111
...
420
421
422
423
424
425
426
427
428


429
430
431
432




433
434
435
436
437
438
439
...
468
469
470
471
472
473
474


475
476

477
478
479
480


481
482
483
484
485
486
487
<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 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 top-level
element (which is presumably an array or object) and does not descend into


substructure.  The json_tree(J) function recursively walks through the
JSON substructure.  Other than the fact that json_tree(J) recursively
descends through JSON substructure, the json_tree(J) and json_each(J)
functions are the same.





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








>
|







 







|
|
>
>
|
<
<
|
>
>
>
>







 







>
>
|
|
>



|
>
>







97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
...
421
422
423
424
425
426
427
428
429
430
431
432


433
434
435
436
437
438
439
440
441
442
443
444
...
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
<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: