Documentation Source Text

Check-in [854fe12156]
Login

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

Overview
Comment:Still more typo corrections and clarification for the json1 documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 854fe1215688d5b649eac9cfb7535cc5aa3f926e
User & Date: drh 2015-09-11 15:36:36
Context
2015-09-11
16:14
Minor updates to json1 and table-valued function documentation. check-in: af1be7762c user: drh tags: trunk
15:36
Still more typo corrections and clarification for the json1 documentation. check-in: 854fe12156 user: drh tags: trunk
15:23
Fix typos, improve wording, and add more examples to the json1 documentation. check-in: 51f0e6c448 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/json1.in.

127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
...
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
...
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243






244
245
246
247
248
249
250
...
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
<li><p>
Compile the ext/misc/json1.c source file separately using the
additional -DSQLITE_CORE compile-time option.  Then link the
resulting json1.o object file with the application.

<li><p>
Append the ext/misc/json1.c source file onto the end of a standard
[amalgamation|sqlite3.c amalagamation] source file and compile them
together.  No -DSQLITE_CORE compile-time option is needed in this
case since the SQLITE_CORE C preprocessor macro is defined 
by the code in sqlite3.c.
</ol>

<p>
In both cases, one can add the -DSQLITE_ENABLE_JSON1 compile-time
................................................................................
'$' character followed by zero or more instances
of ".<i>objectlabel</i>" or "&#91<i>arrayindex</i>&#93".

<h3>2.3 VALUE arguments</h3>

<p>
For functions that accept "<i>value</i>" arguments (also shown as
"<i>value1</i>" and "<i>value2</i>") that are used to
construct or modify a JSON string, those arguments is usually understood
to be a literal strings and are quoted to become primitive JSON string values
in the result.  Even if the input <i>value</i> strings looks like 
well-formed JSON, they are still interpreted as literal strings in the
result.

<p>
However, if a <i>value</i> argument come from the output of another
json function, then the argument is understood to be actual JSON and
the complete JSON is inserted rather than a quoted string.

<p>
For example, in the following call to json_object(), the first <i>value</i>
argument looks like a well-formed JSON array.  But because it is just an
ordinary SQL text value it is interpreted as a literal string and added to the
result as a quoted string:
................................................................................

<tcl>
jexample {json_object('ex','[52,3.14159]')} {'{"ex":"[52,3.14159]"}'}
</tcl>

<p>
But if the <i>value</i> argument in the outer json_object() call is the
result of another json function like [json()] or [json_array()], then
the value is understood to be actual JSON and is inserted as such:

<tcl>
jexample \
  {json_object('ex',json('[52,3.14159]'))} {'{"ex":[52,3.14159]}'}  \
  {json_object('ex',json_array(52,3.14159))} {'{"ex":[52,3.14159]}'}
</tcl>







<h3>2.4 Compatibility</h3>

<p>
The json1 extension uses the [sqlite3_value_subtype()] and
[sqlite3_result_subtype()] interfaces that were introduced with
SQLite version 3.8.12.  Therefore the json1 extension will not work
in earlier versions of SQLite.
................................................................................
<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 a JSON object 
with a '$.id' field that is a unique indentifier
and a '$.partlist' field that can be 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>







|







 







|
|
|





|
|







 







|








>
>
>
>
>
>







 







|











127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
...
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
...
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
...
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
<li><p>
Compile the ext/misc/json1.c source file separately using the
additional -DSQLITE_CORE compile-time option.  Then link the
resulting json1.o object file with the application.

<li><p>
Append the ext/misc/json1.c source file onto the end of a standard
[amalgamation|sqlite3.c amalgamation] source file and compile them
together.  No -DSQLITE_CORE compile-time option is needed in this
case since the SQLITE_CORE C preprocessor macro is defined 
by the code in sqlite3.c.
</ol>

<p>
In both cases, one can add the -DSQLITE_ENABLE_JSON1 compile-time
................................................................................
'$' character followed by zero or more instances
of ".<i>objectlabel</i>" or "&#91<i>arrayindex</i>&#93".

<h3>2.3 VALUE arguments</h3>

<p>
For functions that accept "<i>value</i>" arguments (also shown as
"<i>value1</i>" and "<i>value2</i>"),
those arguments is usually understood
to be a literal strings that are quoted and becomes JSON string values
in the result.  Even if the input <i>value</i> strings looks like 
well-formed JSON, they are still interpreted as literal strings in the
result.

<p>
However, if a <i>value</i> argument come directly from the result of another
json1 function, then the argument is understood to be actual JSON and
the complete JSON is inserted rather than a quoted string.

<p>
For example, in the following call to json_object(), the first <i>value</i>
argument looks like a well-formed JSON array.  But because it is just an
ordinary SQL text value it is interpreted as a literal string and added to the
result as a quoted string:
................................................................................

<tcl>
jexample {json_object('ex','[52,3.14159]')} {'{"ex":"[52,3.14159]"}'}
</tcl>

<p>
But if the <i>value</i> argument in the outer json_object() call is the
result of another json1 function like [json()] or [json_array()], then
the value is understood to be actual JSON and is inserted as such:

<tcl>
jexample \
  {json_object('ex',json('[52,3.14159]'))} {'{"ex":[52,3.14159]}'}  \
  {json_object('ex',json_array(52,3.14159))} {'{"ex":[52,3.14159]}'}
</tcl>

<p>
To be clear: "<i>json</i>" arguments are always interpreted as JSON
regardless of where the value for that argument comes from.  But
"<i>value</i>" arguments are only interpreted as JSON if those arguments
come directly from another json1 function.

<h3>2.4 Compatibility</h3>

<p>
The json1 extension uses the [sqlite3_value_subtype()] and
[sqlite3_result_subtype()] interfaces that were introduced with
SQLite version 3.8.12.  Therefore the json1 extension will not work
in earlier versions of SQLite.
................................................................................
<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 a JSON object 
with a '$.id' field that is a unique identifier
and a '$.partlist' field that can be 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>