Documentation Source Text

Check-in [08908c99e5]
Login

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

Overview
Comment:Simplify the JSON documentation for better display on mobile.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | mobile-friendly
Files: files | file ages | folders
SHA1: 08908c99e5f1fb1ed915d955b33d48449ff0aece
User & Date: drh 2016-09-01 15:15:50
Context
2016-09-01
16:32
Improved show/hide on the docs.html page. check-in: 910e5ab927 user: drh tags: mobile-friendly
15:15
Simplify the JSON documentation for better display on mobile. check-in: 08908c99e5 user: drh tags: mobile-friendly
14:36
Reduced indentation for fancy-format in mobile. check-in: 47dbb59cad user: drh tags: mobile-friendly
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/json1.in.

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
..
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136

























137
138
139
140
141
142
143
...
248
249
250
251
252
253
254

255
256
257
258
259
260
261
262
...
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
<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>







|


|

|
<





|
<






|
<
|
|


<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







|



|











|

|

|











|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
|







 







|







2
3
4
5
6
7
8
9
10
11
12
13
14

15
16
17
18
19
20

21
22
23
24
25
26
27

28
29
30
31
















32
33
34
35
36
37
38
..
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
...
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
...
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
<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>