Debian Codegraph @

www/json1.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>JSON Functions And Operators</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 JSON Functions And Operators
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div>
126 <div class="fancy-toc1"><a href="#compiling_in_json_support">2. Compiling in JSON Support</a></div>
127 <div class="fancy-toc1"><a href="#interface_overview">3. Interface Overview</a></div>
128 <div class="fancy-toc2"><a href="#json_arguments">3.1. JSON arguments</a></div>
129 <div class="fancy-toc2"><a href="#jsonb">3.2. JSONB</a></div>
130 <div class="fancy-toc3"><a href="#the_jsonb_format">3.2.1. The JSONB format</a></div>
131 <div class="fancy-toc3"><a href="#handling_of_malformed_jsonb">3.2.2. Handling of malformed JSONB</a></div>
132 <div class="fancy-toc2"><a href="#path_arguments">3.3. PATH arguments</a></div>
133 <div class="fancy-toc2"><a href="#value_arguments">3.4. VALUE arguments</a></div>
134 <div class="fancy-toc2"><a href="#compatibility">3.5. Compatibility</a></div>
135 <div class="fancy-toc2"><a href="#json5_extensions">3.6. JSON5 Extensions</a></div>
136 <div class="fancy-toc2"><a href="#performance_considerations">3.7. Performance Considerations</a></div>
137 <div class="fancy-toc2"><a href="#the_json_blob_input_bug">3.8. The JSON BLOB Input Bug</a></div>
138 <div class="fancy-toc2"><a href="#quirks">3.9. Quirks</a></div>
139 <div class="fancy-toc1"><a href="#function_details">4. Function Details</a></div>
140 <div class="fancy-toc2"><a href="#the_json_function">4.1. The json() function</a></div>
141 <div class="fancy-toc2"><a href="#the_jsonb_function">4.2. The jsonb() function</a></div>
142 <div class="fancy-toc2"><a href="#the_json_array_function">4.3. The json_array() function</a></div>
143 <div class="fancy-toc2"><a href="#the_jsonb_array_function">4.4. The jsonb_array() function</a></div>
144 <div class="fancy-toc2"><a href="#the_json_array_insert_and_jsonb_array_insert_functions">4.5. The json_array_insert() and jsonb_array_insert() functions</a></div>
145 <div class="fancy-toc2"><a href="#the_json_array_length_function">4.6. The json_array_length() function</a></div>
146 <div class="fancy-toc2"><a href="#the_json_error_position_function">4.7. The json_error_position() function</a></div>
147 <div class="fancy-toc2"><a href="#the_json_extract_function">4.8. The json_extract() function</a></div>
148 <div class="fancy-toc2"><a href="#the_jsonb_extract_function">4.9. The jsonb_extract() function</a></div>
149 <div class="fancy-toc2"><a href="#the_and_operators">4.10. The -> and ->> operators</a></div>
150 <div class="fancy-toc2"><a href="#the_json_insert_json_replace_and_json_set_functions">4.11. The json_insert(), json_replace, and json_set() functions</a></div>
151 <div class="fancy-toc2"><a href="#the_jsonb_insert_jsonb_replace_and_jsonb_set_functions">4.12. The jsonb_insert(), jsonb_replace, and jsonb_set() functions</a></div>
152 <div class="fancy-toc2"><a href="#the_json_object_function">4.13. The json_object() function</a></div>
153 <div class="fancy-toc2"><a href="#the_jsonb_object_function">4.14. The jsonb_object() function</a></div>
154 <div class="fancy-toc2"><a href="#the_json_patch_function">4.15. The json_patch() function</a></div>
155 <div class="fancy-toc2"><a href="#the_jsonb_patch_function">4.16. The jsonb_patch() function</a></div>
156 <div class="fancy-toc2"><a href="#the_json_pretty_function">4.17. The json_pretty() function</a></div>
157 <div class="fancy-toc2"><a href="#the_json_remove_function">4.18. The json_remove() function</a></div>
158 <div class="fancy-toc2"><a href="#the_jsonb_remove_function">4.19. The jsonb_remove() function</a></div>
159 <div class="fancy-toc2"><a href="#the_json_type_function">4.20. The json_type() function</a></div>
160 <div class="fancy-toc2"><a href="#the_json_valid_function">4.21. The json_valid() function</a></div>
161 <div class="fancy-toc2"><a href="#the_json_quote_function">4.22. The json_quote() function</a></div>
162 <div class="fancy-toc2"><a href="#array_and_object_aggregate_functions">4.23. Array and object aggregate functions</a></div>
163 <div class="fancy-toc2"><a href="#table_valued_functions_for_parsing_json_json_each_jsonb_each_json_tree_and_jsonb_tree_">4.24. Table valued functions for parsing JSON:
164 json_each(), jsonb_each(), json_tree(), and jsonb_tree()</a></div>
165 <div class="fancy-toc3"><a href="#examples_using_json_each_and_json_tree_">4.24.1. Examples using json_each() and json_tree()</a></div>
166 </div>
167 </details>
168 </div>
169
170
171
172
173
174 <h1 id="overview"><span>1. </span>Overview</h1>
175 <p>
176 By default, SQLite supports thirty functions and two operators for
177 dealing with JSON values. There are also four <a href="vtab.html#tabfunc2">table-valued functions</a>
178 that can be used to decompose a JSON string. All of the functions listed
179 below have the <a href="c3ref/c_deterministic.html#sqliteinnocuous">SQLITE_INNOCUOUS</a> and <a href="c3ref/c_deterministic.html#sqlitedeterministic">SQLITE_DETERMINISTIC</a> flags.
180
181 </p><p>
182 There are twenty-eight scalar functions and operators:
183
184 </p><ol>
185 <li value='1'>
186 <a href='#jmini'>json</a>(<i>json</i>)
187 </li>
188
189 <li value='2'>
190 <a href='#jminib'>jsonb</a>(<i>json</i>)
191 </li>
192
193 <li value='3'>
194 <a href='#jarray'>json_array</a>(<i>value1</i>,<i>value2</i>,...)
195 </li>
196
197 <li value='4'>
198 <a href='#jarrayb'>jsonb_array</a>(<i>value1</i>,<i>value2</i>,...)
199 </li>
200
201 <li value='5'>
202 <a href='#jarrayins'>jsonb_array_insert</a>(<i>json</i>,<i>path</i>,<i>value</i>,...)
203 </li>
204
205 <li value='6'>
206 <a href='#jarrayins'>json_array_insert</a>(<i>json</i>,<i>path</i>,<i>value</i>,...)
207 </li>
208
209 <li value='7'>
210 <a href='#jarraylen'>json_array_length</a>(<i>json</i>)<br><a href='#jarraylen'>json_array_length</a>(<i>json</i>,<i>path</i>)
211 </li>
212
213 <li value='8'>
214 <a href='#jerr'>json_error_position</a>(<i>json</i>)
215 </li>
216
217 <li value='9'>
218 <a href='#jex'>json_extract</a>(<i>json</i>,<i>path</i>,...)
219 </li>
220
221 <li value='10'>
222 <a href='#jexb'>jsonb_extract</a>(<i>json</i>,<i>path</i>,...)
223 </li>
224
225 <li value='11'>
226 <i>json</i> <a href='#jptr'>-></a> <i>path</i>
227 </li>
228
229 <li value='12'>
230 <i>json</i> <a href='#jptr'>->></a> <i>path</i>
231 </li>
232
233 <li value='13'>
234 <a href='#jins'>json_insert</a>(<i>json</i>,<i>path</i>,<i>value</i>,...)
235 </li>
236
237 <li value='14'>
238 <a href='#jinsb'>jsonb_insert</a>(<i>json</i>,<i>path</i>,<i>value</i>,...)
239 </li>
240
241 <li value='15'>
242 <a href='#jobj'>json_object</a>(<i>label1</i>,<i>value1</i>,...)
243 </li>
244
245 <li value='16'>
246 <a href='#jobjb'>jsonb_object</a>(<i>label1</i>,<i>value1</i>,...)
247 </li>
248
249 <li value='17'>
250 <a href='#jpatch'>json_patch</a>(<i>json</i>1,json2)
251 </li>
252
253 <li value='18'>
254 <a href='#jpatchb'>jsonb_patch</a>(<i>json</i>1,json2)
255 </li>
256
257 <li value='19'>
258 <a href='#jpretty'>json_pretty</a>(<i>json</i>)
259 </li>
260
261 <li value='20'>
262 <a href='#jrm'>json_remove</a>(<i>json</i>,<i>path</i>,...)
263 </li>
264
265 <li value='21'>
266 <a href='#jrmb'>jsonb_remove</a>(<i>json</i>,<i>path</i>,...)
267 </li>
268
269 <li value='22'>
270 <a href='#jrepl'>json_replace</a>(<i>json</i>,<i>path</i>,<i>value</i>,...)
271 </li>
272
273 <li value='23'>
274 <a href='#jreplb'>jsonb_replace</a>(<i>json</i>,<i>path</i>,<i>value</i>,...)
275 </li>
276
277 <li value='24'>
278 <a href='#jset'>json_set</a>(<i>json</i>,<i>path</i>,<i>value</i>,...)
279 </li>
280
281 <li value='25'>
282 <a href='#jsetb'>jsonb_set</a>(<i>json</i>,<i>path</i>,<i>value</i>,...)
283 </li>
284
285 <li value='26'>
286 <a href='#jtype'>json_type</a>(<i>json</i>)<br><a href='#jtype'>json_type</a>(<i>json</i>,<i>path</i>)
287 </li>
288
289 <li value='27'>
290 <a href='#jvalid'>json_valid</a>(<i>json</i>)<br><a href='#jvalid'>json_valid</a>(<i>json</i>,flags)
291 </li>
292
293 <li value='28'>
294 <a href='#jquote'>json_quote</a>(<i>value</i>)
295 </li>
296
297
298 </ol>
299
300 <p>There are four <a href="lang_aggfunc.html">aggregate SQL functions</a>:
301
302 </p><ol>
303 <li value='29'>
304 <a href='#jgrouparray'>json_group_array</a>(<i>value</i>)
305 </li>
306
307 <li value='30'>
308 <a href='#jgrouparrayb'>jsonb_group_array</a>(<i>value</i>)
309 </li>
310
311 <li value='31'>
312 <a href='#jgroupobject'>json_group_object</a>(<i>label</i>,<i>value</i>)
313 </li>
314
315 <li value='32'>
316 <a href='#jgroupobjectb'>jsonb_group_object</a>(<i>label</i>,<i>value</i>)
317 </li>
318
319
320 </ol>
321
322 <p>The four <a href="vtab.html#tabfunc2">table-valued functions</a> are:
323
324 </p><ol>
325 <li value='33'>
326 <a href='#jeach'>json_each</a>(<i>json</i>)<br><a href='#jeach'>json_each</a>(<i>json</i>,<i>path</i>)
327 </li>
328
329 <li value='34'>
330 <a href='#jtree'>json_tree</a>(<i>json</i>)<br><a href='#jtree'>json_tree</a>(<i>json</i>,<i>path</i>)
331 </li>
332
333 <li value='35'>
334 <a href='#jeach'>jsonb_each</a>(<i>json</i>)<br><a href='#jeach'>jsonb_each</a>(<i>json</i>,<i>path</i>)
335 </li>
336
337 <li value='36'>
338 <a href='#jtree'>jsonb_tree</a>(<i>json</i>)<br><a href='#jtree'>jsonb_tree</a>(<i>json</i>,<i>path</i>)
339 </li>
340
341
342 </ol>
343
344
345
346 <style>
347 .jans {color: #050;}
348 .jex {color: #025;}
349 </style>
350
351
352 <a name="howtocompile"></a>
353
354 <h1 id="compiling_in_json_support"><span>2. </span>Compiling in JSON Support</h1>
355
356 <p>
357 The JSON functions and operators are built into SQLite by default,
358 as of SQLite version 3.38.0 (2022-02-22). They can be omitted
359 by adding the -DSQLITE_OMIT_JSON compile-time option. Prior to
360 version 3.38.0, the JSON functions were an extension that would only
361 be included in builds if the -DSQLITE_ENABLE_JSON1 compile-time option
362 was included. In other words, the JSON functions went from being
363 opt-in with SQLite version 3.37.2 and earlier to opt-out with
364 SQLite version 3.38.0 and later.
365
366 </p><h1 id="interface_overview"><span>3. </span>Interface Overview</h1>
367
368 <p>
369 SQLite stores JSON as ordinary text.
370 Backwards compatibility constraints mean that SQLite is only able to
371 store values that are NULL, integers, floating-point numbers, text,
372 and BLOBs. It is not possible to add a new "JSON" type.
373
374
375 </p><h2 id="json_arguments"><span>3.1. </span>JSON arguments</h2>
376
377 <p>
378 For functions that accept JSON as their first argument, that argument
379 can be a JSON object, array, number, string, or null. SQLite numeric
380 values and NULL values are interpreted as JSON numbers and nulls, respectively.
381 SQLite text values can be understood as JSON objects, arrays, or strings.
382 If an SQLite text value that is not a well-formed JSON object, array, or
383 string is passed into a JSON function, that function will usually throw
384 an error. (Exceptions to this rule are <a href="json1.html#jvalid">json_valid()</a>,
385 <a href="json1.html#jquote">json_quote()</a>, and <a href="json1.html#jerr">json_error_position()</a>.)
386
387 </p><p>
388 These routines understand all
389 <a href="https://www.rfc-editor.org/rfc/rfc8259.txt">rfc-8259 JSON syntax</a>
390 and also <a href="https://spec.json5.org/">JSON5 extensions</a>. JSON text
391 generated by these routines always strictly conforms to the
392 <a href="https://json.org">canonical JSON definition</a> and does not contain any JSON5
393 or other extensions. The ability to read and understand JSON5 was added in
394 version 3.42.0 (2023-05-16).
395 Prior versions of SQLite would only read canonical JSON.
396
397 <a name="jsonbx"></a>
398
399 </p><h2 id="jsonb"><span>3.2. </span>JSONB</h2>
400
401 <p>
402 Beginning with version 3.45.0 (2024-01-15), SQLite allows its
403 internal "parse tree" representation of JSON to be stored on disk,
404 as a BLOB, in a format that we call "JSONB". By storing SQLite's internal
405 binary representation of JSON directly in the database, applications
406 can bypass the overhead of parsing and rendering JSON when reading and
407 updating JSON values. The internal JSONB format also uses slightly
408 less disk space than text JSON.
409
410 </p><p>
411 Any SQL function parameter that accepts text JSON as an input will also
412 accept a BLOB in the JSONB format. The function will operate the
413 same in either case, except that it will run faster when
414 the input is JSONB, since it does not need to run the JSON parser.
415
416 </p><p>
417 Most SQL functions that return JSON text have a corresponding function
418 that returns the equivalent JSONB. The functions that return JSON
419 in the text format begin with "<tt>json_</tt>" and functions that
420 return the binary JSONB format begin with "<tt>jsonb_</tt>".
421
422 </p><h3 id="the_jsonb_format"><span>3.2.1. </span>The JSONB format</h3>
423
424 <p>
425 JSONB is a binary representation of JSON used by SQLite and
426 is intended for internal use by SQLite only. Applications
427 should not use JSONB outside of SQLite nor try to reverse-engineer the
428 JSONB format.
429
430 </p><p>
431 The "JSONB" name is inspired by <a href="https://postgresql.org">PostgreSQL</a>, but the
432 on-disk format for SQLite's JSONB is not the same as PostgreSQL's.
433 The two formats have the same name, but are not binary compatible.
434 The PostgreSQL JSONB format claims to offer O(1)
435 lookup of elements in objects and arrays. SQLite's JSONB format makes no
436 such claim. SQLite's JSONB has O(N) time complexity for
437 most operations in SQLite, just like text JSON. The advantage of JSONB in
438 SQLite is that it is smaller and faster than text JSON - potentially several
439 times faster. There is space in the
440 on-disk JSONB format to add enhancements and future versions of SQLite might
441 include options to provide O(1) lookup of elements in JSONB, but no such
442 capability is currently available.
443
444 </p><h3 id="handling_of_malformed_jsonb"><span>3.2.2. </span>Handling of malformed JSONB</h3>
445
446 <p>
447 The JSONB that is generated by SQLite will always be well-formed. If you
448 follow recommended practice and
449 treat JSONB as an opaque BLOB, then you will not have any problems. But
450 JSONB is just a BLOB, so a mischievous programmer could devise BLOBs
451 that are similar to JSONB but that are technically malformed. When
452 misformatted JSONB is feed into JSON functions, any of the following
453 might happen:
454
455 </p><ul>
456 <li><p>
457 The SQL statement might abort with a "malformed JSON" error.
458
459 </p></li><li><p>
460 The correct answer might be returned, if the malformed parts of
461 the JSONB blob do not impact the answer.
462
463 </p></li><li><p>
464 A goofy or nonsensical answer might be returned.
465 </p></li></ul>
466
467 <p>
468 The way in which SQLite handles invalid JSONB might change
469 from one version of SQLite to the next. The system follows
470 the garbage-in/garbage-out rule: If you feed the JSON functions invalid
471 JSONB, you get back an invalid answer. If you are in doubt about the
472 validity of our JSONB, use the <a href="json1.html#jvalid">json_valid()</a> function to verify it.
473
474 </p><p>
475 We do make this one promise:
476 Malformed JSONB will never cause a memory
477 error or similar problem that might lead to a vulnerability.
478 Invalid JSONB might lead to crazy answers,
479 or it might cause queries to abort, but it won't cause a crash.
480
481 <a name="jsonpath"></a>
482
483 </p><h2 id="path_arguments"><span>3.3. </span>PATH arguments</h2>
484
485 <p>
486 For functions that accept PATH arguments, that PATH must be well-formed or
487 else the function will throw an error.
488 A well-formed PATH is a text value that begins with exactly one
489 '$' character followed by zero or more instances
490 of ".<i>objectlabel</i>" or "&#91;<i>arrayindex</i>&#93;".
491
492 </p><p>
493 The <i>arrayindex</i> is usually a non-negative integer <i>N</i>. In
494 that case, the array element selected is the <i>N</i>-th element
495 of the array, starting with zero on the left.
496 The <i>arrayindex</i> can also be of the form "<b>#-</b><i>N</i>"
497 in which case the element selected is the <i>N</i>-th from the
498 right. The last element of the array is "<b>#-1</b>". Think of
499 the "#" characters as the "number of elements in the array". Then
500 the expression "#-1" evaluates to the integer that corresponds to
501 the last entry in the array. It is sometimes useful for the array
502 index to be just the <b>#</b> character, for example when appending
503 a value to an existing JSON array:
504
505 <ul>
506 <li><span class='jex'>json_set('[0,1,2]','$[#]','new')</span>
507 <span class='jans'>&rarr; '[0,1,2,"new"]'</span></li>
508
509 </ul>
510
511
512 <a name="varg"></a>
513
514 </p><h2 id="value_arguments"><span>3.4. </span>VALUE arguments</h2>
515
516 <p>
517 For functions that accept "<i>value</i>" arguments (also shown as
518 "<i>value1</i>" and "<i>value2</i>"),
519 those arguments are usually understood
520 to be literal strings that are quoted and become JSON string values
521 in the result. Even if the input <i>value</i> strings look like
522 well-formed JSON, they are still interpreted as literal strings in the
523 result.
524
525 </p><p>
526 However, if a <i>value</i> argument comes directly from the result of another
527 JSON function or from <a href="json1.html#jptr">the -> operator</a> (but not <a href="json1.html#jptr">the ->> operator</a>),
528 then the argument is understood to be actual JSON and
529 the complete JSON is inserted rather than a quoted string.
530
531 </p><p>
532 For example, in the following call to json_object(), the <i>value</i>
533 argument looks like a well-formed JSON array. However, because it is just
534 ordinary SQL text, it is interpreted as a literal string and added to the
535 result as a quoted string:
536
537 <ul>
538 <li><span class='jex'>json_object('ex','[52,3.14159]')</span>
539 <span class='jans'>&rarr; '{"ex":"[52,3.14159]"}'</span></li>
540
541 <li><span class='jex'>json_object('ex',('[52,3.14159]'->>'$'))</span>
542 <span class='jans'>&rarr; '{"ex":"[52,3.14159]"}'</span></li>
543
544 </ul>
545
546
547 </p><p>
548 But if the <i>value</i> argument in the outer json_object() call is the
549 result of another JSON function like <a href="json1.html#jmini">json()</a> or <a href="json1.html#jarray">json_array()</a>, then
550 the value is understood to be actual JSON and is inserted as such:
551
552 <ul>
553 <li><span class='jex'>json_object('ex',json('[52,3.14159]'))</span>
554 <span class='jans'>&rarr; '{"ex":[52,3.14159]}'</span></li>
555
556 <li><span class='jex'>json_object('ex',json_array(52,3.14159))</span>
557 <span class='jans'>&rarr; '{"ex":[52,3.14159]}'</span></li>
558
559 <li><span class='jex'>json_object('ex','[52,3.14159]'->'$')</span>
560 <span class='jans'>&rarr; '{"ex":[52,3.14159]}'</span></li>
561
562 </ul>
563
564
565 </p><p>
566 To be clear: "<i>json</i>" arguments are always interpreted as JSON
567 regardless of where the value for that argument comes from. But
568 "<i>value</i>" arguments are only interpreted as JSON if those arguments
569 come directly from another JSON function or <a href="json1.html#jptr">the -> operator</a>.
570
571 </p><p>
572 Within JSON value arguments interpreted as JSON strings, Unicode escape
573 sequences are not treated as equivalent to the characters or escaped
574 control characters represented by the expressed Unicode code point.
575 Such escape sequences are not translated or specially treated; they
576 are treated as plain text by SQLite's JSON functions.
577
578 </p><h2 id="compatibility"><span>3.5. </span>Compatibility</h2>
579
580 <p>
581 The current implementation of this JSON library uses a recursive descent
582 parser. In order to avoid using excess stack space, any JSON input that has
583 more than 1000 levels of nesting is considered invalid. Limits on nesting
584 depth are allowed for compatible implementations of JSON by
585 <a href="https://tools.ietf.org/html/rfc8259#section-9">RFC-8259 section 9</a>.
586
587 <a name="json5"></a>
588
589 </p><h2 id="json5_extensions"><span>3.6. </span>JSON5 Extensions</h2>
590
591 <p>
592 Beginning in version 3.42.0 (2023-05-16), these routines will
593 read and interpret input JSON text that includes
594 <a href="https://spec.json5.org/">JSON5</a> extensions. However, JSON text generated
595 by these routines will always be strictly conforming to the
596 <a href="https://json.org">canonical definition of JSON</a>.
597
598 </p><p>
599 Here is a synopsis of JSON5 extensions (adapted from the
600 <a href="https://spec.json5.org/#introduction">JSON5 specification</a>):
601
602 </p><ul>
603 <li> Object keys may be unquoted identifiers.
604 </li><li> Objects may have a single trailing comma.
605 </li><li> Arrays may have a single trailing comma.
606 </li><li> Strings may be single quoted.
607 </li><li> Strings may span multiple lines by escaping new line characters.
608 </li><li> Strings may include new character escapes.
609 </li><li> Numbers may be hexadecimal.
610 </li><li> Numbers may have a leading or trailing decimal point.
611 </li><li> Numbers may be "Infinity", "-Infinity", and "NaN".
612 </li><li> Numbers may begin with an explicit plus sign.
613 </li><li> Single (//...) and multi-line (/*...*/) comments are allowed.
614 </li><li> Additional white space characters are allowed.
615 </li></ul>
616
617 <p>
618 To convert string X from JSON5 into canonical JSON, invoke
619 "<a href="json1.html#jmini">json(X)</a>". The output of the "<a href="json1.html#jmini">json()</a>" function will be canonical
620 JSON regardless of any JSON5 extensions that are present in the input.
621 For backwards compatibility, the <a href="json1.html#jvalid">json_valid(X)</a> function without a
622 "flags" argument continues
623 to report false for inputs that are not canonical JSON, even if the
624 input is JSON5 that the function is able to understand. To determine
625 whether or not an input string is valid JSON5, include the 0x02 bit
626 in the "flags" argument to json_valid: "<tt>json_valid(X,2)</tt>".
627
628 </p><p>
629 These routines understand all of JSON5, plus a little more.
630 SQLite extends the JSON5 syntax in these two ways:
631
632 </p><ol>
633 <li><p>
634 Strict JSON5 requires that
635 unquoted object keys must be ECMAScript 5.1 IdentifierNames. But large
636 unicode tables and lots of code is required in order to determine whether or
637 not a key is an ECMAScript 5.1 IdentifierName. For this reason,
638 SQLite allows object keys to include any unicode characters
639 greater than U+007f that are not whitespace characters. This relaxed
640 definition of "identifier" greatly simplifies the implementation and allows
641 the JSON parser to be smaller and run faster.
642
643 </p></li><li><p>
644 JSON5 allows floating-point infinities to be expressed as
645 "<tt>Infinity</tt>", "<tt>-Infinity</tt>", or "<tt>+Infinity</tt>"
646 in exactly that case - the initial "I" is capitalized and all other
647 characters are lower case. SQLite also allows the abbreviation "<tt>Inf</tt>"
648 to be used in place of "<tt>Infinity</tt>" and it allows both keywords
649 to appear in any combination of upper and lower case letters.
650 Similarly,
651 JSON5 allows "NaN" for not-a-number. SQLite extends this to also allow
652 "QNaN" and "SNaN" in any combination of upper and lower case letters.
653 Note that SQLite interprets NaN, QNaN, and SNaN as just an alternative
654 spellings for "null".
655 This extension has been added because (we are told) there exists a lot
656 of JSON in the wild that includes these non-standard representations
657 for infinity and not-a-number.
658 </p></li></ol>
659
660
661 <h2 id="performance_considerations"><span>3.7. </span>Performance Considerations</h2>
662
663 <p>
664 Most JSON functions do their internal processing using JSONB. So if the
665 input is text, they first must translate the input text into JSONB.
666 If the input is already in the JSONB format, no translation is needed,
667 that step can be skipped, and performance is faster.
668
669 </p><p>
670 For that reason,
671 when an argument to one JSON function is supplied by another
672 JSON function, it is usually more efficient to use the "<tt>jsonb_</tt>"
673 variant for the function used as the argument.
674
675 </p><ul>
676 <li>
677 <tt>... json_insert(A,'$.b',json(C)) ...</tt>
678 &nbsp; &larr; Less efficient.
679 </li><li>
680 <tt>... json_insert(A,'$.b',jsonb(C)) ...</tt>
681 &nbsp; &larr; More efficient.
682 </li></ul>
683
684
685 <p>
686 The <a href="json1.html#jgroupobjectb">aggregate JSON SQL functions</a> are an exception to this rule. Those
687 functions all do their processing using text instead of JSONB. So for the
688 aggregate JSON SQL functions, it is more efficient for the arguments
689 to be supplied using "<tt>json_</tt>" functions than "<tt>jsonb_</tt>"
690 functions.
691
692 </p><ul>
693 <li>
694 <tt>... json_group_array(json(A))) ...</tt>
695 &nbsp; &larr; More efficient.
696 </li><li>
697 <tt>... json_group_array(jsonb(A))) ...</tt>
698 &nbsp; &larr; Less efficient.
699 </li></ul>
700
701 <a name="jblobbug"></a>
702
703 <h2 id="the_json_blob_input_bug"><span>3.8. </span>The JSON BLOB Input Bug</h2>
704
705 <p>If a JSON input is a BLOB that is not JSONB and that looks like
706 text JSON when cast to text, then it is accepted as text JSON.
707 This is actually a long-standing bug in the original implementation
708 that the SQLite developers were unaware of. The documentation stated
709 that a BLOB input to a JSON function should raise an error. But in the
710 actual implementation, the input would be accepted as long
711 as the BLOB content was a valid JSON string in the text encoding of
712 the database.
713
714 </p><p>This JSON BLOB input bug was accidentally fixed when the JSON routines
715 were reimplemented for the 3.45.0 release (2024-01-15).
716 That caused breakage in applications that had come to depend on the old
717 behavior. (In defense of those applications: they were often lured into
718 using BLOBs as JSON by the <a href="cli.html#fileio">readfile()</a> SQL function
719 available in the <a href="cli.html">CLI</a>. Readfile() was used to read JSON from disk files,
720 but readfile() returns a BLOB. And that worked for them, so why not just
721 do it?)
722
723 </p><p>For backwards bug-compatibility,
724 the (formerly incorrect) legacy behavior of interpreting BLOBs as text JSON
725 if no other interpretation works
726 is hereby documented and is officially supported in
727 version 3.45.1 (2024-01-30) and all subsequent releases.
728 Beware, however, that there exist BLOBs which are both valid JSONB and
729 which are valid JSON after being cast to text. For example, the
730 BLOB <tt>x'33343536'</tt> is valid JSONB, specifically the integer 456,
731 and when cast to text is the valid JSON integer literal 3456. Therefore,
732 if you have legacy databases in which you store JSON text as a BLOB
733 and you want to continue using those database with SQLite 3.45.0 or later,
734 you will do well to run an UPDATE on that legacy JSON text so that it
735 is actually stored as TEXT and not as a BLOB.
736
737 <a name="jquirk"></a>
738
739 </p><h2 id="quirks"><span>3.9. </span>Quirks</h2>
740
741 <p>
742 SQLite uses U+0000 as a string terminator. If you embed that character
743 in the middle of an object label, then PATH comparisons will only look
744 at the prefix that comes before the U+0000.
745
746 </p><p>
747 SQLite does not strictly enforce JSON string syntax in PATHs. If you
748 use <a href="json1.html#jset">json_set()</a> or similar to add new elements to an object, and the
749 PATH argument contains backslash escapes that are not valid in JSON,
750 SQLite does not try to prevent you from doing so and you can end up
751 with invalid JSON.
752
753 </p><h1 id="function_details"><span>4. </span>Function Details</h1>
754
755 <p>The following sections provide additional detail on the operation of
756 the various JSON functions and operators:
757
758 <a name="jmini"></a>
759
760 </p><h2 id="the_json_function"><span>4.1. </span>The json() function</h2>
761
762 <p>The json(X) function verifies that its argument X is a valid
763 JSON string or JSONB blob and returns a minified version of that JSON string
764 with all unnecessary whitespace removed. If X is not a well-formed
765 JSON string or JSONB blob, then this routine throws an error.
766
767 </p><p>If the input is JSON5 text, then it is converted into canonical
768 RFC-8259 text prior to being returned.
769
770 </p><p>If the argument X to json(X) contains JSON objects with duplicate
771 labels, then it is undefined whether or not the duplicates are
772 preserved. The current implementation preserves duplicates.
773 However, future enhancements
774 to this routine may choose to silently remove duplicates.
775
776 </p><p>
777 Example:
778
779 <ul>
780 <li><span class='jex'>json(' { "this" : "is", "a": [ "test" ] } ')</span>
781 <span class='jans'>&rarr; '{"this":"is","a":["test"]}'</span></li>
782
783 </ul>
784
785
786 <a name="jminib"></a>
787
788 </p><h2 id="the_jsonb_function"><span>4.2. </span>The jsonb() function</h2>
789
790 <p>The jsonb(X) function returns the binary JSONB representation
791 of the JSON provided as argument X. An error is raised if X is
792 TEXT that does not have valid JSON syntax.
793
794 </p><p>If X is a BLOB and appears to be JSONB,
795 then this routine simply returns a copy of X.
796 Only the outer-most element of the JSONB input is examined, however.
797 The deep structure of the JSONB is not validated.
798
799 <a name="jarray"></a>
800
801 </p><h2 id="the_json_array_function"><span>4.3. </span>The json_array() function</h2>
802
803 <p>The json_array() SQL function accepts zero or more arguments and
804 returns a well-formed JSON array that is composed from those arguments.
805 If any argument to json_array() is a BLOB then an error is thrown.
806
807 </p><p>An argument with SQL type TEXT is normally converted into a quoted
808 JSON string. However, if the argument is the output from another json1
809 function, then it is stored as JSON. This allows calls to json_array()
810 and <a href="json1.html#jobj">json_object()</a> to be nested. The <a href="json1.html#jmini">json()</a> function can also
811 be used to force strings to be recognized as JSON.
812
813 </p><p>Examples:
814
815 <ul>
816 <li><span class='jex'>json_array(1,2,'3',4)</span>
817 <span class='jans'>&rarr; '[1,2,"3",4]'</span></li>
818
819 <li><span class='jex'>json_array('[1,2]')</span>
820 <span class='jans'>&rarr; '["[1,2]"]'</span></li>
821
822 <li><span class='jex'>json_array(json_array(1,2))</span>
823 <span class='jans'>&rarr; '[[1,2]]'</span></li>
824
825 <li><span class='jex'>json_array(1,null,'3','[4,5]','{"six":7.7}')</span>
826 <span class='jans'>&rarr; '[1,null,"3","[4,5]","{\"six\":7.7}"]'</span></li>
827
828 <li><span class='jex'>json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}'))</span>
829 <span class='jans'>&rarr; '[1,null,"3",[4,5],{"six":7.7}]'</span></li>
830
831 </ul>
832
833
834
835 <a name="jarrayb"></a>
836
837 </p><h2 id="the_jsonb_array_function"><span>4.4. </span>The jsonb_array() function</h2>
838
839 <p>The jsonb_array() SQL function works just like the <a href="json1.html#jarray">json_array()</a>
840 function except that it returns the constructed JSON array in the
841 SQLite's private JSONB format rather than in the standard
842 RFC 8259 text format.
843
844
845
846 <a name="jarrayins"></a>
847
848 </p><h2 id="the_json_array_insert_and_jsonb_array_insert_functions"><span>4.5. </span>The json_array_insert() and jsonb_array_insert() functions</h2>
849
850 <p>The json_array_insert() and jsonb_array_insert() SQL functions
851 operate similarly to the <a href="json1.html#jrepl">json_replace()</a> and <a href="json1.html#jreplb">jsonb_replace</a> functions,
852 with just two differences:
853
854 </p><ol>
855 <li><p>
856 The PATH arguments must reference an element of an array.
857 If any PATH argument does not end with an array element
858 reference (if a PATH does not end with "&#91;&#8230;&#93;")
859 then an error is raised.
860
861 </p></li><li><p>
862 Instead of overwriting existing elements in the array,
863 the array is enlarged and the new elements are inserted at
864 the position specified by the PATH argument.
865 </p></li></ol>
866
867 <p>
868 As with json_replace() and jsonb_replace(), the PATH and VALUE
869 arguments can be repeated multiple times. The modified JSON
870 is returned, either in a text format for json_array_insert() or
871 in the binary BLOB format for jsonb_array_insert().
872
873 </p><p>Examples:
874
875 <ul>
876 <li><span class='jex'>json_array_insert('[1,2,3]','$[1]','new')</span>
877 <span class='jans'>&rarr; [1,"new",2,3]</span></li>
878
879 <li><span class='jex'>json_array_insert('{"a":[1,2,3]}', '$.a[0]','new')</span>
880 <span class='jans'>&rarr; {"a":["new",1,2,3]}</span></li>
881
882 </ul>
883
884
885
886 <a name="jarraylen"></a>
887
888 </p><h2 id="the_json_array_length_function"><span>4.6. </span>The json_array_length() function</h2>
889
890 <p>The json_array_length(X) function returns the number of elements
891 in the JSON array X, or 0 if X is some kind of JSON value other
892 than an array. The json_array_length(X,P) locates the array at path P
893 within X and returns the length of that array, or 0 if path P locates
894 an element in X that is not a JSON array, and NULL if path P does not
895 locate any element of X. Errors are thrown if either X is not
896 well-formed JSON or if P is not a well-formed path.
897
898 </p><p>Examples:
899
900 <ul>
901 <li><span class='jex'>json_array_length('[1,2,3,4]')</span>
902 <span class='jans'>&rarr; 4</span></li>
903
904 <li><span class='jex'>json_array_length('[1,2,3,4]', '$')</span>
905 <span class='jans'>&rarr; 4</span></li>
906
907 <li><span class='jex'>json_array_length('[1,2,3,4]', '$[2]')</span>
908 <span class='jans'>&rarr; 0</span></li>
909
910 <li><span class='jex'>json_array_length('{"one":[1,2,3]}')</span>
911 <span class='jans'>&rarr; 0</span></li>
912
913 <li><span class='jex'>json_array_length('{"one":[1,2,3]}', '$.one')</span>
914 <span class='jans'>&rarr; 3</span></li>
915
916 <li><span class='jex'>json_array_length('{"one":[1,2,3]}', '$.two')</span>
917 <span class='jans'>&rarr; NULL</span></li>
918
919 </ul>
920
921
922
923 <a name="jerr"></a>
924
925 </p><h2 id="the_json_error_position_function"><span>4.7. </span>The json_error_position() function</h2>
926
927 <p>The json_error_position(X) function returns 0 if the input X is a
928 well-formed JSON or JSON5 string. If the input X contains one or more
929 syntax errors, then this function returns the character position of the
930 first syntax error. The left-most character is position 1.
931
932 </p><p>If the input X is a BLOB, then this routine returns 0 if X is
933 a well-formed JSONB blob. If the return value is positive, then it
934 represents the <i>approximate</i> 1-based position in the BLOB of the
935 first detected error.
936
937 </p><p>
938 The json_error_position() function was added with
939 SQLite version 3.42.0 (2023-05-16).
940
941
942 <a name="jex"></a>
943
944 </p><h2 id="the_json_extract_function"><span>4.8. </span>The json_extract() function</h2>
945
946 <p>The json_extract(X,P1,P2,...) extracts and returns one or more
947 values from the
948 well-formed JSON at X. If only a single path P1 is provided, then the
949 SQL datatype of the result is NULL for a JSON null, INTEGER or REAL
950 for a JSON numeric value, an INTEGER zero for a JSON false value,
951 an INTEGER one for a JSON true value, the dequoted text for a
952 JSON string value, and a text representation for JSON object and array values.
953 If there are multiple path arguments (P1, P2, and so forth) then this
954 routine returns SQLite text which is a well-formed JSON array holding
955 the various values.
956
957 </p><p>Examples:
958
959 <ul>
960 <li><span class='jex'>json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$')</span>
961 <span class='jans'>&rarr; '{"a":2,"c":[4,5,{"f":7}]}'</span></li>
962
963 <li><span class='jex'>json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c')</span>
964 <span class='jans'>&rarr; '[4,5,{"f":7}]'</span></li>
965
966 <li><span class='jex'>json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]')</span>
967 <span class='jans'>&rarr; '{"f":7}'</span></li>
968
969 <li><span class='jex'>json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f')</span>
970 <span class='jans'>&rarr; 7</span></li>
971
972 <li><span class='jex'>json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a')</span>
973 <span class='jans'>&rarr; '[[4,5],2]'</span></li>
974
975 <li><span class='jex'>json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]')</span>
976 <span class='jans'>&rarr; 5</span></li>
977
978 <li><span class='jex'>json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x')</span>
979 <span class='jans'>&rarr; NULL</span></li>
980
981 <li><span class='jex'>json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a')</span>
982 <span class='jans'>&rarr; '[null,2]'</span></li>
983
984 <li><span class='jex'>json_extract('{"a":"xyz"}', '$.a')</span>
985 <span class='jans'>&rarr; 'xyz'</span></li>
986
987 <li><span class='jex'>json_extract('{"a":null}', '$.a')</span>
988 <span class='jans'>&rarr; NULL</span></li>
989
990 </ul>
991
992
993 </p><p>There is a subtle incompatibility between the json_extract() function
994 in SQLite and the json_extract() function in MySQL. The MySQL version
995 of json_extract() always returns JSON. The SQLite version of
996 json_extract() only returns JSON if there are two or more PATH arguments
997 (because the result is then a JSON array) or if the single PATH argument
998 references an array or object. In SQLite, if json_extract() has only
999 a single PATH argument and that PATH references a JSON null or a string
1000 or a numeric value, then json_extract() returns the corresponding SQL
1001 NULL, TEXT, INTEGER, or REAL value.
1002
1003 </p><p>The difference between MySQL json_extract() and SQLite json_extract()
1004 really only stands out when accessing individual values within the JSON
1005 that are strings or NULLs. The following table demonstrates the difference:
1006
1007 </p><center>
1008 <table border="1" cellpadding="3" cellspacing="0">
1009 <tr><th>Operation</th><th>SQLite Result</th><th>MySQL Result
1010 </th></tr><tr><td>json_extract('{"a":null,"b":"xyz"}','$.a')</td><td>NULL</td><td>'null'
1011 </td></tr><tr><td>json_extract('{"a":null,"b":"xyz"}','$.b')</td><td>'xyz'</td><td>'"xyz"'
1012 </td></tr></table></center>
1013
1014 <a name="jexb"></a>
1015
1016 <h2 id="the_jsonb_extract_function"><span>4.9. </span>The jsonb_extract() function</h2>
1017
1018 <p>
1019 The jsonb_extract() function works the same as the <a href="json1.html#jex">json_extract()</a> function,
1020 except in cases where json_extract() would normally return a text
1021 JSON array object, this routine returns the array or object in the
1022 JSONB format. For the common case where a text, numeric, null, or
1023 boolean JSON element is returned, this routine works exactly the same
1024 as json_extract().
1025
1026
1027 <a name="jptr"></a>
1028
1029 </p><h2 id="the_and_operators"><span>4.10. </span>The -> and ->> operators</h2>
1030
1031 <p>Beginning with SQLite version 3.38.0 (2022-02-22), the ->
1032 and ->> operators are available for extracting subcomponents of JSON.
1033 The SQLite implementation of -> and ->> strives to be
1034 compatible with both MySQL and PostgreSQL.
1035 The -> and ->> operators take a JSON string or JSONB blob
1036 as their left operand and a PATH expression or object field
1037 label or array index as their right operand. The -> operator
1038 returns a text JSON representation of the selected subcomponent or
1039 NULL if that subcomponent does not exist. The ->> operator returns
1040 an SQL TEXT, INTEGER, REAL, or NULL value that represents the selected
1041 subcomponent, or NULL if the subcomponent does not exist.
1042
1043 </p><p>Both the -> and ->> operators select the same subcomponent
1044 of the JSON to their left. The difference is that -> always returns a
1045 JSON representation of that subcomponent and the ->> operator always
1046 returns an SQL representation of that subcomponent. Thus, these operators
1047 are subtly different from a two-argument <a href="json1.html#jex">json_extract()</a> function call.
1048 A call to json_extract() with two arguments will return a JSON representation
1049 of the subcomponent if and only if the subcomponent is a JSON array or
1050 object, and will return an SQL representation of the subcomponent if the
1051 subcomponent is a JSON null, string, or numeric value.
1052
1053 </p><p>When the -> operator returns JSON, it always returns the
1054 RFC 8565 text representation of that JSON, not JSONB. Use the
1055 <a href="json1.html#jexb">jsonb_extract()</a> function if you need a subcomponent in the
1056 JSONB format.
1057
1058 </p><p>The right-hand operand to the -> and ->> operators can
1059 be a well-formed JSON path expression. This is the form used by MySQL.
1060 For compatibility with PostgreSQL,
1061 the -> and ->> operators also accept an alphanumeric text object label
1062 or integer array index as their right-hand operand.
1063 If the right operand is an alphanumeric text
1064 label X, then it is interpreted as the JSON path '$.X'. If the right
1065 operand is an integer value N, then it is interpreted as the JSON path '$[N]'
1066 if it is non-negative. Or if N is negative integer with value -K, then it
1067 is interpreted like the JSON path '$&#91;#-K&#93;'. In other words, the
1068 indexing begins at the end of the array and moves back toward the front.
1069 Negative values for N are only supported in SQLite versions 3.47.0
1070 (2024-10-21) and later.
1071
1072
1073 </p><p>Examples:
1074
1075 <ul>
1076 <li><span class='jex'>'{"a":2,"c":[4,5,{"f":7}]}' -> '$'</span>
1077 <span class='jans'>&rarr; '{"a":2,"c":[4,5,{"f":7}]}'</span></li>
1078
1079 <li><span class='jex'>'{"a":2,"c":[4,5,{"f":7}]}' -> '$.c'</span>
1080 <span class='jans'>&rarr; '[4,5,{"f":7}]'</span></li>
1081
1082 <li><span class='jex'>'{"a":2,"c":[4,5,{"f":7}]}' -> 'c'</span>
1083 <span class='jans'>&rarr; '[4,5,{"f":7}]'</span></li>
1084
1085 <li><span class='jex'>'{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2]'</span>
1086 <span class='jans'>&rarr; '{"f":7}'</span></li>
1087
1088 <li><span class='jex'>'{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2].f'</span>
1089 <span class='jans'>&rarr; '7'</span></li>
1090
1091 <li><span class='jex'>'{"a":2,"c":[4,5,{"f":7}]}' ->> '$.c[2].f'</span>
1092 <span class='jans'>&rarr; 7</span></li>
1093
1094 <li><span class='jex'>'{"a":2,"c":[4,5,{"f":7}]}' -> 'c' -> 2 ->> 'f'</span>
1095 <span class='jans'>&rarr; 7</span></li>
1096
1097 <li><span class='jex'>'{"a":2,"c":[4,5],"f":7}' -> '$.c[#-1]'</span>
1098 <span class='jans'>&rarr; '5'</span></li>
1099
1100 <li><span class='jex'>'{"a":2,"c":[4,5,{"f":7}]}' -> '$.x'</span>
1101 <span class='jans'>&rarr; NULL</span></li>
1102
1103 <li><span class='jex'>'[11,22,33,44]' -> 3</span>
1104 <span class='jans'>&rarr; '44'</span></li>
1105
1106 <li><span class='jex'>'[11,22,33,44]' ->> 3</span>
1107 <span class='jans'>&rarr; 44</span></li>
1108
1109 <li><span class='jex'>'{"a":"xyz"}' -> '$.a'</span>
1110 <span class='jans'>&rarr; '"xyz"'</span></li>
1111
1112 <li><span class='jex'>'{"a":"xyz"}' ->> '$.a'</span>
1113 <span class='jans'>&rarr; 'xyz'</span></li>
1114
1115 <li><span class='jex'>'{"a":null}' -> '$.a'</span>
1116 <span class='jans'>&rarr; 'null'</span></li>
1117
1118 <li><span class='jex'>'{"a":null}' ->> '$.a'</span>
1119 <span class='jans'>&rarr; NULL</span></li>
1120
1121 </ul>
1122
1123
1124
1125
1126
1127 <a name="jins"></a>
1128
1129 <a name="jrepl"></a>
1130
1131 <a name="jset"></a>
1132
1133 </p><h2 id="the_json_insert_json_replace_and_json_set_functions"><span>4.11. </span>The json_insert(), json_replace, and json_set() functions</h2>
1134
1135 <p>The json_insert(), json_replace, and json_set() functions all take
1136 a single JSON value as their first argument followed by zero or more
1137 pairs of path and value arguments, and return a new JSON string formed
1138 by updating the input JSON by the path/value pairs. The functions
1139 differ only in how they deal with creating new values and overwriting
1140 preexisting values.
1141
1142 </p><center>
1143 <table border="1" cellpadding="3" cellspacing="0">
1144 <tr>
1145 <th>Function</th><th>Overwrite if already exists?</th><th>Create if does not exist?
1146 </th></tr><tr>
1147 <td>json_insert()</td><td align="center">No</td><td align="center">Yes
1148 </td></tr><tr>
1149 <td>json_replace()</td><td align="center">Yes</td><td align="center">No
1150 </td></tr><tr>
1151 <td>json_set()</td><td align="center">Yes</td><td align="center">Yes
1152 </td></tr></table></center>
1153
1154 <p>The json_insert(), json_replace(), and json_set() functions always
1155 take an odd number of arguments. The first argument is always the original
1156 JSON to be edited. Subsequent arguments occur in pairs with the first
1157 element of each pair being a path and the second element being the value
1158 to insert or replace or set on that path.
1159
1160 </p><p>Edits occur sequentially from left to right. Changes caused by
1161 prior edits can affect the path search for subsequent edits.
1162
1163 </p><p>If the value of a path/value pair is an SQLite TEXT value, then it
1164 is normally inserted as a quoted JSON string, even if the string looks
1165 like valid JSON. However, if the value is the result of another
1166 json function (such as <a href="json1.html#jmini">json()</a> or <a href="json1.html#jarray">json_array()</a> or <a href="json1.html#jobj">json_object()</a>)
1167 or if it is the result of <a href="json1.html#jptr">the -> operator</a>,
1168 then it is interpreted as JSON and is inserted as JSON retaining all
1169 of its substructure. Values that are the result of <a href="json1.html#jptr">the ->> operator</a>
1170 are always interpreted as TEXT and are inserted as a JSON string even
1171 if they look like valid JSON.
1172
1173 </p><p>These routines throw an error if the first JSON argument is not
1174 well-formed or if any PATH argument is not well-formed or if any
1175 argument is a BLOB.
1176
1177 </p><p>To append an element onto the end of an array, use json_insert()
1178 with an array index of "#". Examples:
1179
1180 <ul>
1181 <li><span class='jex'>json_insert('[1,2,3,4]','$[#]',99)</span>
1182 <span class='jans'>&rarr; '[1,2,3,4,99]'</span></li>
1183
1184 <li><span class='jex'>json_insert('[1,[2,3],4]','$[1][#]',99)</span>
1185 <span class='jans'>&rarr; '[1,[2,3,99],4]'</span></li>
1186
1187 </ul>
1188
1189
1190 </p><p>Use the <a href="json1.html#jarrayins">json_array_insert()</a> function to insert elements
1191 at the beginning or in the middle of an array.
1192
1193 </p><p>Other examples:
1194
1195 <ul>
1196 <li><span class='jex'>json_insert('{"a":2,"c":4}', '$.a', 99)</span>
1197 <span class='jans'>&rarr; '{"a":2,"c":4}'</span></li>
1198
1199 <li><span class='jex'>json_insert('{"a":2,"c":4}', '$.e', 99)</span>
1200 <span class='jans'>&rarr; '{"a":2,"c":4,"e":99}'</span></li>
1201
1202 <li><span class='jex'>json_replace('{"a":2,"c":4}', '$.a', 99)</span>
1203 <span class='jans'>&rarr; '{"a":99,"c":4}'</span></li>
1204
1205 <li><span class='jex'>json_replace('{"a":2,"c":4}', '$.e', 99)</span>
1206 <span class='jans'>&rarr; '{"a":2,"c":4}'</span></li>
1207
1208 <li><span class='jex'>json_set('{"a":2,"c":4}', '$.a', 99)</span>
1209 <span class='jans'>&rarr; '{"a":99,"c":4}'</span></li>
1210
1211 <li><span class='jex'>json_set('{"a":2,"c":4}', '$.e', 99)</span>
1212 <span class='jans'>&rarr; '{"a":2,"c":4,"e":99}'</span></li>
1213
1214 <li><span class='jex'>json_set('{"a":2,"c":4}', '$.c', '[97,96]')</span>
1215 <span class='jans'>&rarr; '{"a":2,"c":"[97,96]"}'</span></li>
1216
1217 <li><span class='jex'>json_set('{"a":2,"c":4}', '$.c', json('[97,96]'))</span>
1218 <span class='jans'>&rarr; '{"a":2,"c":[97,96]}'</span></li>
1219
1220 <li><span class='jex'>json_set('{"a":2,"c":4}', '$.c', json_array(97,96))</span>
1221 <span class='jans'>&rarr; '{"a":2,"c":[97,96]}'</span></li>
1222
1223 </ul>
1224
1225
1226 <a name="jinsb"></a>
1227
1228 <a name="jreplb"></a>
1229
1230 <a name="jsetb"></a>
1231
1232 </p><h2 id="the_jsonb_insert_jsonb_replace_and_jsonb_set_functions"><span>4.12. </span>The jsonb_insert(), jsonb_replace, and jsonb_set() functions</h2>
1233
1234 <p>The jsonb_insert(), jsonb_replace(), and jsonb_set() functions work the
1235 same as <a href="json1.html#jins">json_insert()</a>, <a href="json1.html#jrepl">json_replace()</a>, and <a href="json1.html#jset">json_set()</a>, respectively,
1236 except that "<tt>jsonb_</tt>" versions return their result in the binary
1237 JSONB format.
1238
1239 <a name="jobj"></a>
1240
1241 </p><h2 id="the_json_object_function"><span>4.13. </span>The json_object() function</h2>
1242
1243 <p>The json_object() SQL function accepts zero or more pairs of arguments
1244 and returns a well-formed JSON object that is composed from those arguments.
1245 The first argument of each pair is the label and the second argument of
1246 each pair is the value.
1247 If any argument to json_object() is a BLOB then an error is thrown.
1248
1249 </p><p>The json_object() function currently allows duplicate labels without
1250 complaint, though this might change in a future enhancement.
1251
1252 </p><p>An argument with SQL type TEXT is normally converted into a quoted
1253 JSON string even if the input text is well-formed JSON.
1254 However, if the argument is the direct result from another JSON
1255 function or <a href="json1.html#jptr">the -> operator</a> (but not <a href="json1.html#jptr">the ->> operator</a>),
1256 then it is treated as JSON and all of its JSON type information
1257 and substructure is preserved. This allows calls to json_object()
1258 and <a href="json1.html#jarray">json_array()</a> to be nested. The <a href="json1.html#jmini">json()</a> function can also
1259 be used to force strings to be recognized as JSON.
1260
1261 </p><p>Examples:
1262
1263 <ul>
1264 <li><span class='jex'>json_object('a',2,'c',4)</span>
1265 <span class='jans'>&rarr; '{"a":2,"c":4}'</span></li>
1266
1267 <li><span class='jex'>json_object('a',2,'c','{e:5}')</span>
1268 <span class='jans'>&rarr; '{"a":2,"c":"{e:5}"}'</span></li>
1269
1270 <li><span class='jex'>json_object('a',2,'c',json_object('e',5))</span>
1271 <span class='jans'>&rarr; '{"a":2,"c":{"e":5}}'</span></li>
1272
1273 </ul>
1274
1275
1276 <a name="jobjb"></a>
1277
1278 </p><h2 id="the_jsonb_object_function"><span>4.14. </span>The jsonb_object() function</h2>
1279
1280 <p>
1281 The jsonb_object() function works just like the <a href="json1.html#jobj">json_object()</a> function
1282 except that the generated object is returned in the binary JSONB format.
1283
1284 <a name="jpatch"></a>
1285
1286 </p><h2 id="the_json_patch_function"><span>4.15. </span>The json_patch() function</h2>
1287
1288 <p>The json_patch(T,P) SQL function runs the
1289 <a href="https://tools.ietf.org/html/rfc7396">RFC-7396</a> MergePatch algorithm
1290 to apply patch P against input T. The patched copy of T is returned.
1291
1292 </p><p>MergePatch can add, modify, or delete elements of a JSON Object,
1293 and so for JSON Objects, the json_patch() routine is a generalized
1294 replacement for <a href="json1.html#jset">json_set()</a> and <a href="json1.html#jrm">json_remove()</a>. However, MergePatch
1295 treats JSON Array objects as atomic. MergePatch cannot append to an
1296 Array nor modify individual elements of an Array. It can only insert,
1297 replace, or delete the whole Array as a single unit. Hence, json_patch()
1298 is not as useful when dealing with JSON that includes Arrays,
1299 especially Arrays with lots of substructure.
1300
1301 </p><p>Examples:
1302
1303 <ul>
1304 <li><span class='jex'>json_patch('{"a":1,"b":2}','{"c":3,"d":4}')</span>
1305 <span class='jans'>&rarr; '{"a":1,"b":2,"c":3,"d":4}'</span></li>
1306
1307 <li><span class='jex'>json_patch('{"a":[1,2],"b":2}','{"a":9}')</span>
1308 <span class='jans'>&rarr; '{"a":9,"b":2}'</span></li>
1309
1310 <li><span class='jex'>json_patch('{"a":[1,2],"b":2}','{"a":null}')</span>
1311 <span class='jans'>&rarr; '{"b":2}'</span></li>
1312
1313 <li><span class='jex'>json_patch('{"a":1,"b":2}','{"a":9,"b":null,"c":8}')</span>
1314 <span class='jans'>&rarr; '{"a":9,"c":8}'</span></li>
1315
1316 <li><span class='jex'>json_patch('{"a":{"x":1,"y":2},"b":3}','{"a":{"y":9},"c":8}')</span>
1317 <span class='jans'>&rarr; '{"a":{"x":1,"y":9},"b":3,"c":8}'</span></li>
1318
1319 </ul>
1320
1321
1322
1323 <a name="jpatchb"></a>
1324
1325 </p><h2 id="the_jsonb_patch_function"><span>4.16. </span>The jsonb_patch() function</h2>
1326
1327 <p>
1328 The jsonb_patch() function works just like the <a href="json1.html#jpatch">json_patch()</a> function
1329 except that the patched JSON is returned in the binary JSONB format.
1330
1331 <a name="jpretty"></a>
1332
1333 </p><h2 id="the_json_pretty_function"><span>4.17. </span>The json_pretty() function</h2>
1334 <p>
1335 The json_pretty() function works like <a href="json1.html#jmini">json()</a> except that it adds
1336 extra whitespace to make the JSON result easier for humans to read.
1337 The first argument is the JSON or JSONB that is to be pretty-printed.
1338 The optional second argument is a text string that is used for indentation.
1339 If the second argument is omitted or is NULL, then indentation is four
1340 spaces per level.
1341 </p><p>
1342 The json_pretty() function was added with SQLite version 3.46.0
1343 (2024-05-23).
1344
1345 <a name="jrm"></a>
1346
1347 </p><h2 id="the_json_remove_function"><span>4.18. </span>The json_remove() function</h2>
1348
1349 <p>The json_remove(X,P,...) function takes a single JSON value as its
1350 first argument followed by zero or more path arguments.
1351 The json_remove(X,P,...) function returns
1352 a copy of the X parameter with all the elements
1353 identified by path arguments removed. Paths that select elements
1354 not found in X are silently ignored.
1355
1356 </p><p>Removals occurs sequentially from left to right. Changes caused by
1357 prior removals can affect the path search for subsequent arguments.
1358
1359 </p><p>If the json_remove(X) function is called with no path arguments,
1360 then it returns the input X reformatted, with excess whitespace
1361 removed.
1362
1363 </p><p>The json_remove() function throws an error if the first argument
1364 is not well-formed JSON or if any later argument is not a well-formed
1365 path.
1366
1367 </p><p>Examples:
1368
1369 <ul>
1370 <li><span class='jex'>json_remove('[0,1,2,3,4]','$[2]')</span>
1371 <span class='jans'>&rarr; '[0,1,3,4]'</span></li>
1372
1373 <li><span class='jex'>json_remove('[0,1,2,3,4]','$[2]','$[0]')</span>
1374 <span class='jans'>&rarr; '[1,3,4]'</span></li>
1375
1376 <li><span class='jex'>json_remove('[0,1,2,3,4]','$[0]','$[2]')</span>
1377 <span class='jans'>&rarr; '[1,2,4]'</span></li>
1378
1379 <li><span class='jex'>json_remove('[0,1,2,3,4]','$[#-1]','$[0]')</span>
1380 <span class='jans'>&rarr; '[1,2,3]'</span></li>
1381
1382 <li><span class='jex'>json_remove('{"x":25,"y":42}')</span>
1383 <span class='jans'>&rarr; '{"x":25,"y":42}'</span></li>
1384
1385 <li><span class='jex'>json_remove('{"x":25,"y":42}','$.z')</span>
1386 <span class='jans'>&rarr; '{"x":25,"y":42}'</span></li>
1387
1388 <li><span class='jex'>json_remove('{"x":25,"y":42}','$.y')</span>
1389 <span class='jans'>&rarr; '{"x":25}'</span></li>
1390
1391 <li><span class='jex'>json_remove('{"x":25,"y":42}','$')</span>
1392 <span class='jans'>&rarr; NULL</span></li>
1393
1394 </ul>
1395
1396
1397 <a name="jrmb"></a>
1398
1399 </p><h2 id="the_jsonb_remove_function"><span>4.19. </span>The jsonb_remove() function</h2>
1400 <p>
1401 The jsonb_remove() function works just like the <a href="json1.html#jrm">json_remove()</a> function
1402 except that the edited JSON result is returned in the binary JSONB format.
1403
1404 <a name="jtype"></a>
1405
1406 </p><h2 id="the_json_type_function"><span>4.20. </span>The json_type() function</h2>
1407
1408 <p>The json_type(X) function returns the "type" of the outermost element
1409 of X. The json_type(X,P) function returns the "type" of the element
1410 in X that is selected by path P. The "type" returned by json_type() is
1411 one of the following SQL text values:
1412 'null', 'true', 'false', 'integer', 'real', 'text', 'array', or 'object'.
1413 If the path P in json_type(X,P) selects an element that does not exist
1414 in X, then this function returns NULL.
1415
1416 </p><p>The json_type() function throws an error if its first argument is
1417 not well-formed JSON or JSONB or if its second argument is not a well-formed
1418 JSON path.
1419
1420 </p><p>Examples:
1421
1422 <ul>
1423 <li><span class='jex'>json_type('{"a":[2,3.5,true,false,null,"x"]}')</span>
1424 <span class='jans'>&rarr; 'object'</span></li>
1425
1426 <li><span class='jex'>json_type('{"a":[2,3.5,true,false,null,"x"]}','$')</span>
1427 <span class='jans'>&rarr; 'object'</span></li>
1428
1429 <li><span class='jex'>json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a')</span>
1430 <span class='jans'>&rarr; 'array'</span></li>
1431
1432 <li><span class='jex'>json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]')</span>
1433 <span class='jans'>&rarr; 'integer'</span></li>
1434
1435 <li><span class='jex'>json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]')</span>
1436 <span class='jans'>&rarr; 'real'</span></li>
1437
1438 <li><span class='jex'>json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]')</span>
1439 <span class='jans'>&rarr; 'true'</span></li>
1440
1441 <li><span class='jex'>json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]')</span>
1442 <span class='jans'>&rarr; 'false'</span></li>
1443
1444 <li><span class='jex'>json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]')</span>
1445 <span class='jans'>&rarr; 'null'</span></li>
1446
1447 <li><span class='jex'>json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]')</span>
1448 <span class='jans'>&rarr; 'text'</span></li>
1449
1450 <li><span class='jex'>json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]')</span>
1451 <span class='jans'>&rarr; NULL</span></li>
1452
1453 </ul>
1454
1455
1456 <a name="jvalid"></a>
1457
1458 </p><h2 id="the_json_valid_function"><span>4.21. </span>The json_valid() function</h2>
1459
1460 <p>The json_valid(X,Y) function returns 1 if the argument X is well-formed
1461 JSON, or returns 0 if X is not well-formed. The Y parameter is an integer
1462 bitmask that defines what is meant by "well-formed". The following bits
1463 of Y are currently defined:
1464
1465 </p><ul>
1466 <li> <b>0x01</b> &rarr;
1467 The input is text that strictly complies with canonical RFC-8259 JSON,
1468 without any extensions.
1469 </li><li> <b>0x02</b> &rarr;
1470 The input is text that is JSON with <a href="json1.html#json5">JSON5</a> extensions described above.
1471 </li><li> <b>0x04</b> &rarr;
1472 The input is a BLOB that superficially appears to be <a href="json1.html#jsonbx">JSONB</a>.
1473 </li><li> <b>0x08</b> &rarr;
1474 The input is a BLOB that strictly conforms to the internal <a href="json1.html#jsonbx">JSONB</a> format.
1475 </li></ul>
1476
1477 <p>By combining bits, the following useful values of Y can be derived:
1478
1479 </p><ul>
1480 <li> <b>1</b> &rarr; X is RFC-8259 JSON text
1481 </li><li> <b>2</b> &rarr; X is <a href="json1.html#json5">JSON5</a> text
1482 </li><li> <b>4</b> &rarr; X is probably <a href="json1.html#jsonbx">JSONB</a>
1483 </li><li> <b>5</b> &rarr; X is RFC-8259 JSON text or <a href="json1.html#jsonbx">JSONB</a>
1484 </li><li> <b>6</b> &rarr; X is <a href="json1.html#json5">JSON5</a> text or <a href="json1.html#jsonbx">JSONB</a>
1485 &larr; <i>This is probably the value you want</i>
1486 </li><li> <b>8</b> &rarr; X is strictly conforming <a href="json1.html#jsonbx">JSONB</a>
1487 </li><li> <b>9</b> &rarr; X is RFC-8259 or strictly conforming <a href="json1.html#jsonbx">JSONB</a>
1488 </li><li> <b>10</b> &rarr; X is JSON5 or strictly conforming <a href="json1.html#jsonbx">JSONB</a>
1489 </li></ul>
1490
1491 <p>The Y parameter is optional. If omitted, it defaults to 1, which means
1492 that the default behavior is to return true only if the input X is
1493 strictly conforming RFC-8259 JSON text without any extensions. This
1494 makes the one-argument version of json_valid() compatible with older
1495 versions of SQLite, prior to the addition of support for
1496 <a href="json1.html#json5">JSON5</a> and <a href="json1.html#jsonbx">JSONB</a>.
1497
1498 </p><p>The difference between 0x04 and 0x08 bits in the Y parameter is that
1499 0x04 only examines the outer wrapper of the BLOB to see if it superficially
1500 looks like <a href="json1.html#jsonbx">JSONB</a>. This is sufficient for most purposes and is very fast.
1501 The 0x08 bit does a thorough examination of all internal details of the BLOB.
1502 The 0x08 bit takes time that is linear in the size of the X input and is much
1503 slower. The 0x04 bit is recommended for most purposes.
1504
1505 </p><p>If you just want to know if a value is a plausible input to one of
1506 the other JSON functions, a Y value of 6 is probably what you want to use.
1507
1508 </p><p>Any Y value less than 1 or greater than 15 raises an error, for the
1509 latest version of json_valid(). However, future versions of json_valid()
1510 might be enhanced to accept flag values outside of this range, having new
1511 meanings that we have not yet thought of.
1512
1513 </p><p>If either X or Y inputs to json_valid() are NULL, then the function
1514 returns NULL.
1515
1516 </p><p>Examples:
1517
1518 <ul>
1519 <li><span class='jex'>json_valid('{"x":35}')</span>
1520 <span class='jans'>&rarr; 1</span></li>
1521
1522 <li><span class='jex'>json_valid('{x:35}')</span>
1523 <span class='jans'>&rarr; 0</span></li>
1524
1525 <li><span class='jex'>json_valid('{x:35}',6)</span>
1526 <span class='jans'>&rarr; 1</span></li>
1527
1528 <li><span class='jex'>json_valid('{"x":35')</span>
1529 <span class='jans'>&rarr; 0</span></li>
1530
1531 <li><span class='jex'>json_valid(NULL)</span>
1532 <span class='jans'>&rarr; NULL</span></li>
1533
1534 </ul>
1535
1536
1537 <a name="jquote"></a>
1538
1539 </p><h2 id="the_json_quote_function"><span>4.22. </span>The json_quote() function</h2>
1540
1541 <p>The json_quote(X) function converts the SQL value X (a number or a
1542 string) into its corresponding JSON representation. If X is a JSON value
1543 returned by another JSON function, then this function is a no-op.
1544
1545 </p><p>Examples:
1546
1547 <ul>
1548 <li><span class='jex'>json_quote(3.14159)</span>
1549 <span class='jans'>&rarr; 3.14159</span></li>
1550
1551 <li><span class='jex'>json_quote('verdant')</span>
1552 <span class='jans'>&rarr; '"verdant"'</span></li>
1553
1554 <li><span class='jex'>json_quote('[1]')</span>
1555 <span class='jans'>&rarr; '"[1]"'</span></li>
1556
1557 <li><span class='jex'>json_quote(json('[1]'))</span>
1558 <span class='jans'>&rarr; '[1]'</span></li>
1559
1560 <li><span class='jex'>json_quote('[1,')</span>
1561 <span class='jans'>&rarr; '"[1,"'</span></li>
1562
1563 </ul>
1564
1565
1566 <a name="jgrouparray"></a>
1567 <a name="jgroupobject"></a>
1568 <a name="jgrouparrayb"></a>
1569 <a name="jgroupobjectb"></a>
1570
1571 </p><h2 id="array_and_object_aggregate_functions"><span>4.23. </span>Array and object aggregate functions</h2>
1572
1573 <p>The json_group_array(X) function is an
1574 <a href="lang_aggfunc.html">aggregate SQL function</a> that returns a JSON array
1575 comprised of all X values in the aggregation.
1576 Similarly, the json_group_object(NAME,VALUE) function returns a JSON object
1577 comprised of all NAME/VALUE pairs in the aggregation.
1578 The "<tt>jsonb_</tt>" variants are the same except that they return their
1579 result in the binary <a href="json1.html#jsonbx">JSONB</a> format.
1580
1581 <a name="jeach"></a>
1582 <a name="jtree"></a>
1583 <a name="jbeach"></a>
1584 <a name="jbtree"></a>
1585
1586 </p><h2 id="table_valued_functions_for_parsing_json_json_each_jsonb_each_json_tree_and_jsonb_tree_"><span>4.24. </span>Table valued functions for parsing JSON:
1587 json_each(), jsonb_each(), json_tree(), and jsonb_tree()</h2>
1588
1589 <p>The json_each(X), jsonb_each(X), json_tree(X), and jsonb_tree(X)
1590 <a href="vtab.html#tabfunc2">table-valued functions</a> all walk the
1591 JSON value provided as their first argument and return one row for each
1592 element. The json_each(X) and jsonb_each(X) functions walk only the
1593 immediate children of the top-level array or object,
1594 or just the top-level element itself if the top-level
1595 element is a primitive value.
1596 The json_tree(X) and jsonb_tree(X) functions recursively walk through the
1597 JSON substructure starting with the top-level element.
1598
1599 </p><p>The json_each(X,P), jsonb_each(X,P), json_tree(X,P), and jsonb_tree(X,P)
1600 functions work just like their one-argument counterparts except that they
1601 treat the element identified by path P as the top-level element.
1602
1603 </p><p>The jsonb_each() and jsonb_tree() variants
1604 are available beginning with SQLite version 3.51.0 (2025-11-04).
1605 These two variants work the same as their counterparts with out the "b"
1606 except that the "value" column returns <a href="json1.html#jsonbx">JSONB</a> instead of text JSON
1607 when the "type" column is 'object' or 'array'.
1608
1609 </p><p>The following chart summarizes the differences between the various
1610 JSON table-valued functions:
1611
1612 </p><table border="1" cellpadding="5" cellspacing="0" width="75%" align="center">
1613 <tr>
1614 <td width="20%">&nbsp;
1615 </td><td width="10%" align="center"><b>json_each()</b>
1616 </td><td width="10%" align="center"><b>jsonb_each()</b>
1617 </td><td width="10%" align="center"><b>json_tree()</b>
1618 </td><td width="10%" align="center"><b>jsonb_tree()</b>
1619 </td></tr>
1620 <tr>
1621 <td width="20%">
1622 Recursively walk the substructure of the input
1623 JSON, or parse only a single layer
1624 </td><td width="10%" align="center">single layer
1625 </td><td width="10%" align="center">single layer
1626 </td><td width="10%" align="center">recursive
1627 </td><td width="10%" align="center">recursive
1628 </td></tr>
1629 <tr>
1630 <td width="20%">
1631 The "value" column returns text
1632 or JSONB for 'array' and 'object'
1633 </td><td width="10%" align="center">text
1634 </td><td width="10%" align="center">JSONB
1635 </td><td width="10%" align="center">text
1636 </td><td width="10%" align="center">JSONB
1637 </td></tr>
1638 <tr>
1639 <td width="20%">
1640 Supported since SQLite version...
1641 </td><td width="10%" align="center">3.9.0<br>(2015-10-14)
1642 </td><td width="10%" align="center">3.51.0<br>(2025-11-04)
1643 </td><td width="10%" align="center">3.9.0<br>(2015-10-14)
1644 </td><td width="10%" align="center">3.51.0<br>(2025-11-04)
1645 </td></tr>
1646 </table>
1647
1648 <p>The schema for the table returned by all of the JSON table-valued
1649 functions is as follows:
1650
1651 </p><blockquote><pre>
1652 CREATE TABLE json_tree(
1653 key ANY, -- key for current element relative to its parent
1654 value ANY, -- value for the current element
1655 type TEXT, -- 'object','array','string','integer', etc.
1656 atom ANY, -- value for primitive types, null for array & object
1657 id INTEGER, -- integer ID for this element
1658 parent INTEGER, -- integer ID for the parent of this element
1659 fullkey TEXT, -- full path describing the current element
1660 path TEXT, -- path to the container of the current row
1661 json JSON HIDDEN, -- 1st input parameter: the raw JSON
1662 root TEXT HIDDEN -- 2nd input parameter: the PATH at which to start
1663 );
1664 </pre></blockquote>
1665
1666 <p>
1667 The "key" column is the integer array index for elements of a JSON array
1668 and the text label for elements of a JSON object. The key column is
1669 NULL in all other cases.
1670
1671 </p><p>
1672 The "value" column is the SQL value for the JSON element designated
1673 by "key" or "fullkey". If the "type" of the element is one of 'null',
1674 'true', 'false', 'integer', 'real', or 'text, then the corresponding
1675 SQL value of that element is returned as the "value".
1676 When "type" is 'array' or 'object', the "value" column will return
1677 the text JSON of the array or object in json_each() and json_tree(), or
1678 the JSONB of the array or object in the case of jsonb_each() or jsonb_tree().
1679 The format of the "value" column is the only behavioral difference
1680 between json_each() and jsonb_each() and between json_tree() and jsonb_tree().
1681
1682 </p><p>
1683 The "type" column is an SQL text value taken from ('null', 'true', 'false',
1684 'integer', 'real', 'text', 'array', 'object') according to the type of
1685 the current JSON element.
1686
1687 </p><p>
1688 The "atom" column is the SQL value corresponding to primitive elements -
1689 elements other than JSON arrays and objects. The "atom" column is NULL
1690 for a JSON array or object.
1691
1692 </p><p>
1693 The "id" column is an integer that identifies a specific JSON element
1694 within the complete JSON string. The "id" integer is an internal housekeeping
1695 number, the computation of which might change in future releases. The
1696 only guarantee is that the "id" column will be different for every row.
1697
1698 </p><p>
1699 The "parent" column is always NULL for json_each().
1700 For json_tree(),
1701 the "parent" column is the "id" integer for the parent of the current
1702 element, or NULL for the top-level JSON element or the element identified
1703 by the root path in the second argument.
1704
1705 </p><p>
1706 The "fullkey" column is a text path that uniquely identifies the current
1707 row element within the original JSON string. The complete key to the
1708 true top-level element is returned even if an alternative starting point
1709 is provided by the "root" argument.
1710
1711 </p><p>
1712 The "path" column is the path to the array or object container that holds
1713 the current row, or the path to the current row in the case where the
1714 iteration starts on a primitive type and thus only provides a single
1715 row of output.
1716
1717 </p><p>
1718 The two hidden columns, "json" and "root", are intended for input to the
1719 virtual table only. Use them for join constraints, including the implied
1720 join constraints that are constructed automatically by the
1721 <a href="vtab.html#tabfunc2">table-valued function</a> syntax.
1722 If you try to return the values of the "json" or "root" columns,
1723 you might not get reliable answers.
1724
1725 </p><h3 id="examples_using_json_each_and_json_tree_"><span>4.24.1. </span>Examples using json_each() and json_tree()</h3>
1726
1727 <p>Suppose the table "CREATE TABLE user(name,phone)" stores zero or
1728 more phone numbers as a JSON array object in the user.phone field.
1729 To find all users who have any phone number with a 704 area code:
1730
1731 </p><blockquote><pre>
1732 SELECT DISTINCT user.name
1733 FROM user, json_each(user.phone)
1734 WHERE json_each.value LIKE '704-%';
1735 </pre></blockquote>
1736
1737 <p>Now suppose the user.phone field contains plain text if the user
1738 has only a single phone number and a JSON array if the user has multiple
1739 phone numbers. The same question is posed: "Which users have a phone number
1740 in the 704 area code?" But now the json_each() function can only be called
1741 for those users that have two or more phone numbers since json_each()
1742 requires well-formed JSON as its first argument:
1743
1744 </p><blockquote><pre>
1745 SELECT name FROM user WHERE phone LIKE '704-%'
1746 UNION
1747 SELECT user.name
1748 FROM user, json_each(user.phone)
1749 WHERE json_valid(user.phone)
1750 AND json_each.value LIKE '704-%';
1751 </pre></blockquote>
1752
1753 <p>Consider a different database with "CREATE TABLE big(json JSON)".
1754 To see a complete line-by-line decomposition of the data:
1755
1756 </p><blockquote><pre>
1757 SELECT big.rowid, fullkey, value
1758 FROM big, json_tree(big.json)
1759 WHERE json_tree.type NOT IN ('object','array');
1760 </pre></blockquote>
1761
1762 <p>In the previous, the "type NOT IN ('object','array')" term of the
1763 WHERE clause suppresses containers and only lets through leaf elements.
1764 The same effect could be achieved this way:
1765
1766 </p><blockquote><pre>
1767 SELECT big.rowid, fullkey, atom
1768 FROM big, json_tree(big.json)
1769 WHERE atom IS NOT NULL;
1770 </pre></blockquote>
1771
1772 <p>Suppose each entry in the BIG table is a JSON object
1773 with a '$.id' field that is a unique identifier
1774 and a '$.partlist' field that can be a deeply nested object.
1775 You want to find the id of every entry that contains one
1776 or more references to uuid '6fa5181e-5721-11e5-a04e-57f3d7b32808' anywhere
1777 in its '$.partlist'.
1778
1779 </p><blockquote><pre>
1780 SELECT DISTINCT json_extract(big.json,'$.id')
1781 FROM big, json_tree(big.json, '$.partlist')
1782 WHERE json_tree.key='uuid'
1783 AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
1784 </pre></blockquote>
1785 <p align="center"><small><i>This page was last updated on 2026-05-20 11:35:23Z </small></i></p>
1786