Debian Codegraph @

www/tcljson.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>Bridging Tcl and JSON with the SQLite Extension</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 Bridging Tcl and JSON with the SQLite Extension
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#using_sqlite_to_bridge_tcl_and_json">1. Using SQLite to Bridge Tcl and JSON</a></div>
126 <div class="fancy-toc2"><a href="#consuming_json">1.1. Consuming JSON</a></div>
127 <div class="fancy-toc2"><a href="#emitting_json_db_table_as_a_json_object">1.2. Emitting JSON: Db Table as a JSON Object</a></div>
128 <div class="fancy-toc2"><a href="#emitting_json_incremental_build_up">1.3. Emitting JSON: Incremental Build-up</a></div>
129 </div>
130 </details>
131 </div>
132
133
134
135
136
137 <h1 id="using_sqlite_to_bridge_tcl_and_json"><span>1. </span>Using SQLite to Bridge Tcl and JSON</h1>
138
139 <p><a href="./tclsqlite.html">The Tcl SQLite extension</a> can act as
140 a bridge to smooth over Tcl's impedance mismatch with JSON,
141 shifting all of the parsing and escaping from Tcl to SQLite with
142 very little effort. The sections below demonstrate techniques
143 for consuming and emitting JSON via SQLite.
144 </p>
145
146 <h2 id="consuming_json"><span>1.1. </span>Consuming JSON</h2>
147
148 <p>One of the simplest way to consume and break down a JSON payload is
149 to feed it to SQLite and extract the results using Tcl's
150 <code>lassign</code> function:
151
152 </p><blockquote><pre>
153 set json theJSON...
154 lassign &lbrack;db eval {
155 with payload(j) as (select jsonb($json)) select
156 -- A simple value:
157 j->>'foo',
158 -- A sub-object or array:
159 CASE WHEN json_valid(j->>'bar') THEN j->>'bar' ELSE NULL END
160 -- Any other fields...
161 from payload
162 }] foo bar
163 </pre></blockquote>
164
165 <p>Arrays can be injested using <code>json_each()</code>:</p>
166
167 <blockquote><pre>
168 set json {&lbrack;1, "two", 3.0, {"nested": true}, null]}
169 set myList &lbrack;db eval {
170 select value from json_each($json)
171 }]
172
173 # Emits: 1 two 3.0 {{"nested":true}} {}
174 </pre></blockquote>
175
176 <p>Arrays of objects can be decomposed into a list of dict objects:</p>
177
178 <blockquote><pre>
179 set json {&lbrack;{"id":1, "v":"A"}, {"id":2, "v":"B"}]}
180 set myDictList &lbrack;db eval {
181 select json_object('id', value->>'id', 'v', value->>'v')
182 from json_each($json)
183 }]
184
185 # Emits: {{"id":1,"v":"A"}} {{"id":2,"v":"B"}}
186 </pre></blockquote>
187
188 <p>Object entries in array objects can be decomposed using an approach
189 like the first example shown above.</p>
190
191 <h2 id="emitting_json_db_table_as_a_json_object"><span>1.2. </span>Emitting JSON: Db Table as a JSON Object</h2>
192
193 <p>The approach described here uses an in-memory db to
194 incrementally build up a JSON object, automatically smoothing
195 over the edges between data types such as numbers and strings.
196 </p>
197
198 <blockquote><pre>
199 #
200 # Initialize, if needed, the sqlite3 connection for the JR temp db and
201 # return its command name.
202 #
203 # JR = JSON Response (to an HTTP request)
204 #
205 proc jr-db {} {
206 if {"" eq &lbrack;info command ::__jrdb]} {
207 sqlite3 ::__jrdb ":memory:"
208 ::__jrdb eval {
209 -- Holds a list of key/value pairs for a JSON response object
210 create table jr(k TEXT UNIQUE ON CONFLICT REPLACE,v ANY);
211 }
212 }
213 return ::__jrdb
214 }
215
216 #
217 # Remove all entries from the JR buffer.
218 #
219 proc jr-reset {args} {
220 &lbrack;jr-db] eval {delete from jr}
221 }
222
223 #
224 # Set one or more key/value pairs in the JR object.
225 #
226 proc jr-set {args} {
227 set db &lbrack;jr-db]
228 foreach {k v} $args {
229 $db eval {insert into jr(k,v) values($k,$v)}
230 }
231 }
232
233 #
234 # Returns a JSON-format object representing the current JR state.
235 #
236 proc jr-get {} {
237 &lbrack;jr-db] onecolumn {
238 select json_group_object(k,CASE WHEN json_valid(v,1)
239 THEN json(v) ELSE v END) from jr
240 }
241 }
242 </pre></blockquote>
243
244 <p>Example usage:</p>
245
246 <blockquote><pre>
247 jr-set message "This is a message."
248 jr-set aBool true isNull null anInt 37 aFloat 42.42
249 puts &lbrack;jr-get]
250 </pre></blockquote>
251
252 <p>Results in:</p>
253
254
255 <blockquote><pre>
256 {"message":"This is a message.","aBool":true,"isNull":null,"anInt":37,"aFloat":42.42}
257 </pre></blockquote>
258
259
260 <h2 id="emitting_json_incremental_build_up"><span>1.3. </span>Emitting JSON: Incremental Build-up</h2>
261
262 <p>The approach shown here uses a couple of routines to incrementally
263 build up a nested object or array, faithfully navigating the type
264 handling, such that integers, null, and boolean-looking values can be
265 emitted as such instead of strings.</p>
266
267 <blockquote><pre>
268 #
269 # Returns the command name of the shared SQLite3 instance used by the
270 # X-to-JSON APIs, initializing it on demand.
271 #
272 proc json-db {} {
273 if {"" eq &lbrack;info command ::__jsonDb]} {
274 sqlite3 ::__jsonDb ":memory:"
275 }
276 return ::__jsonDb
277 }
278
279 #
280 # Given a dict-style object, this creates a JSON object representation.
281 # It does not handle nested objects but each value may itself be the result
282 # of its own call to this method, which has a similar effect.
283 #
284 proc json-obj {kvps} {
285 set q "select json_object("
286 set n 0
287 set nn 1
288 foreach {k v} $kvps {
289 set $n $k
290 set $nn $v
291 if {$n} {append q ","}
292 append q "\$$n, CASE WHEN json_valid(\$$nn) THEN json(\$$nn) ELSE \$$nn END"
293 # This may look like an SQL injection opportunity, but it's
294 # injecting prepared statement placeholders, not values.
295 incr n 2
296 incr nn 2
297 }
298 append q ")"
299 &lbrack;json-db] onecolumn $q
300 }
301
302 #
303 # Returns a JSON-format array of the given arguments. See json-object
304 # for notes about nesting.
305 #
306 proc json-array {args} {
307 set n 0
308 set q "select json_array("
309 foreach a $args {
310 set $n $a
311 if {$n} {append q ","}
312 append q "CASE WHEN json_valid(\$$n) THEN json(\$$n) ELSE \$$n END"
313 incr n
314 }
315 append q ")"
316 &lbrack;json-db] onecolumn $q
317 }
318 </pre></blockquote>
319
320 <p>Example usage:</p>
321
322 <blockquote><pre>
323 proc json-pretty {arg {space " "}} {
324 &lbrack;json-db] onecolumn {select json_pretty($arg,$space)}
325 }
326 set o {
327 hi world
328 number 17.3
329 truth true
330 lies false
331 nil null
332 nada "null"
333 notNull "Null"
334 }
335 lappend o list &lbrack;json-array hello there &lbrack;json-obj {nested true}]]
336 lappend o obj &lbrack;json-obj {name "Nested object"}]
337 puts &lbrack;json-pretty &lbrack;json-obj $o]]
338 </pre></blockquote>
339
340 <p>Results in:</p>
341
342 <blockquote><pre>{
343 "hi": "world",
344 "number": 17.3,
345 "truth": true,
346 "lies": false,
347 "nil": null,
348 "nada": null,
349 "notNull": "Null",
350 "list": &lbrack;
351 "hello",
352 "there",
353 {
354 "nested": true
355 }
356 ],
357 "obj": {
358 "name": "Nested object"
359 }
360 }
361 </pre></blockquote>
362 <p align="center"><small><i>This page was last updated on 2026-02-24 16:01:53Z </small></i></p>
363