| 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 "[<i>arrayindex</i>]". |
| 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'>→ '[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'>→ '{"ex":"[52,3.14159]"}'</span></li> |
| 540 |
|
| 541 |
<li><span class='jex'>json_object('ex',('[52,3.14159]'->>'$'))</span> |
| 542 |
<span class='jans'>→ '{"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'>→ '{"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'>→ '{"ex":[52,3.14159]}'</span></li> |
| 558 |
|
| 559 |
<li><span class='jex'>json_object('ex','[52,3.14159]'->'$')</span> |
| 560 |
<span class='jans'>→ '{"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 |
← Less efficient. |
| 679 |
</li><li> |
| 680 |
<tt>... json_insert(A,'$.b',jsonb(C)) ...</tt> |
| 681 |
← 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 |
← More efficient. |
| 696 |
</li><li> |
| 697 |
<tt>... json_group_array(jsonb(A))) ...</tt> |
| 698 |
← 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'>→ '{"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'>→ '[1,2,"3",4]'</span></li> |
| 818 |
|
| 819 |
<li><span class='jex'>json_array('[1,2]')</span> |
| 820 |
<span class='jans'>→ '["[1,2]"]'</span></li> |
| 821 |
|
| 822 |
<li><span class='jex'>json_array(json_array(1,2))</span> |
| 823 |
<span class='jans'>→ '[[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'>→ '[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'>→ '[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 "[…]") |
| 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'>→ [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'>→ {"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'>→ 4</span></li> |
| 903 |
|
| 904 |
<li><span class='jex'>json_array_length('[1,2,3,4]', '$')</span> |
| 905 |
<span class='jans'>→ 4</span></li> |
| 906 |
|
| 907 |
<li><span class='jex'>json_array_length('[1,2,3,4]', '$[2]')</span> |
| 908 |
<span class='jans'>→ 0</span></li> |
| 909 |
|
| 910 |
<li><span class='jex'>json_array_length('{"one":[1,2,3]}')</span> |
| 911 |
<span class='jans'>→ 0</span></li> |
| 912 |
|
| 913 |
<li><span class='jex'>json_array_length('{"one":[1,2,3]}', '$.one')</span> |
| 914 |
<span class='jans'>→ 3</span></li> |
| 915 |
|
| 916 |
<li><span class='jex'>json_array_length('{"one":[1,2,3]}', '$.two')</span> |
| 917 |
<span class='jans'>→ 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'>→ '{"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'>→ '[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'>→ '{"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'>→ 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'>→ '[[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'>→ 5</span></li> |
| 977 |
|
| 978 |
<li><span class='jex'>json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x')</span> |
| 979 |
<span class='jans'>→ 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'>→ '[null,2]'</span></li> |
| 983 |
|
| 984 |
<li><span class='jex'>json_extract('{"a":"xyz"}', '$.a')</span> |
| 985 |
<span class='jans'>→ 'xyz'</span></li> |
| 986 |
|
| 987 |
<li><span class='jex'>json_extract('{"a":null}', '$.a')</span> |
| 988 |
<span class='jans'>→ 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 '$[#-K]'. 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'>→ '{"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'>→ '[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'>→ '[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'>→ '{"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'>→ '7'</span></li> |
| 1090 |
|
| 1091 |
<li><span class='jex'>'{"a":2,"c":[4,5,{"f":7}]}' ->> '$.c[2].f'</span> |
| 1092 |
<span class='jans'>→ 7</span></li> |
| 1093 |
|
| 1094 |
<li><span class='jex'>'{"a":2,"c":[4,5,{"f":7}]}' -> 'c' -> 2 ->> 'f'</span> |
| 1095 |
<span class='jans'>→ 7</span></li> |
| 1096 |
|
| 1097 |
<li><span class='jex'>'{"a":2,"c":[4,5],"f":7}' -> '$.c[#-1]'</span> |
| 1098 |
<span class='jans'>→ '5'</span></li> |
| 1099 |
|
| 1100 |
<li><span class='jex'>'{"a":2,"c":[4,5,{"f":7}]}' -> '$.x'</span> |
| 1101 |
<span class='jans'>→ NULL</span></li> |
| 1102 |
|
| 1103 |
<li><span class='jex'>'[11,22,33,44]' -> 3</span> |
| 1104 |
<span class='jans'>→ '44'</span></li> |
| 1105 |
|
| 1106 |
<li><span class='jex'>'[11,22,33,44]' ->> 3</span> |
| 1107 |
<span class='jans'>→ 44</span></li> |
| 1108 |
|
| 1109 |
<li><span class='jex'>'{"a":"xyz"}' -> '$.a'</span> |
| 1110 |
<span class='jans'>→ '"xyz"'</span></li> |
| 1111 |
|
| 1112 |
<li><span class='jex'>'{"a":"xyz"}' ->> '$.a'</span> |
| 1113 |
<span class='jans'>→ 'xyz'</span></li> |
| 1114 |
|
| 1115 |
<li><span class='jex'>'{"a":null}' -> '$.a'</span> |
| 1116 |
<span class='jans'>→ 'null'</span></li> |
| 1117 |
|
| 1118 |
<li><span class='jex'>'{"a":null}' ->> '$.a'</span> |
| 1119 |
<span class='jans'>→ 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'>→ '[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'>→ '[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'>→ '{"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'>→ '{"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'>→ '{"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'>→ '{"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'>→ '{"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'>→ '{"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'>→ '{"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'>→ '{"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'>→ '{"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'>→ '{"a":2,"c":4}'</span></li> |
| 1266 |
|
| 1267 |
<li><span class='jex'>json_object('a',2,'c','{e:5}')</span> |
| 1268 |
<span class='jans'>→ '{"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'>→ '{"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'>→ '{"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'>→ '{"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'>→ '{"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'>→ '{"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'>→ '{"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'>→ '[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'>→ '[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'>→ '[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'>→ '[1,2,3]'</span></li> |
| 1381 |
|
| 1382 |
<li><span class='jex'>json_remove('{"x":25,"y":42}')</span> |
| 1383 |
<span class='jans'>→ '{"x":25,"y":42}'</span></li> |
| 1384 |
|
| 1385 |
<li><span class='jex'>json_remove('{"x":25,"y":42}','$.z')</span> |
| 1386 |
<span class='jans'>→ '{"x":25,"y":42}'</span></li> |
| 1387 |
|
| 1388 |
<li><span class='jex'>json_remove('{"x":25,"y":42}','$.y')</span> |
| 1389 |
<span class='jans'>→ '{"x":25}'</span></li> |
| 1390 |
|
| 1391 |
<li><span class='jex'>json_remove('{"x":25,"y":42}','$')</span> |
| 1392 |
<span class='jans'>→ 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'>→ 'object'</span></li> |
| 1425 |
|
| 1426 |
<li><span class='jex'>json_type('{"a":[2,3.5,true,false,null,"x"]}','$')</span> |
| 1427 |
<span class='jans'>→ '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'>→ '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'>→ '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'>→ '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'>→ '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'>→ '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'>→ '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'>→ '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'>→ 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> → |
| 1467 |
The input is text that strictly complies with canonical RFC-8259 JSON, |
| 1468 |
without any extensions. |
| 1469 |
</li><li> <b>0x02</b> → |
| 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> → |
| 1472 |
The input is a BLOB that superficially appears to be <a href="json1.html#jsonbx">JSONB</a>. |
| 1473 |
</li><li> <b>0x08</b> → |
| 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> → X is RFC-8259 JSON text |
| 1481 |
</li><li> <b>2</b> → X is <a href="json1.html#json5">JSON5</a> text |
| 1482 |
</li><li> <b>4</b> → X is probably <a href="json1.html#jsonbx">JSONB</a> |
| 1483 |
</li><li> <b>5</b> → X is RFC-8259 JSON text or <a href="json1.html#jsonbx">JSONB</a> |
| 1484 |
</li><li> <b>6</b> → X is <a href="json1.html#json5">JSON5</a> text or <a href="json1.html#jsonbx">JSONB</a> |
| 1485 |
← <i>This is probably the value you want</i> |
| 1486 |
</li><li> <b>8</b> → X is strictly conforming <a href="json1.html#jsonbx">JSONB</a> |
| 1487 |
</li><li> <b>9</b> → X is RFC-8259 or strictly conforming <a href="json1.html#jsonbx">JSONB</a> |
| 1488 |
</li><li> <b>10</b> → 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'>→ 1</span></li> |
| 1521 |
|
| 1522 |
<li><span class='jex'>json_valid('{x:35}')</span> |
| 1523 |
<span class='jans'>→ 0</span></li> |
| 1524 |
|
| 1525 |
<li><span class='jex'>json_valid('{x:35}',6)</span> |
| 1526 |
<span class='jans'>→ 1</span></li> |
| 1527 |
|
| 1528 |
<li><span class='jex'>json_valid('{"x":35')</span> |
| 1529 |
<span class='jans'>→ 0</span></li> |
| 1530 |
|
| 1531 |
<li><span class='jex'>json_valid(NULL)</span> |
| 1532 |
<span class='jans'>→ 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'>→ 3.14159</span></li> |
| 1550 |
|
| 1551 |
<li><span class='jex'>json_quote('verdant')</span> |
| 1552 |
<span class='jans'>→ '"verdant"'</span></li> |
| 1553 |
|
| 1554 |
<li><span class='jex'>json_quote('[1]')</span> |
| 1555 |
<span class='jans'>→ '"[1]"'</span></li> |
| 1556 |
|
| 1557 |
<li><span class='jex'>json_quote(json('[1]'))</span> |
| 1558 |
<span class='jans'>→ '[1]'</span></li> |
| 1559 |
|
| 1560 |
<li><span class='jex'>json_quote('[1,')</span> |
| 1561 |
<span class='jans'>→ '"[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%"> |
| 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 |
|