fix authentication failure
[Evergreen.git] / docs / TechRef / JSONTutorial.xml
1 <?xml version="1.0" encoding="utf-8"?>
2
3 <sect1 version="5.0" xmlns="http://docbook.org/ns/docbook"
4         xmlns:xi="http://www.w3.org/2003/XInclude" xmlns:xlink="http://www.w3.org/1999/xlink">
5
6         <info>
7                 <title>JSON Queries: A Tutorial</title>
8
9                 <author>
10                         <personname>
11                                 <firstname>Scott</firstname>
12                                 <surname>McKellar</surname>
13                         </personname>
14                         <affiliation>
15                                 <orgname>Equinox Software, Inc.</orgname>
16                         </affiliation>
17                 </author>
18
19                 <copyright>
20                         <year>2009</year>
21                         <holder>Equinox Software, Inc.</holder>
22                 </copyright>
23
24                 <releaseinfo>
25                         Licensing: Creative Commons Attribution-Share Alike 3.0 United States License.
26                 </releaseinfo>
27         </info>
28
29         <sect2>
30                 <title>Introduction</title>
31                 <para> The json_query facility provides a way for client applications to query the
32                         database over the network.  Instead of constructing its own SQL, the application
33                         encodes a query in the form of a JSON string and passes it to the json_query service.
34                         Then the json_query service parses the JSON, constructs and executes the
35                         corresponding SQL, and returns the results to the client application. </para>
36                 <para> This arrangement enables the json_query service to act as a gatekeeper, protecting
37                         the database from potentially damaging SQL commands.  In particular, the generated SQL
38                         is confined to SELECT statements, which will not change the contents of the database. </para>
39                 <para> In addition, the json_query service sometimes uses its knowledge of the database
40                         structure to supply column names and join conditions so that the client application
41                         doesn't have to. </para>
42                 <para> Nevertheless, the need to encode a query in a JSON string adds complications,
43                         because the client needs to know how to build the right JSON.  JSON queries are also
44                         somewhat limiting -- they can't do all of the things that you can do with raw SQL. </para>
45                 <para> This tutorial explains what you can do with a JSON query, and how you can do it. </para>
46
47                 <sect3>
48                         <title>The IDL</title>
49                         <para> A JSON query does not refer to tables and columns.  Instead, it refers to classes
50                                 and fields, which the IDL maps to the corresponding database entities. </para>
51                         <para> The IDL (Interface Definition Language) is an XML file, typically
52                                 <filename>/openils/conf/fm_IDL.xml</filename>.  It maps each class to a table, view,
53                                 or subquery, and each field to a column.  It also includes information about foreign
54                                 key relationships. </para>
55                         <para> (The IDL also defines virtual classes and virtual fields, which don't correspond
56                                 to database entities.  We won't discuss them here, because json_query ignores them.) </para>
57                         <para> When it first starts up, json_query loads a relevant subset of the IDL into memory.
58                                 Thereafter, it consults its copy of the IDL whenever it needs to know about the
59                                 database structure.  It uses the IDL to validate the JSON queries, and to translate
60                                 classes and fields to the corresponding tables and columns.  In some cases it uses the
61                                 IDL to supply information that the queries don't provide. </para>
62                 </sect3>
63
64                 <sect3>
65                         <title>Definitions</title>
66                         <para> References to "SQL" refer to the dialect implemented by PostgreSQL.  This tutorial
67                                 assumes that you are already familiar with SQL. </para>
68                         <para> You should also be familiar with JSON.  However it is worth defining a couple of terms
69                                 that have other meanings in other contexts: </para>
70
71                         <itemizedlist>
72
73                                 <listitem>
74                                         <para> An "object" is a JSON object, i.e. a comma-separated list of name:value pairs,
75                                                 enclosed in curly braces, like this:
76                                                 <informalexample>
77                                                         <programlisting>
78         { "a":"frobozz", "b":24, "c":null }
79                                                         </programlisting>
80                                                 </informalexample>
81                                         </para>
82                                 </listitem>
83
84                                 <listitem>
85                                         <para> An "array" is a JSON array, i.e. a comma-separated list of values, enclosed
86                                                 in square brackets, like this:
87                                                 <informalexample>
88                                                         <programlisting>
89         [ "Goober", 629, null, false, "glub" ]
90                                                         </programlisting>
91                                                 </informalexample>
92                                         </para>
93                                 </listitem>
94
95                         </itemizedlist>
96
97                 </sect3>
98
99                 <sect3>
100                         <title>The Examples</title>
101                         <para> The test_json_query utility generated the SQL for all of the sample queries in this
102                                 tutorial.  Newlines and indentation were then inserted manually for readability. </para>
103                         <para> All examples involve the actor.org_unit table, sometimes in combination with a
104                                 few related tables.  The queries themselves are designed to illustrate the syntax, not
105                                 to do anything useful at the application level.  For example, it's not meaningful to
106                                 take the square root of an org_unit id, except to illustrate how to code a function call.
107                                 The examples are like department store mannequins -- they have no brains, they're only
108                                 for display. </para>
109                 </sect3>
110
111         </sect2>
112
113         <sect2>
114                 <title>Hello, World!</title>
115
116                 <para> The simplest kind of query defines nothing but a FROM clause.  For example: </para>
117                 <informalexample>
118                         <programlisting language="JSON">
119
120         {
121             "from":"aou"
122         }
123
124                         </programlisting>
125                 </informalexample>
126                 <para> In this minimal example we select from only one table.  Later we will see how to join
127                         multiple tables. </para>
128                 <para> Since we don't supply a WHERE clause, json_query constructs a default WHERE clause for
129                         us, including all the available columns.  The resulting SQL looks like this: </para>
130                 <informalexample>
131                         <programlisting language="SQL">
132         SELECT
133             "aou".billing_address AS "billing_address",
134             "aou".holds_address   AS "holds_address",
135             "aou".id              AS "id",
136             "aou".ill_address     AS "ill_address",
137             "aou".mailing_address AS "mailing_address",
138             "aou".name            AS "name",
139             "aou".ou_type         AS "ou_type",
140             "aou".parent_ou       AS "parent_ou",
141             "aou".shortname       AS "shortname",
142             "aou".email           AS "email",
143             "aou".phone           AS "phone",
144             "aou".opac_visible    AS "opac_visible"
145         FROM
146             actor.org_unit        AS "aou" ;
147                         </programlisting>
148                 </informalexample>
149
150                 <sect3>
151                         <title>Default SELECT Clauses</title>
152                         <para> The default SELECT clause includes every column that the IDL defines it as a
153                                 non-virtual field for the class in question.  If a column is present in the database
154                                 but not defined in the IDL, json_query doesn't know about it.  In the case of the
155                                 example shown above, all the columns are defined in the IDL, so they all show up in
156                                 the default SELECT clause. </para>
157                         <para> If the FROM clause joins two or more tables, the default SELECT clause includes
158                                 columns only from the core table, not from any of the joined tables. </para>
159                         <para> The default SELECT clause has almost the same effect as "<literal>SELECT *</literal>",
160                                 but not exactly.  If you were to "<literal>SELECT * from actor.org_unit_type</literal>
161                                 in psql, the output would include all the same columns as in the example above, but not in
162                                 the same order.  A default SELECT clause includes the columns in the order in which the IDL
163                                 defines them, which may be different from the order in which the database defines them. </para>
164                         <para> In practice, the sequencing of columns in the SELECT clause is not significant.
165                                 The result set is returned to the client program in the form of a data structure, which
166                                 the client program can navigate however it chooses. </para>
167                 </sect3>
168
169                 <sect3>
170                         <title>Other Lessons</title>
171                         <para> There are other ways to get a default SELECT clause.  However, default SELECT clauses
172                                 are a distraction at this point, because most of the time you'll specify your own SELECT
173                                 clause explicitly, as we will discuss later. </para>
174                         <para> Let's consider some more important aspects of this simple example -- more important
175                                 because they apply to more complex queries as well. </para>
176
177                         <itemizedlist>
178
179                                 <listitem>
180                                         <para> The entire JSON query is an object.  In this simple case the object includes
181                                                 only one entry, for the FROM clause.  Typically you'll also have entries for
182                                                 the SELECT clause and the WHERE clause, and possibly for HAVING, ORDER BY,
183                                                 LIMIT, or OFFSET clauses.  There is no separate entry for a GROUP BY clause,
184                                                 which you can specify by other means. </para>
185                                 </listitem>
186
187                                 <listitem>
188                                         <para> Although all the other entries are optional, you must include an entry for
189                                                 the FROM clause.  You cannot, for example, do a SELECT USER the way you can in
190                                                 psql. </para>
191                                 </listitem>
192
193                                 <listitem>
194                                         <para> Every column is qualified by an alias for the table.  This alias is always the
195                                                 class name for the table, as defined in the IDL. </para>
196                                 </listitem>
197
198                                 <listitem>
199                                         <para> Every column is aliased with the column name.  There is a way to choose a
200                                                 different column alias (not shown here). </para>
201                                 </listitem>
202
203                         </itemizedlist>
204
205                 </sect3>
206         </sect2>
207
208         <sect2>
209                 <title>The SELECT Clause</title>
210
211                 <para> The following variation also produces a default SELECT clause: </para>
212
213                 <informalexample>
214                         <programlisting language="JSON">
215         {
216             "from":"aou",
217                 "select": {
218                 "aou":"*"
219             }
220         }
221                         </programlisting>
222                 </informalexample>
223
224                 <para> ...and so does this one: </para>
225
226                 <informalexample>
227                         <programlisting language="JSON">
228         {
229             "select": {
230                  "aou":null
231             },
232             "from":"aou"
233         }
234                         </programlisting>
235                 </informalexample>
236
237                 <para> While this syntax may not be terribly useful, it does illustrate the minimal structure
238                         of a SELECT clause in a JSON query: an entry in the outermost JSON object, with a key of
239                         <literal>"select"</literal>.  The value associated with this key is another JSON object,
240                         whose keys are class names. </para>
241                 <para> (These two examples also illustrate another point: unlike SQL, a JSON query doesn't care
242                         whether the FROM clause or the SELECT clause comes first.) </para>
243                 <para> Usually you don't want the default SELECT clause.  Here's how to select only some of the
244                         columns: </para>
245
246                 <informalexample>
247                         <programlisting language="JSON">
248         {
249             "from":"aou",
250             "select": {
251                 "aou":[ "id", "name" ]
252             }
253         }
254                         </programlisting>
255                 </informalexample>
256
257                 <para> The value associated with the class name is an array of column names.  If you select
258                         columns from multiple tables (not shown here), you'll need a separate entry for each
259                         table, and a separate column list for each entry. </para>
260                 <para> The previous example results in the following SQL: </para>
261
262                 <informalexample>
263                         <programlisting language="SQL">
264         SELECT
265             "aou".id       AS "id",
266             "aou".name     AS "name"
267         FROM
268             actor.org_unit AS "aou" ;
269                         </programlisting>
270                 </informalexample>
271
272                 <sect3>
273                         <title>Fancier SELECT Clauses</title>
274                         <para> The previous example featured an array of column names.  More generally, it
275                                 featured an array of field specifications, and one kind of field specification
276                                 is a column name.  The other kind is a JSON object, with some combination of the
277                                 following keys: </para>
278
279                         <itemizedlist>
280
281                                 <listitem>
282                                         <para> <literal>"column"</literal> -- the column name (required). </para>
283                                 </listitem>
284
285                                 <listitem>
286                                         <para> <literal>"alias"</literal> -- used to define a column alias, which
287                                                 otherwise defaults to the column name. </para>
288                                 </listitem>
289
290                                 <listitem>
291                                         <para> <literal>"aggregate"</literal> -- takes a value of
292                                                 <literal>true</literal> or <literal>false.</literal>  Don't worry about
293                                                 this one yet.  It concerns the use of GROUP BY clauses, which we will
294                                                 examine later. </para>
295                                 </listitem>
296
297                                 <listitem>
298                                         <para> <literal>"transform"</literal> -- the name of an SQL function to be
299                                                 called. </para>
300                                 </listitem>
301
302                                 <listitem>
303                                         <para> <literal>"result_field"</literal> -- used with
304                                                 <literal>"transform"</literal>; specifies an output column of a function that
305                                                 returns multiple columns at a time. </para>
306                                 </listitem>
307
308                                 <listitem>
309                                         <para> <literal>"params"</literal> -- used with <literal>"transform"</literal>;
310                                                 provides a list of parameters for the function.  They may be strings, numbers,
311                                                 or nulls. </para>
312                                 </listitem>
313
314                         </itemizedlist>
315
316                         <para> This example assigns a different column alias: </para>
317
318                         <informalexample>
319                                 <programlisting language="JSON">
320         {
321             "from":"aou",
322             "select": {
323                 "aou": [
324                     "id",
325                     { "column":"name", "alias":"org_name" }
326                 ]
327             }
328         }
329                                 </programlisting>
330                         </informalexample>
331
332                         <informalexample>
333                                 <programlisting language="SQL">
334         SELECT
335             "aou".id AS "id",
336             "aou".name AS "org_name"
337         FROM
338             actor.org_unit AS "aou" ;
339                                 </programlisting>
340                         </informalexample>
341
342                         <para> In this case, changing the column alias doesn't accomplish much.  But if we
343                                 were joining to the actor.org_unit_type table, which also has a "name" column,
344                                 we could use different aliases to distinguish them. </para>
345                         <para> The following example uses a function to raise a column to upper case: </para>
346
347                         <informalexample>
348                                 <programlisting language="JSON">
349         {
350             "from":"aou",
351             "select": {
352                 "aou": [
353                     "id",
354                     { "column":"name", "transform":"upper" }
355                 ]
356             }
357         }
358                                 </programlisting>
359                         </informalexample>
360
361                         <informalexample>
362                                 <programlisting language="SQL">
363         SELECT
364             "aou".id           AS "id",
365             upper("aou".name ) AS "name"
366         FROM
367             actor.org_unit     AS "aou" ;
368                                 </programlisting>
369                         </informalexample>
370
371                         <para> Here we take a substring of the name, using the <literal>"params"</literal>
372                                 element to pass parameters: </para>
373
374                         <informalexample>
375                                 <programlisting language="JSON">
376         {
377             "from":"aou",
378             "select": {
379                 "aou": [
380                     "id", {
381                         "column":"name",
382                         "transform":"substr",
383                         "params":[ 3, 5 ]
384                     }
385                 ]
386             }
387         }
388                                 </programlisting>
389                         </informalexample>
390
391                         <informalexample>
392                                 <programlisting language="SQL">
393         SELECT
394             "aou".id AS "id",
395             substr("aou".name,'3','5' ) AS "name"
396         FROM
397             actor.org_unit AS "aou" ;
398                                 </programlisting>
399                         </informalexample>
400
401                         <para> The parameters specified with <literal>"params"</literal> are inserted
402                                 <emphasis>after</emphasis> the applicable column (<literal>"name"</literal> in this
403                                 case), which is always the first parameter.  They are always passed as strings,
404                                 i.e. enclosed in quotes, even if the JSON expresses them as numbers.  PostgreSQL
405                                 will ordinarily coerce them to the right type.  However if the function name is
406                                 overloaded to accept different types, PostgreSQL may invoke a function other than
407                                 the one intended. </para>
408                         <para> Finally we call a fictitious function <literal>"frobozz"</literal> that returns
409                                 multiple columns, where we want only one of them: </para>
410
411                         <informalexample>
412                                 <programlisting language="JSON">
413         {
414             "from":"aou",
415             "select": {
416                 "aou": [
417                     "id", {
418                         "column":"name",
419                         "transform":"frobozz",
420                         "result_field":"zamzam"
421                     }
422                 ]
423             }
424         }
425                                 </programlisting>
426                         </informalexample>
427
428                         <informalexample>
429                                 <programlisting language="SQL">
430         SELECT
431             "aou".id                        AS "id",
432             (frobozz("aou".name ))."zamzam" AS "name"
433         FROM
434             actor.org_unit                  AS "aou" ;
435                                 </programlisting>
436                         </informalexample>
437
438                         <para> The "frobozz" function doesn't actually exist, but json_query doesn't know
439                                 that.  The query won't fail until json_query tries to execute it in the database. </para>
440                 </sect3>
441
442
443                 <sect3>
444                         <title>Things You Can't Do</title>
445                         <para> You can do some things in a SELECT clause with raw SQL (with psql, for example)
446                                 that you can't do with a JSON query.  Some of them matter and some of them don't. </para>
447                         <para> When you do a JOIN, you can't arrange the selected columns in any arbitrary
448                                 sequence, because all of the columns from a given table must be grouped together.
449                                 This limitation doesn't matter.  The results are returned in the form of a data
450                                 structure, which the client program can navigate however it likes. </para>
451                         <para> You can't select an arbitrary expression, such as
452                                 <literal>"percentage / 100"</literal> or <literal>"last_name || ', ' || first_name"</literal>.
453                                 Most of the time this limitation doesn't matter either, because the client program
454                                 can do these kinds of manipulations for itself.  However, function calls may be a problem.
455                                 You can't nest them, and you can't pass more than one column value to them (and it has
456                                 to be the first parameter). </para>
457                         <para> You can't use a CASE expression.  Instead, the client application can do the equivalent
458                                 branching for itself. </para>
459                         <para> You can't select a subquery.  In raw SQL you can do something like the following: </para>
460
461                         <informalexample>
462                                 <programlisting language="SQL">
463         SELECT
464             id,
465             name,
466             (
467                 SELECT name
468                 FROM actor.org_unit_type AS aout
469                 WHERE aout.id = aou.ou_type
470             ) AS type_name
471         FROM
472             actor.org_unit AS aou;
473                                 </programlisting>
474                         </informalexample>
475
476                         <para> This contrived example is not very realistic.  Normally you would use a JOIN in this
477                                 case, and that's what you should do in a JSON query.  Other cases may not be so easy
478                                 to solve. </para>
479                 </sect3>
480
481         </sect2>
482
483         <sect2>
484                 <title>The WHERE Clause</title>
485                 <para> Most queries need a WHERE clause, as in this simple example: </para>
486
487                 <informalexample>
488                         <programlisting language="JSON">
489         {
490             "from":"aou",
491             "select": { "aou":[ "id", "name" ] },
492             "where": {
493                 "parent_ou":"3"
494             }
495         }
496                         </programlisting>
497                 </informalexample>
498
499                 <para> Like the SELECT clause, the WHERE clause gets its own entry in the top-level object
500                         of a JSON query.  The key is <literal>"where"</literal>, and the associated value is
501                         either an object (as shown here) or an array (to be discussed a bit later).  Each entry
502                         in the object is a separate condition. </para>
503                 <para> In this case, we use a special shortcut for expressing an equality condition.  The
504                         column name is on the left of the colon, and the value to which we are equating it is
505                         on the right. </para>
506                 <para> Here's the resulting SQL: </para>
507
508                 <informalexample>
509                         <programlisting language="SQL">
510         SELECT
511             "aou".id       AS "id",
512             "aou".name     AS "name"
513         FROM
514             actor.org_unit AS "aou"
515         WHERE
516             "aou".parent_ou = 3;
517                         </programlisting>
518                 </informalexample>
519
520                 <para> Like the SELECT clause, the generated WHERE clause qualifies each column name with
521                         the alias of the relevant table. </para>
522                 <para> If you want to compare a column to NULL, put "<literal>null</literal>" (without
523                         quotation marks) to the right of the colon instead of a literal value.  The resulting
524                         SQL will include <literal>"IS NULL"</literal> instead of an equals sign. </para>
525
526                 <sect3>
527                         <title>Other Kinds of Comparisons</title>
528                         <para> Here's the same query (which generates the same SQL) without the special
529                                 shortcut: </para>
530
531                         <informalexample>
532                                 <programlisting language="JSON">
533         {
534             "from":"aou",
535             "select": { "aou":[ "id", "name" ] },
536             "where": {
537                 "parent_ou":{ "=":3 }
538             }
539         }
540                                 </programlisting>
541                         </informalexample>
542
543                         <para> We still have an entry whose key is the column name, but this time the
544                                 associated value is another JSON object.  It must contain exactly one entry,
545                                 with the comparison operator on the left of the colon, and the value to be
546                                 compared on the right. </para>
547                         <para> The same syntax works for other kinds of comparison operators.
548                                 For example: </para>
549
550                         <informalexample>
551                                 <programlisting language="JSON">
552         {
553             "from":"aou",
554             "select": { "aou":[ "id", "name" ] },
555             "where": {
556                 "parent_ou":{ "&gt;":3 }
557             }
558         }
559                                 </programlisting>
560                         </informalexample>
561
562                         <para> ...turns into: </para>
563
564                         <informalexample>
565                                 <programlisting language="SQL">
566         SELECT
567             "aou".id       AS "id",
568             "aou".name     AS "name"
569         FROM
570             actor.org_unit AS "aou"
571         WHERE
572             "aou".parent_ou > 3 ;
573                                 </programlisting>
574                         </informalexample>
575
576                         <para> The condition '<literal>"=":null</literal>' turns into IS NULL.  Any other
577                                 operator used with <literal>"null"</literal> turns into IS NOT NULL. </para>
578                         <para> You can use most of the comparison operators recognized by PostgreSQL: </para>
579
580                         <programlisting language="SQL">
581         =    &lt;&gt;   !=
582         &lt;    &gt;    &lt;=   &gt;=
583         ~    ~*   !~   !~*
584         like      ilike
585         similar to
586                         </programlisting>
587
588                         <para> The only ones you can't use are <literal>"is distinct from"</literal> and
589                                 <literal>"is not distinct from"</literal>. </para>
590                 </sect3>
591
592                 <sect3>
593                         <title>Custom Comparisons</title>
594                         <para> Here's a dirty little secret: json_query doesn't really pay much attention to the
595                                 operator you supply.  It merely checks to make sure that the operator doesn't contain
596                                 any semicolons or white space, in order to prevent certain kinds of SQL injection.
597                                 It also allows <literal>"similar to"</literal> as a special exception. </para>
598                         <para> As a result, you can slip an operator of your own devising into the SQL, so long as
599                                 it doesn't contain any semicolons or white space, and doesn't create invalid syntax.
600                                 Here's a contrived and rather silly example: </para>
601
602                         <informalexample>
603                                 <programlisting language="JSON">
604         {
605             "from":"aou",
606             "select": { "aou":[ "id", "name" ] },
607             "where": {
608                 "parent_ou":{ "&lt;2+":3 }
609             }
610         }
611                                 </programlisting>
612                         </informalexample>
613
614                         <para> ...which results in the following SQL: </para>
615
616                         <informalexample>
617                                 <programlisting language="SQL">
618         SELECT
619             "aou".id       AS "id",
620             "aou".name     AS "name"
621         FROM
622             actor.org_unit AS "aou"
623         WHERE
624             "aou".parent_ou &lt;2+ 3;
625                                 </programlisting>
626                         </informalexample>
627
628                         <para> It's hard to come up with a realistic case where this hack would be useful, but it
629                                 could happen. </para>
630                 </sect3>
631
632                 <sect3>
633                         <title>Comparing One Column to Another</title>
634                         <para> Here's how to put another column on the right hand side of a comparison: </para>
635
636                         <informalexample>
637                                 <programlisting language="JSON">
638         {
639             "from":"aou",
640             "select": { "aou":[ "id", "name" ] },
641             "where": {
642                 "id": { "&gt;": { "+aou":"parent_ou" } }
643             }
644         };
645                                 </programlisting>
646                         </informalexample>
647
648                         <para> This syntax is similar to the previous examples, except that instead of comparing
649                                 to a literal value, we compare to an object.  This object has only a single entry,
650                                 whose key is a table alias preceded by a leading plus sign.  The associated value is
651                                 the name of the column. </para>
652                         <para> Here's the resulting SQL: </para>
653
654                         <informalexample>
655                                 <programlisting language="SQL">
656         SELECT
657             "aou".id AS "id",
658             "aou".name AS "name"
659         FROM
660             actor.org_unit AS "aou"
661         WHERE
662             (
663                 "aou".id &gt; (  "aou".parent_ou  )
664             );
665                                 </programlisting>
666                         </informalexample>
667
668                         <para> The table alias must correspond to the appropriate table.  Since json_query doesn't
669                                 validate the choice of alias, it won't detect an invalid alias until it tries to
670                                 execute the query.  In this simple example there's only one table to choose from.  The
671                                 choice of alias is more important in a subquery or join. </para>
672                         <para> The leading plus sign, combined with a table alias, can be used in other situations
673                                 to designate the table to which a column belongs.  We shall defer a discussion of this
674                                 usage to the section on joins. </para>
675                 </sect3>
676
677                 <sect3>
678                         <title>Testing Boolean Columns</title>
679                         <para> In SQL, there are several ways to test a boolean column such as
680                                 actor.org_unit.opac_visible.  The most obvious way is to compare it to true or false: </para>
681
682                         <informalexample>
683                                 <programlisting language="SQL">
684         SELECT
685             id
686         FROM
687             actor.org_unit
688         WHERE
689             opac_visible = true;
690                                 </programlisting>
691                         </informalexample>
692
693                         <para> In a JSON query this approach doesn't work.  If you try it, the "= true" test will
694                                 turn into IS NULL.  Don't do that.  Instead, use a leading plus sign, as described in
695                                 the preceding section, to treat the boolean column as a stand-alone condition: </para>
696
697                         <informalexample>
698                                 <programlisting language="JSON">
699         {
700             "from":"aou",
701             "select": { "aou":[ "id" ] },
702             "where": {
703                 "+aou":"opac_visible"
704             }
705         }
706                                 </programlisting>
707                         </informalexample>
708
709                         <para> Result: </para>
710
711                         <informalexample>
712                                 <programlisting language="SQL">
713         SELECT
714             "aou".id AS "id"
715         FROM
716             actor.org_unit AS "aou"
717         WHERE
718             "aou".opac_visible ;
719                                 </programlisting>
720                         </informalexample>
721
722                         <para> If you need to test for falsity, then write a test for truth and negate it with the
723                                 <literal>"-not"</literal> operator.  We will discuss the "-not" operator later, but
724                                 here's a preview: </para>
725
726                         <informalexample>
727                                 <programlisting language="JSON">
728         {
729             "from":"aou",
730             "select": { "aou":[ "id" ] },
731             "where": {
732                 "-not": {
733                     "+aou":"opac_visible"
734                 }
735             }
736         }
737                                 </programlisting>
738                         </informalexample>
739
740                         <informalexample>
741                                 <programlisting language="SQL">
742         SELECT
743             "aou".id AS "id"
744         FROM
745             actor.org_unit AS "aou"
746         WHERE
747             NOT (  "aou".opac_visible  );
748                                 </programlisting>
749                         </informalexample>
750
751                         <para> You can also compare a boolean column directly to a more complex condition: </para>
752
753                         <informalexample>
754                                 <programlisting language="JSON">
755         {
756             "from":"aou",
757             "select": { "aou":[ "id" ] },
758             "where": {
759                 "opac_visible": {
760                     "=": { "parent_ou":{ "&gt;":3 } }
761                 }
762             }
763         }
764                                 </programlisting>
765                         </informalexample>
766
767                         <para> Here we compare a boolean column, not to a literal value, but to a boolean expression.
768                                 The resulting SQL looks a little goofy, but it works: </para>
769
770                         <informalexample>
771                                 <programlisting language="SQL">
772         SELECT
773             "aou".id AS "id"
774         FROM
775             actor.org_unit AS "aou"
776         WHERE
777             (
778                 "aou".opac_visible = ( "aou".parent_ou &gt; 3 )
779             );
780                                 </programlisting>
781                         </informalexample>
782
783                         <para> In this case we compare the boolean column to a single simple condition.  However you
784                                 can include additional complications -- multiple conditions, IN lists, BETWEEN clauses,
785                                 and other features as described below. </para>
786                 </sect3>
787
788                 <sect3>
789                         <title>Multiple Conditions</title>
790                         <para> If you need multiple conditions, just add them to the <literal>"where"</literal>
791                                 object, separated by commas: </para>
792
793                         <informalexample>
794                                 <programlisting language="JSON">
795         {
796                 "from":"aou",
797                 "select": { "aou":[ "id", "name" ] },
798                 "where": {
799                         "parent_ou":{ "&gt;":3 },
800                         "id":{ "&lt;&gt;":7 }
801                 }
802         }
803                                 </programlisting>
804                         </informalexample>
805
806                         <para> The generated SQL connects the conditions with AND: </para>
807
808                         <informalexample>
809                                 <programlisting language="SQL">
810         SELECT
811             "aou".id       AS "id",
812             "aou".name     AS "name"
813         FROM
814             actor.org_unit AS "aou"
815         WHERE
816             "aou".parent_ou &gt; 3
817             AND "aou".id &lt;&gt; 7;
818                                 </programlisting>
819                         </informalexample>
820
821                         <para> Later we will see how to use OR instead of AND. </para>
822                 </sect3>
823
824                 <sect3>
825                         <title>Using Arrays</title>
826                         <para> Here's a puzzler.  Suppose you need two conditions for the same column.  How do
827                                 you code them in the same WHERE clause?  For example, suppose you want something
828                                 like this: </para>
829
830                         <informalexample>
831                                 <programlisting language="SQL">
832         SELECT
833             id,
834             name
835         FROM
836             actor.org_unit
837         WHERE
838             parent_ou &gt; 3
839             AND parent_ou &lt;&gt; 7;
840                                 </programlisting>
841                         </informalexample>
842
843                         <para> You might try a WHERE clause like this: </para>
844
845                         <informalexample>
846                                 <programlisting language="JSON">
847             "where": {
848                 "parent_ou":{ "&gt;":3 },
849                 "parent_ou":{ "&lt;&gt;":7 }
850             }
851                                 </programlisting>
852                         </informalexample>
853
854                         <para> Nope.  Won't work.  According to JSON rules, two entries in the same object
855                                 can't have the same key. </para>
856                         <para> After slapping yourself in the forehead, you try something a little smarter: </para>
857
858                         <informalexample>
859                                 <programlisting language="JSON">
860             "where": {
861                 "parent_ou": {
862                     "&gt;":3,
863                     "&lt;&gt;":7
864                 }
865             }
866                                 </programlisting>
867                         </informalexample>
868
869                         <para> Nice try, but that doesn't work either.  Maybe it ought to work -- at least it's
870                                 legal JSON -- but, no. </para>
871                         <para> Here's what works: </para>
872
873                         <informalexample>
874                                 <programlisting language="JSON">
875         {
876             "from":"aou",
877             "select": { "aou":[ "id", "name" ] },
878             "where": [
879                 { "parent_ou":{ "&gt;":3 } },
880                 { "parent_ou":{ "&lt;&gt;":7 } }
881             ]
882         }
883                                 </programlisting>
884                         </informalexample>
885
886                         <para> We wrapped the two conditions into two separate JSON objects, and then wrapped
887                                 those objects together into a JSON array.  The resulting SQL looks like this: </para>
888
889                         <informalexample>
890                                 <programlisting language="SQL">
891         SELECT
892             "aou".id       AS "id",
893             "aou".name     AS "name
894         FROM
895             actor.org_unit AS "aou"
896         WHERE
897             ( "aou".parent_ou &gt; 3 )
898             AND
899             ( "aou".parent_ou &lt;&gt; 7 );
900                                 </programlisting>
901                         </informalexample>
902
903                         <para> That's not quite what we were hoping for, because the extra parentheses are so ugly.
904                                 But they're harmless.  This will do. </para>
905                         <para> If you're in the mood, you can use arrays to as many parentheses as
906                                 you like, even if there is only one condition inside: </para>
907
908                         <informalexample>
909                                 <programlisting language="JSON">
910         {
911             "from":"aou",
912             "select": { "aou":[ "id", "name" ] },
913             "where":
914             [[[[[[
915                   {
916                       "parent_ou":{ "&gt;":3 }
917                   },
918             ]]]]]]
919         }
920                                 </programlisting>
921                         </informalexample>
922
923                         <para> ...yields: </para>
924
925                         <informalexample>
926                                 <programlisting language="SQL">
927         SELECT
928             "aou".id       AS "id",
929             "aou".name     AS "name"
930         FROM
931             actor.org_unit AS "aou"
932         WHERE
933             ( ( ( ( ( ( "aou".parent_ou &gt; 3 ) ) ) ) ) );
934                                 </programlisting>
935                         </informalexample>
936
937                 </sect3>
938
939                 <sect3>
940                         <title>How to OR</title>
941                         <para> By default, json_query combines conditions with AND.  When you need OR,
942                                 here's how to do it: </para>
943
944                         <informalexample>
945                                 <programlisting language="JSON">
946         {
947             "from":"aou",
948             "select": { "aou":[ "id", "name" ] },
949             "where": {
950                 "-or": {
951                     "id":2,
952                     "parent_ou":3
953                 }
954             }
955         }
956                                 </programlisting>
957                         </informalexample>
958
959                         <para> We use <literal>"-or"</literal> as the key, with the conditions to be ORed in an
960                                 associated object.  The leading minus sign is there to make sure that the operator
961                                 isn't confused with a column name.  Later we'll see some other operators with leading
962                                 minus signs.  In a couple of spots we even use plus signs. </para>
963                         <para> Here are the results from the above example: </para>
964
965                         <informalexample>
966                                 <programlisting language="SQL">
967         SELECT
968             "aou".id AS "id",
969             "aou".name AS "name"
970         FROM
971             actor.org_unit AS "aou"
972         WHERE
973             (
974                 "aou".id = 2
975                 OR "aou".parent_ou = 3
976             );
977                                 </programlisting>
978                         </informalexample>
979
980                         <para> The conditions paired with <literal>"-or"</literal> are linked by OR and enclosed
981                                 in parentheses, </para>
982                         <para> Here's how to do the same thing using an array, except that it produces an extra
983                                 layer of parentheses: </para>
984
985                         <informalexample>
986                                 <programlisting language="JSON">
987         {
988             "from":"aou",
989             "select": { "aou":[ "id", "name" ] },
990             "where": {
991                 "-or": [
992                     { "id":2 },
993                     { "parent_ou":3 }
994                 ]
995             }
996         }
997                                 </programlisting>
998                         </informalexample>
999
1000                         <informalexample>
1001                                 <programlisting language="SQL">
1002         SELECT
1003             "aou".id AS "id",
1004             "aou".name AS "name"
1005         FROM
1006             actor.org_unit AS "aou"
1007         WHERE
1008             (
1009                 ( "aou".id = 2 )
1010                 OR ( "aou".parent_ou = 3 )
1011             );
1012                                 </programlisting>
1013                         </informalexample>
1014
1015                         <para> It's possible, though not very useful, to have only a single condition subject to
1016                                 the <literal>"-or"</literal> operator.  In that case, the condition appears by itself,
1017                                 since there's nothing to OR it to.  This trick is another way to add an extraneous
1018                                 layer of parentheses, </para>
1019                 </sect3>
1020
1021                 <sect3>
1022                         <title>Another way to AND</title>
1023                         <para> You can also use the <literal>"-and"</literal> operator.  It works just like
1024                                 <literal>"-or"</literal>, except that it combines conditions with AND instead of OR.
1025                                 Since AND is the default, we don't usually need a separate operator for it, but it's
1026                                 available.
1027                         </para>
1028                         <para>
1029                                 In rare cases, nothing else will do -- you can't include two conditions in the same
1030                                 list because of the duplicate key problem, but you can't combine them with arrays
1031                                 either.  In particular, you might need to combine them within an expression that
1032                                 you're comparing to a boolean column (see the subsection above on Testing Boolean
1033                                 Columns). </para>
1034                 </sect3>
1035
1036                 <sect3>
1037                         <title>Negation with NOT</title>
1038                         <para> The <literal>"-not"</literal> operator negates a condition or set of conditions.
1039                                 For example: </para>
1040
1041                         <informalexample>
1042                                 <programlisting language="JSON">
1043         {
1044             "from":"aou",
1045             "select": { "aou":[ "id", "name" ] },
1046             "where": {
1047                 "-not": {
1048                     "id":{ "&gt;":2 },
1049                     "parent_ou":3
1050                 }
1051             }
1052         }
1053                                 </programlisting>
1054                         </informalexample>
1055
1056                         <informalexample>
1057                                 <programlisting language="SQL">
1058         SELECT
1059             "aou".id AS "id",
1060             "aou".name AS "name"
1061         FROM
1062             actor.org_unit AS "aou"
1063         WHERE
1064             NOT
1065             (
1066                 "aou".id &gt; 2
1067                 AND "aou".parent_ou = 3
1068             );
1069                                 </programlisting>
1070                         </informalexample>
1071
1072                         <para> In this example we merely negate a combination of two comparisons.  However the
1073                                 condition to be negated may be as complicated as it needs to be.  Anything that can
1074                                 be subject to <literal>"where"</literal> can be subject to
1075                                 <literal>"-not"</literal>. </para>
1076                         <para> In most cases you can achieve the same result by other means.  However the
1077                                 <literal>"-not"</literal> operator is the only way to represent NOT BETWEEN
1078                                 (to be discussed later). </para>
1079                 </sect3>
1080
1081                 <sect3>
1082                         <title>EXISTS with Subqueries</title>
1083                         <para> Two other operators carry a leading minus sign: <literal>"-exists"</literal>
1084                                 and its negation <literal>"-not-exists"</literal>.  These operators apply to
1085                                 subqueries, which have the same format as a full query.  For example: </para>
1086
1087                         <informalexample>
1088                                 <programlisting language="JSON">
1089         {
1090             "from":"aou",
1091             "select": { "aou":[ "id", "name" ] },
1092             "where": {
1093                 "-exists": {
1094                     "from":"asv",
1095                     "select":{ "asv":[ "id" ] },
1096                     "where": {
1097                         "owner":7
1098                     }
1099                 }
1100             }
1101         }
1102                                 </programlisting>
1103                         </informalexample>
1104
1105                         <informalexample>
1106                                 <programlisting language="SQL">
1107         SELECT
1108             "aou".id AS "id",
1109             "aou".name AS "name"
1110         FROM
1111             actor.org_unit AS "aou"
1112         WHERE
1113             EXISTS
1114             (
1115                 SELECT "asv".id AS "id"
1116                 FROM action.survey AS "asv"
1117                 WHERE "asv".owner = 7
1118             );
1119                                 </programlisting>
1120                         </informalexample>
1121
1122                         <para> This kind of subquery is of limited use, because its WHERE clause doesn't
1123                                 have anything to do with the main query.  It just shuts down the main query
1124                                 altogether if it isn't satisfied. </para>
1125                         <para> More typical is a correlated subquery, whose WHERE clause refers to a row
1126                                 from the main query.  For example: </para>
1127
1128                         <informalexample>
1129                                 <programlisting language="JSON">
1130         {
1131             "from":"aou",
1132             "select": { "aou":[ "id", "name" ] },
1133             "where": {
1134                 "-exists": {
1135                     "from":"asv",
1136                     "select":{ "asv":[ "id" ] },
1137                     "where": {
1138                         "owner":{ "=":{ "+aou":"id" }}
1139                     }
1140                 }
1141             }
1142         }
1143                                 </programlisting>
1144                         </informalexample>
1145
1146                         <para> Note the use of <literal>"+aou"</literal> to qualify the id column in the
1147                                 inner WHERE clause. </para>
1148
1149                         <informalexample>
1150                                 <programlisting language="SQL">
1151         SELECT
1152             "aou".id AS "id",
1153             "aou".name AS "name"
1154         FROM
1155             actor.org_unit AS "aou"
1156         WHERE
1157             EXISTS
1158             (
1159                 SELECT  "asv".id AS "id"
1160                 FROM action.survey AS "asv"
1161                 WHERE ("asv".owner = (  "aou".id  ))
1162             );
1163                                 </programlisting>
1164                         </informalexample>
1165
1166                         <para> This latter example illustrates the syntax, but in practice, it would
1167                                 probably be more natural to use an IN clause with a subquery (to be discussed
1168                                 later). </para>
1169                 </sect3>
1170
1171                 <sect3>
1172                         <title>BETWEEN Clauses</title>
1173                         <para> Here's how to express a BETWEEN clause: </para>
1174
1175                         <informalexample>
1176                                 <programlisting language="JSON">
1177         {
1178             "from":"aou",
1179             "select": { "aou":[ "id" ] },
1180             "where": {
1181                 "parent_ou": { "between":[ 3, 7 ] }
1182             }
1183         }
1184                                 </programlisting>
1185                         </informalexample>
1186
1187                         <para> The value associated with the column name is an object with a single
1188                                 entry, whose key is <literal>"between"</literal>.  The corresponding
1189                                 value is an array with exactly two values, defining the range to be
1190                                 tested. </para>
1191                         <para> The range bounds must be either numbers or string literals.  Although
1192                                 SQL allows them to be null, a null doesn't make sense in this context,
1193                                 because a null never matches anything.  Consequently json_query doesn't
1194                                 allow them. </para>
1195                         <para> The resulting SQL is just what you would expect: </para>
1196
1197                         <informalexample>
1198                                 <programlisting language="SQL">
1199         SELECT
1200             "aou".id AS "id"
1201         FROM
1202             actor.org_unit AS "aou"
1203         WHERE
1204             parent_ou BETWEEN '3' AND '7';
1205                                 </programlisting>
1206                         </informalexample>
1207
1208                 </sect3>
1209
1210                 <sect3>
1211                         <title>IN and NOT IN Lists</title>
1212                         <para> There are two ways to code an IN list.  One way is simply to include
1213                                 the list of values in an array: </para>
1214
1215                         <informalexample>
1216                                 <programlisting language="JSON">
1217         {
1218             "from":"aou",
1219             "select": { "aou":[ "id", "name" ] },
1220             "where": {
1221                 "parent_ou": [ 3, 5, 7 ]
1222             }
1223         }
1224                                 </programlisting>
1225                         </informalexample>
1226
1227                         <para> As with a BETWEEN clause, the values in the array must be numbers or
1228                                 string literals.  Nulls aren't allowed.  Here's the resulting SQL, which
1229                                 again is just what you would expect: </para>
1230
1231                         <informalexample>
1232                                 <programlisting language="SQL">
1233         SELECT
1234             "aou".id AS "id",
1235             "aou".name AS "name"
1236         FROM
1237             actor.org_unit AS "aou"
1238         WHERE
1239             "aou".parent_ou IN (3, 5, 7);
1240                                 </programlisting>
1241                         </informalexample>
1242
1243                         <para> The other way is similar to the syntax shown above for a BETWEEN clause,
1244                                 except that the array may include any non-zero number of values: </para>
1245
1246                         <informalexample>
1247                                 <programlisting language="JSON">
1248         {
1249             "from":"aou",
1250             "select": { "aou":[ "id", "name" ] },
1251             "where": {
1252                 "parent_ou": { "in": [ 3, 5, 7 ] }
1253             }
1254         }
1255                                 </programlisting>
1256                         </informalexample>
1257
1258                         <para> This version results in the same SQL as the first one. </para>
1259                         <para> For a NOT IN list, you can use the latter format, using the
1260                                 <literal>"not in"</literal> operator instead of <literal>"in"</literal>.
1261                                 Alternatively, you can use either format together with the
1262                                 <literal>"-not"</literal> operator. </para>
1263                 </sect3>
1264
1265                 <sect3>
1266                         <title>IN and NOT IN Clauses with Subqueries</title>
1267                         <para> For an IN clause with a subquery, the syntax is similar to the second
1268                                 of the two formats for an IN list (see the previous subsection).  The
1269                                 <literal>"in"</literal> or <literal>"not in"</literal> operator is paired,
1270                                 not with an array of values, but with an object representing the subquery.
1271                                 For example: </para>
1272
1273                         <informalexample>
1274                                 <programlisting language="JSON">
1275         {
1276             "from":"aou",
1277             "select": { "aou":[ "id", "name" ] },
1278             "where": {
1279                 "id": {
1280                     "in": {
1281                         "from":"asv",
1282                         "select":{ "asv":[ "owner" ] },
1283                         "where":{ "name":"Voter Registration" }
1284                     }
1285                 }
1286             }
1287         }
1288                                 </programlisting>
1289                         </informalexample>
1290
1291                         <para> The results: </para>
1292
1293                         <informalexample>
1294                                 <programlisting language="SQL">
1295         SELECT
1296             "aou".id AS "id",
1297             "aou".name AS "name"
1298         FROM
1299             actor.org_unit AS "aou"
1300         WHERE
1301             "aou".id IN
1302             (
1303                 SELECT
1304                     "asv".owner AS "owner"
1305                 FROM
1306                     action.survey AS "asv"
1307                 WHERE
1308                     "asv".name = 'Voter Registration'
1309             );
1310                                 </programlisting>
1311                         </informalexample>
1312
1313                         <para> In SQL the subquery may select multiple columns, but in a JSON query it
1314                                 can select only a single column. </para>
1315                         <para> For a NOT IN clause with a subquery, use the <literal>"not in"</literal>
1316                                 operator instead of <literal>"in"</literal>. </para>
1317                 </sect3>
1318
1319                 <sect3>
1320                         <title>Comparing to a Function</title>
1321                         <para> Here's how to compare a column to a function call: </para>
1322
1323                         <informalexample>
1324                                 <programlisting language="JSON">
1325         {
1326             "from":"aou",
1327             "select": { "aou":[ "id", "name" ] },
1328             "where": {
1329                 "id":{ ">":[ "sqrt", 16 ] }
1330             }
1331         }
1332                                 </programlisting>
1333                         </informalexample>
1334
1335                         <para> A comparison operator (<literal>"&gt;"</literal> in this case) is paired
1336                                 with an array.  The first entry in the array must be a string giving the name
1337                                 of the function.  The remaining parameters, if any, are the parameters.  They
1338                                 may be strings, numbers, or nulls.  The resulting SQL for this example: </para>
1339
1340                         <informalexample>
1341                                 <programlisting language="SQL">
1342         SELECT
1343             "aou".id AS "id",
1344             "aou".name AS "name"
1345         FROM
1346             actor.org_unit AS "aou"
1347         WHERE
1348             "aou".id &gt; sqrt( '16' );
1349                                 </programlisting>
1350                         </informalexample>
1351
1352                         <para> All parameters are passed as quoted strings -- even if, as in this case,
1353                                 they are really numbers. </para>
1354                         <para> This syntax  is somewhat limited in that the function parameters must be
1355                                 constants (hence the use of a silly example). </para>
1356                 </sect3>
1357
1358                 <sect3>
1359                         <title>Putting a Function Call on the Left</title>
1360                         <para> In the discussion of the SELECT clause, we saw how you could transform the value
1361                                 of a selected column by passing it to a function.  In the WHERE clause, you can use
1362                                 similar syntax to transform the value of a column before comparing it to something
1363                                 else. </para>
1364                         <para> For example: </para>
1365
1366                         <informalexample>
1367                                 <programlisting language="JSON">
1368         {
1369             "from":"aou",
1370             "select": { "aou":[ "id", "name" ] },
1371             "where": {
1372                 "name": {
1373                     "=": {
1374                         "transform":"upper",
1375                         "value":"CARTER BRANCH"
1376                     }
1377                 }
1378             }
1379         }
1380                                 </programlisting>
1381                         </informalexample>
1382
1383                         <para> The <literal>"transform"</literal> entry gives the name of the function that we
1384                                 will use on the left side of the comparison.  The <literal>"value"</literal> entry
1385                                 designates the value on the right side of the comparison. </para>
1386
1387                         <informalexample>
1388                                 <programlisting language="SQL">
1389         SELECT
1390             "aou".id AS "id",
1391             "aou".name AS "name"
1392         FROM
1393             actor.org_unit AS "aou"
1394         WHERE
1395             upper("aou".name ) =  'CARTER BRANCH' ;
1396                                 </programlisting>
1397                         </informalexample>
1398
1399                         <para> As in the SELECT clause, you can pass literal values or nulls to the  function
1400                                 as additional parameters by using an array tagged as
1401                                 <literal>"params"</literal>: </para>
1402
1403                         <informalexample>
1404                                 <programlisting language="JSON">
1405         {
1406             "from":"aou",
1407             "select": { "aou":[ "id", "name" ] },
1408             "where": {
1409                 "name": {
1410                     "=": {
1411                         "transform":"substr",
1412                         "params":[ 1, 6 ],
1413                         "value":"CARTER"
1414                     }
1415                 }
1416             }
1417         }
1418                                 </programlisting>
1419                         </informalexample>
1420
1421                         <informalexample>
1422                                 <programlisting language="SQL">
1423         SELECT
1424             "aou".id AS "id",
1425             "aou".name AS "name"
1426         FROM
1427             actor.org_unit AS "aou"
1428         WHERE
1429             substr("aou".name,'1','6' ) =  'CARTER' ;
1430                                 </programlisting>
1431                         </informalexample>
1432
1433                         <para> The first parameter is always the column name, qualified by the class name,
1434                                 followed by any additional parameters (which are always enclosed in quotes even
1435                                 if they are numeric). </para>
1436                         <para> As in the SELECT clause: if the function returns multiple columns, you can specify
1437                                 the one you want by using a <literal>"result_field"</literal> entry (not shown
1438                                 here). </para>
1439                         <para> If you leave out the <literal>"transform"</literal> entry (or misspell it), the
1440                                 column name will appear on the left without any function call.  This syntax works,
1441                                 but it's more complicated than it needs to be. </para>
1442                 </sect3>
1443
1444                 <sect3>
1445                         <title>Putting Function Calls on Both Sides</title>
1446                         <para> If you want to compare one function call to another, you can use the same syntax
1447                                 shown in the previous subsection -- except that the <literal>"value"</literal> entry
1448                                 carries an array instead of a literal value.  For example: </para>
1449
1450                         <informalexample>
1451                                 <programlisting language="JSON">
1452         {
1453             "from":"aou",
1454             "select": { "aou":[ "id", "name" ] },
1455             "where": {
1456                 "id": {
1457                     "&gt;": {
1458                         "transform":"factorial",
1459                         "value":[ "sqrt", 1000 ]
1460                     }
1461                 }
1462             }
1463         }
1464                                 </programlisting>
1465                         </informalexample>
1466
1467                         <informalexample>
1468                                 <programlisting language="SQL">
1469         SELECT
1470             "aou".id AS "id",
1471             "aou".name AS "name"
1472         FROM
1473             actor.org_unit AS "aou"
1474         WHERE
1475             factorial("aou".id ) &gt;  sqrt( '1000' ) ;
1476                                 </programlisting>
1477                         </informalexample>
1478
1479                         <para> The format for the right side function is similar to what we saw earlier, in the
1480                                 subsection Comparing to a Function.  Note that there are two different formats for
1481                                 defining function calls: </para>
1482
1483                         <itemizedlist>
1484
1485                                 <listitem>
1486                                         <para> For a function call to the left of the comparison, the function name is
1487                                                 tagged as <literal>"transform"</literal>.  The first parameter is always the
1488                                                 relevant column name; additional parameters, if any, are in an array tagged
1489                                                 as <literal>"params"</literal>.  The entry for
1490                                                 <literal>"result_field"</literal>, if present, specifies a subcolumn. </para>
1491                                 </listitem>
1492
1493                                 <listitem>
1494                                         <para> For a function call to the right of the comparison, the function name is
1495                                                 the first entry in an array, together with any parameters.  There's no way to
1496                                                 specify a subcolumn. </para>
1497                                 </listitem>
1498
1499                         </itemizedlist>
1500
1501                 </sect3>
1502
1503                 <sect3>
1504                         <title>Comparing a Function to a Condition</title>
1505                         <para> So far we have seen two kinds of data for the <literal>"value"</literal> tag.  A
1506                                 string or number translates to a literal value, and an array translates to a function
1507                                 call.  The third possibility is a JSON object, which translates to a condition.  For
1508                                 example: </para>
1509
1510                         <informalexample>
1511                                 <programlisting language="JSON">
1512         {
1513             "from":"aou",
1514             "select": { "aou":[ "id", "name" ] },
1515             "where": {
1516                 "id": {
1517                     "=": {
1518                         "value":{ "parent_ou":{ ">":3 } },
1519                         "transform":"is_prime"
1520                     }
1521                 }
1522             }
1523         }
1524                                 </programlisting>
1525                         </informalexample>
1526
1527                         <para> The function tagged as <literal>"transform"</literal> must return boolean, or else
1528                                 json_query will generate invalid SQL.  The function used here,
1529                                 <literal>"is_prime"</literal>, is fictitious. </para>
1530
1531                         <informalexample>
1532                                 <programlisting language="SQL">
1533         SELECT
1534             "aou".id AS "id",
1535             "aou".name AS "name"
1536         FROM
1537             actor.org_unit AS "aou"
1538         WHERE
1539             (
1540                 is_prime("aou".id ) = ( "aou".parent_ou > 3 )
1541             );
1542                                 </programlisting>
1543                         </informalexample>
1544
1545                         <para> If we left out the <literal>"transform"</literal> entry, json_query would compare
1546                                 the column on the left (which would to be boolean) to the condition on the right.
1547                                 The results are similar to those for a simpler format described earlier (see the
1548                                 subsection Testing Boolean Columns). </para>
1549                         <para> In the example above we compared the boolean to a simple condition.  However the
1550                                 expression on the right may include multiple conditions, IN lists, subqueries, and
1551                                 whatever other complications are necessary. </para>
1552                 </sect3>
1553
1554                 <sect3>
1555                         <title>Things You Can't Do</title>
1556                         <para> The WHERE clause is subject to some of the same limitations as the SELECT clause.
1557                                 However, in the WHERE clause these limitations are more limiting, because the client
1558                                 program can't compensate by doing some of the work for itself. </para>
1559                         <para> You can't use arbitrary expressions in a WHERE condition, such as
1560                                 <literal>"WHERE id > parent_ou -- 3"</literal>.  In some cases you may be able to
1561                                 contrive a custom operator in order to fake such an expression.  However this mechanism
1562                                 is neither very general nor very aesthetic. </para>
1563                         <para> To the right of a comparison operator, all function parameters must be literals or
1564                                 null.  You can't pass a column value, nor can you nest function calls. </para>
1565                         <para> Likewise you can't include column values or arbitrary expressions in an IN list
1566                                 or a BETWEEN clause. </para>
1567                         <para> You can't include null values in an IN list or a BETWEEN list, not that you should
1568                                 ever want to. </para>
1569                         <para> As noted earlier: you can't use the comparison operators
1570                                 <literal>"is distinct from"</literal> or <literal>"is not distinct from"</literal>. </para>
1571                         <para> Also as noted earlier: a subquery in an IN clause cannot select more than one
1572                                 column. </para>
1573                 </sect3>
1574
1575         </sect2>
1576
1577         <sect2>
1578                 <title>JOIN clauses</title>
1579                 <para> Until now, our examples have selected from only one table at a time.  As a result,
1580                         the FROM clause has been very simple -- just a single string containing the class name of
1581                         the relevant table. </para>
1582                 <para> When the FROM clause joins multiple tables, the corresponding JSON naturally gets more
1583                         complicated. </para>
1584                 <para> SQL provides two ways to define a join.  One way is to list both tables in the FROM
1585                         clause, and put the join conditions in the WHERE clause: </para>
1586
1587                 <informalexample>
1588                         <programlisting language="SQL">
1589         SELECT
1590             aou.id,
1591             aout.name
1592         FROM
1593             actor.org_unit aou,
1594             actor.org_unit_type aout
1595         WHERE
1596             aout.id = aou.ou_type;
1597                         </programlisting>
1598                 </informalexample>
1599
1600                 <para> The other way is to use an explicit JOIN clause: </para>
1601
1602                 <informalexample>
1603                         <programlisting language="SQL">
1604         SELECT
1605             aou.id,
1606             aout.name
1607         FROM
1608             actor.org_unit aou
1609                 JOIN actor.org_unit_type aout
1610                     ON ( aout.id = aou.ou_type );
1611                         </programlisting>
1612                 </informalexample>
1613
1614                 <para> JSON queries use only the second of these  methods.  The following example expresses
1615                         the same query in JSON: </para>
1616
1617                 <informalexample>
1618                         <programlisting language="JSON">
1619         {
1620             "select": { "aou":[ "id" ], "aout":[ "name" ] },
1621             "from": {
1622                 "aou":"aout"
1623             }
1624         }
1625                         </programlisting>
1626                 </informalexample>
1627
1628                 <para> First, let's review the SELECT clause.  Since it selects rows from two different tables,
1629                         the data for <literal>"select"</literal> includes two entries, one for each table. </para>
1630                 <para> As for the FROM clause, it's no longer just a string.  It's a JSON object, with exactly
1631                         one entry.  The key of this entry is the class name of the core table, i.e. the table
1632                         named immediately after the FROM keyword.  The data associated with this key contains the
1633                         rest of the information about the join.  In this simple example, that information consists
1634                         entirely of a string containing the class name of the other table. </para>
1635                 <para> So where is the join condition? </para>
1636                 <para> It's in the IDL.  Upon reading the IDL, json_query knows that actor.org_unit has a
1637                         foreign key pointing to actor.org_unit_type, and builds a join condition accordingly: </para>
1638
1639                 <informalexample>
1640                         <programlisting language="SQL">
1641         SELECT
1642             "aou".id AS "id",
1643             "aout".name AS "name"
1644         FROM
1645             actor.org_unit AS "aou"
1646                 INNER JOIN actor.org_unit_type AS "aout"
1647                     ON ( "aout".id = "aou".ou_type ) ;
1648                         </programlisting>
1649                 </informalexample>
1650
1651                 <para> In this case the core table is the child table, and the joined table is the parent table.
1652                         We could just as well have written it the other way around: </para>
1653
1654                 <informalexample>
1655                         <programlisting language="JSON">
1656         {
1657             "select": { "aou":[ "id" ], "aout":[ "name" ] },
1658             "from": {
1659                 "aout":"aou"
1660             }
1661         }
1662                         </programlisting>
1663                 </informalexample>
1664
1665                 <informalexample>
1666                         <programlisting language="SQL">
1667         SELECT
1668             "aou".id AS "id",
1669             "aout".name AS "name"
1670         FROM
1671             actor.org_unit_type AS "aout"
1672                 INNER JOIN actor.org_unit AS "aou"
1673                     ON ( "aou".ou_type = "aout".id ) ;
1674                         </programlisting>
1675                 </informalexample>
1676
1677                 <sect3>
1678                         <title>Specifying The Join Columns Explicitly</title>
1679                         <para> While it's convenient to let  json_query pick the join columns, it doesn't
1680                                 always work. </para>
1681                         <para> For example, the actor.org_unit table has four different address ids, for
1682                                 four different kinds of addresses.  Each of them is a foreign key to the
1683                                 actor.org_address table.  Json_query can't guess which one you want if you
1684                                 don't tell it. </para>
1685                         <para> (Actually it will try to guess.  It will pick the first matching link that
1686                                 it finds in the IDL, which may or may not be the one you want.) </para>
1687                         <para> Here's how to define exactly which columns you want for the join: </para>
1688
1689                         <informalexample>
1690                                 <programlisting language="JSON">
1691         {
1692             "select": { "aou":[ "id" ], "aoa":[ "street1" ] },
1693             "from": {
1694                 "aou": {
1695                     "aoa": {
1696                         "fkey":"holds_address",
1697                         "field":"id"
1698                     }
1699                 }
1700             }
1701         }
1702                                 </programlisting>
1703                         </informalexample>
1704
1705                         <para> Before, the table we were joining was represented merely by its class name.
1706                                 Now it's represented by an entry in a JSON object.  The key of that entry is the
1707                                 class name, and the associated data is another layer of JSON object containing
1708                                 the attributes of the join. </para>
1709                         <para> Later we'll encounter other kinds of join attributes.  For now, the only
1710                                 attributes that we're looking at are the ones that identify the join columns:
1711                                 <literal>"fkey"</literal> and <literal>"field"</literal>.  The hard part is
1712                                 remembering which is which: </para>
1713
1714                         <itemizedlist>
1715
1716                                 <listitem>
1717                                         <para> <literal>"fkey"</literal> identifies the join column from the
1718                                                 <emphasis>left</emphasis> table; </para>
1719                                 </listitem>
1720
1721                                 <listitem>
1722                                         <para> <literal>"field"</literal> identifies the join column from the
1723                                                 <emphasis>right</emphasis> table. </para>
1724                                 </listitem>
1725
1726                         </itemizedlist>
1727
1728                         <para> When there are only two tables involved, the core table is on the left, and
1729                                 the non-core table is on the right.  In more complex queries neither table may
1730                                 be the core table. </para>
1731                         <para> Here is the result of the preceding JSON: </para>
1732
1733                         <informalexample>
1734                                 <programlisting language="SQL">
1735         SELECT
1736             "aou".id AS "id",
1737             "aoa".street1 AS "street1"
1738         FROM
1739             actor.org_unit AS "aou"
1740                 INNER JOIN actor.org_address AS "aoa"
1741                     ON ( "aoa".id = "aou".holds_address ) ;
1742                                 </programlisting>
1743                         </informalexample>
1744
1745                         <para> In this example the child table is on the left and the parent table is on the
1746                                 right.  We can swap the tables if we swap the join columns as well: </para>
1747
1748                         <informalexample>
1749                                 <programlisting language="JSON">
1750         {
1751             "select": { "aou":[ "id" ], "aoa":[ "street1" ] },
1752             "from": {
1753                 "aoa": {
1754                     "aou": {
1755                         "fkey":"id",
1756                         "field":"holds_address"
1757                     }
1758                 }
1759             }
1760         }
1761                                 </programlisting>
1762                         </informalexample>
1763
1764                         <informalexample>
1765                                 <programlisting language="SQL">
1766         SELECT
1767             "aou".id AS "id",
1768             "aoa".street1 AS "street1"
1769         FROM
1770             actor.org_address AS "aoa"
1771                 INNER JOIN actor.org_unit AS "aou"
1772                     ON ( "aou".holds_address = "aoa".id ) ;
1773                                 </programlisting>
1774                         </informalexample>
1775
1776                         <para> When you specify both of the join columns, json_query assumes that you know
1777                                 what you're doing.  It doesn't check the IDL to confirm that the join makes sense.
1778                                 The burden is on you to avoid absurdities. </para>
1779                 </sect3>
1780
1781                 <sect3>
1782                         <title>Specifying Only One Join Column</title>
1783                         <para> We just saw how to specify both ends of a join.  It turns out that there's a
1784                                 shortcut -- most of the time you only need to specify one end.  Consider the
1785                                 following variation on the previous example: </para>
1786
1787                         <informalexample>
1788                                 <programlisting language="JSON">
1789         {
1790             "select": { "aou":[ "id" ], "aoa":[ "street1" ] },
1791             "from": {
1792                 "aoa": {
1793                     "aou": {
1794                         "field":"holds_address"
1795                     }
1796                 }
1797             }
1798         }
1799                                 </programlisting>
1800                         </informalexample>
1801
1802                         <para> ..which results in exactly the same SQL as before. </para>
1803                         <para> Here we specified the join column from the child table, the column that is a
1804                                 foreign key pointing to another table.  As long as that linkage is defined in the IDL,
1805                                 json_query can look it up and figure out what the corresponding column is in the
1806                                 parent table. </para>
1807                         <para> However this shortcut doesn't work if you specify only the column in the parent
1808                                 table, because it would lead to ambiguities.  Suppose we had specified the id column
1809                                 of actor.org_address.  As noted earlier, there are four different foreign keys from
1810                                 actor.org_unit to actor.org_address, and json_query would have no way to guess
1811                                 which one we wanted. </para>
1812                 </sect3>
1813
1814                 <sect3>
1815                         <title>Joining to Multiple Tables</title>
1816                         <para> So far we have joined only two tables at a time.  What if we need to join one
1817                                 table to two different tables? </para>
1818                         <para> Here's an example: </para>
1819
1820                         <informalexample>
1821                                 <programlisting language="JSON">
1822         {
1823             "select": { "aou":[ "id" ], "aout":[ "depth" ], "aoa":[ "street1" ] },
1824             "from": {
1825                 "aou": {
1826                     "aout":{},
1827                     "aoa": {
1828                         "fkey":"holds_address"
1829                     }
1830                 }
1831             }
1832         }
1833                                 </programlisting>
1834                         </informalexample>
1835
1836                         <para> The first join, to actor.org_unit_type, is simple.  We could have specified join
1837                                 columns, but we don't have to, because json_query will construct that join on the
1838                                 basis of what it finds in the IDL.  Having no join attributes to specify, we leave
1839                                 that object empty. </para>
1840                         <para> For the second join, to actor.org_address, we have to specify at least the join
1841                                 column in the child table, as discussed earlier.  We could also have specified the
1842                                 join column from the parent table, but we don't have to, so we didn't. </para>
1843                         <para> Here is the resulting SQL: </para>
1844
1845                         <informalexample>
1846                                 <programlisting language="SQL">
1847         SELECT
1848             "aou".id AS "id",
1849             "aout".depth AS "depth",
1850             "aoa".street1 AS "street1"
1851         FROM
1852             actor.org_unit AS "aou"
1853                 INNER JOIN actor.org_unit_type AS "aout"
1854                     ON ( "aout".id = "aou".ou_type )
1855                 INNER JOIN actor.org_address AS "aoa"
1856                     ON ( "aoa".id = "aou".holds_address ) ;
1857                                 </programlisting>
1858                         </informalexample>
1859
1860                         <para> Since there can be only one core table, the outermost object in the FROM clause
1861                                 can have only one entry, whose key is the class name of the core table.  The next
1862                                 level has one entry for every table that's joined to the core table. </para>
1863                 </sect3>
1864
1865                 <sect3>
1866                         <title>Nested Joins</title>
1867                         <para> Let's look at that last query again.  It joins three tables, and the core table
1868                                 is the one in the middle.  Can we make one of the end tables the core table instead? </para>
1869                         <para> Yes, we can: </para>
1870
1871                         <informalexample>
1872                                 <programlisting language="JSON">
1873         {
1874             "select": { "aou":[ "id" ], "aout":[ "depth" ], "aoa":[ "street1" ] },
1875             "from": {
1876                 "aoa": {
1877                     "aou": {
1878                         "field":"holds_address",
1879                         "join": {
1880                             "aout":{ "fkey":"ou_type" }
1881                         }
1882                     }
1883                 }
1884             }
1885         }
1886                                 </programlisting>
1887                         </informalexample>
1888
1889                         <para> The <literal>"join"</literal> attribute introduces another level of join.  In this
1890                                 case <literal>"aou"</literal> is the left table for the nested join, and the right table
1891                                 for the original join.  Here are the results: </para>
1892
1893                         <informalexample>
1894                                 <programlisting language="SQL">
1895         SELECT
1896             "aou".id AS "id",
1897             "aout".depth AS "depth",
1898             "aoa".street1 AS "street1"
1899         FROM
1900             actor.org_address AS "aoa"
1901                 INNER JOIN actor.org_unit AS "aou"
1902                     ON ( "aou".holds_address = "aoa".id )
1903                 INNER JOIN actor.org_unit_type AS "aout"
1904                     ON ( "aout".id = "aou".ou_type ) ;
1905                                 </programlisting>
1906                         </informalexample>
1907
1908                 </sect3>
1909
1910                 <sect3>
1911                         <title>Outer Joins</title>
1912                         <para> By default, json_query constructs an inner join.  If you need an outer join, you
1913                                 can add the join type as an attribute of the join: </para>
1914
1915                         <informalexample>
1916                                 <programlisting language="JSON">
1917         {
1918             "select": { "aou":[ "id" ], "aoa":[ "street1" ] },
1919             "from": {
1920                 "aoa": {
1921                     "aou": {
1922                         "field":"mailing_address",
1923                         "type":"left"
1924                     }
1925                 }
1926             }
1927         }
1928                                 </programlisting>
1929                         </informalexample>
1930
1931                         <para> Here we asked for a left outer join.  For a right outer join, code
1932                                 <literal>"type":"right"</literal>.  For a full outer join, code
1933                                 <literal>"type":"full"</literal>.  Any other value for "type" results in an inner
1934                                 join, so watch out for typos.  A type of <literal>"rihgt"</literal> will give you
1935                                 a wrong join instead of a right one. </para>
1936                         <para> Here is the resulting SQL for this example: </para>
1937
1938                         <informalexample>
1939                                 <programlisting language="SQL">
1940         SELECT
1941             "aou".id AS "id",
1942             "aoa".street1 AS "street1"
1943         FROM
1944             actor.org_address AS "aoa"
1945                 LEFT JOIN actor.org_unit AS "aou"
1946                     ON ( "aou".mailing_address = "aoa".id ) ;
1947                                 </programlisting>
1948                         </informalexample>
1949
1950                 </sect3>
1951
1952                 <sect3>
1953                         <title>Referring to Joined Tables in the WHERE Clause</title>
1954                         <para> In the WHERE clause of the generated SQL, every column name is qualified by a
1955                                 table alias, which is always the corresponding class name. </para>
1956                         <para> If a column belongs to the core table, this qualification happens by default.
1957                                 If it belongs to a joined table, the JSON must specify what class name to use for
1958                                 an alias.  For example: </para>
1959
1960                         <informalexample>
1961                                 <programlisting language="JSON">
1962         {
1963             "select": { "aou":[ "id" ], "aout":[ "name" ] },
1964             "from": {
1965                 "aout":"aou"
1966             },
1967             "where": {
1968                 "+aou":{ "parent_ou":2 }
1969             }
1970         }
1971                                 </programlisting>
1972                         </informalexample>
1973
1974                         <para> Note the peculiar operator <literal>"+aou"</literal> -- a plus sign followed
1975                                 by the relevant class name.  This operator tells json_query to apply the specified
1976                                 class to the condition that follows.  The result: </para>
1977
1978                         <informalexample>
1979                                 <programlisting language="SQL">
1980         SELECT
1981             "aou".id AS "id",
1982             "aout".name AS "name"
1983         FROM
1984             actor.org_unit_type AS "aout"
1985                 INNER JOIN actor.org_unit AS "aou"
1986                     ON ( "aou".ou_type = "aout".id )
1987         WHERE
1988             ( "aou".parent_ou = 2 );
1989                                 </programlisting>
1990                         </informalexample>
1991
1992                         <para> The plus-class operator may apply to multiple conditions: </para>
1993
1994                         <informalexample>
1995                                 <programlisting language="JSON">
1996         {
1997             "select": { "aou":[ "id" ], "aout":[ "name" ] },
1998             "from": {
1999                 "aout":"aou"
2000             },
2001             "where": {
2002                 "+aou":{
2003                     "parent_ou":2,
2004                     "id":{ "&lt;":42 }
2005                 }
2006             }
2007         }
2008                                 </programlisting>
2009                         </informalexample>
2010
2011                         <informalexample>
2012                                 <programlisting language="SQL">
2013         SELECT
2014             "aou".id AS "id",
2015             "aout".name AS "name"
2016         FROM
2017             actor.org_unit_type AS "aout"
2018                 INNER JOIN actor.org_unit AS "aou"
2019                     ON ( "aou".ou_type = "aout".id )
2020         WHERE
2021             (
2022                 "aou".parent_ou = 2
2023                 AND "aou".id &lt; 42
2024             );
2025                                 </programlisting>
2026                         </informalexample>
2027
2028                         <para> For these artificial examples, it would have been simpler to swap the tables,
2029                                 so that actor.org_unit is the core table.  Then you wouldn't need to go through
2030                                 any special gyrations to apply the right table alias.  In a more realistic case,
2031                                 however, you might need to apply conditions to both tables.  Just swapping the
2032                                 tables wouldn't solve the problem. </para>
2033                         <para> You can also use a plus-class operator to compare columns from two different
2034                                 tables: </para>
2035
2036                         <informalexample>
2037                                 <programlisting language="JSON">
2038         {
2039             "select": { "aou":[ "id" ], "aout":[ "name" ] },
2040             "from": {
2041                 "aout":"aou"
2042             },
2043             "where": {
2044                 "depth": { "&gt;": { "+aou":"parent_ou" } }
2045             }
2046         }
2047                                 </programlisting>
2048                         </informalexample>
2049
2050                         <informalexample>
2051                                 <programlisting language="SQL">
2052         SELECT
2053             "aou".id AS "id",
2054             "aout".name AS "name"
2055         FROM
2056             actor.org_unit_type AS "aout"
2057                 INNER JOIN actor.org_unit AS "aou"
2058                     ON ( "aou".ou_type = "aout".id )
2059         WHERE
2060             (
2061                 "aout".depth > (  "aou".parent_ou  )
2062             );
2063                                 </programlisting>
2064                         </informalexample>
2065
2066                         <para> Please don't expect that query to make any sense.  It doesn't.  But it
2067                                 illustrates the syntax. </para>
2068                 </sect3>
2069
2070                 <sect3>
2071                         <title>Join Filters</title>
2072                         <para> While the above approach certainly works, the special syntax needed is goofy
2073                                 and awkward.  A somewhat cleaner solution is to include a condition in the JOIN
2074                                 clause: </para>
2075
2076                         <informalexample>
2077                                 <programlisting language="JSON">
2078         {
2079             "select": { "aou":[ "id" ], "aout":[ "name" ] },
2080             "from": {
2081                 "aout": {
2082                     "aou": {
2083                         "filter": {
2084                             "parent_ou":2
2085                         }
2086                     }
2087                 }
2088             }
2089         }
2090                                 </programlisting>
2091                         </informalexample>
2092
2093                         <informalexample>
2094                                 <programlisting language="SQL">
2095         SELECT
2096             "aou".id AS "id", "aout".name AS "name"
2097         FROM
2098             actor.org_unit_type AS "aout"
2099                 INNER JOIN actor.org_unit AS "aou"
2100                     ON ( "aou".ou_type = "aout".id
2101                          AND  "aou".parent_ou = 2 ) ;
2102                         </programlisting>
2103                         </informalexample>
2104
2105                         <para> By default, json_query uses AND to combine the <literal>"filter"</literal>
2106                                 condition with the original join condition.  If you need OR, you can use the
2107                                 <literal>"filter_op"</literal> attribute to say so: </para>
2108
2109                         <informalexample>
2110                                 <programlisting language="JSON">
2111         {
2112             "select": { "aou":[ "id" ], "aout":[ "name" ] },
2113             "from": {
2114                 "aout": {
2115                     "aou": {
2116                         "filter": {
2117                             "parent_ou":2
2118                         },
2119                         "filter_op":"or"
2120                     }
2121                 }
2122             }
2123         }
2124                                 </programlisting>
2125                         </informalexample>
2126
2127                         <informalexample>
2128                                 <programlisting language="SQL">
2129         SELECT
2130             "aou".id AS "id",
2131             "aout".name AS "name"
2132         FROM
2133             actor.org_unit_type AS "aout"
2134                 INNER JOIN actor.org_unit AS "aou"
2135                     ON ( "aou".ou_type = "aout".id
2136                          OR  "aou".parent_ou = 2 ) ;
2137                                 </programlisting>
2138                         </informalexample>
2139
2140                         <para> If the data tagged by <literal>"filter_op"</literal> is anything but
2141                                 <literal>"or"</literal> (in upper, lower, or mixed case), json_query uses AND
2142                                 instead of OR. </para>
2143                         <para> The condition tagged by <literal>"filter"</literal> may be much more complicated.
2144                                 In fact it accepts all the same syntax as the WHERE clause. </para>
2145                         <para> Remember, though, that it all gets combined with the the original join condition
2146                                 with an AND, or with an OR if you so specify.  If you're not careful, the result
2147                                 may be a confusing mixture of AND and OR at the same level. </para>
2148                 </sect3>
2149
2150                 <sect3>
2151                         <title>Joining to a Subquery</title>
2152                         <para> In SQL you can put a subquery in a FROM clause, and select from it as if it were
2153                                 a table.  A JSON query has no way to do that directly.  The IDL, however, can define
2154                                 a class as a subquery instead of as a table.  When you SELECT from it, json_query
2155                                 inserts the corresponding subquery into the FROM clause.  For example: </para>
2156
2157                         <informalexample>
2158                                 <programlisting language="JSON">
2159         {
2160             "select":{ "iatc":[ "id", "dest", "copy_status" ] },
2161             "from": "iatc"
2162         }
2163                                 </programlisting>
2164                         </informalexample>
2165
2166                         <para> There's nothing special-looking about this JSON, but json_query expands it as
2167                                 follows: </para>
2168
2169                         <informalexample>
2170                                 <programlisting language="SQL">
2171         SELECT
2172             "iatc".id AS "id",
2173             "iatc".dest AS "dest",
2174             "iatc".copy_status AS "copy_status"
2175         FROM
2176             (
2177                 SELECT  t.*
2178                 FROM
2179                     action.transit_copy t
2180                         JOIN actor.org_unit AS s
2181                             ON (t.source = s.id)
2182                         JOIN actor.org_unit AS d
2183                             ON (t.dest = d.id)
2184                 WHERE
2185                     s.parent_ou &lt;&gt; d.parent_ou
2186             ) AS "iatc" ;
2187                                 </programlisting>
2188                         </informalexample>
2189
2190                         <para> The <literal>"iatc"</literal> class is like a view, except that it's defined in the
2191                                 IDL instead of the database.  In this case it provides a way to do a join that would
2192                                 otherwise be impossible through a JSON query, because it joins the same table in two
2193                                 different ways (see the next subsection). </para>
2194                 </sect3>
2195
2196                 <sect3>
2197                         <title>Things You Can't Do</title>
2198                         <para> In a JOIN, as with other SQL constructs, there are some things that you can't do with
2199                                 a JSON query. </para>
2200                         <para> In particular, you can't specify a table alias, because the table alias is always the
2201                                 class name.  As a result: </para>
2202
2203                         <itemizedlist>
2204
2205                                 <listitem>
2206                                         <para> You can't join a table to itself.  For example, you can't join actor.org_unit
2207                                                 to itself in order to select the name of the parent for every org_unit. </para>
2208                                 </listitem>
2209
2210                                 <listitem>
2211                                         <para> You can't join to the same table in more than one way.  For example, you can't
2212                                                 join actor.org_unit to actor.org_address through four different foreign keys, to
2213                                                 get four kinds of addresses in a single query. </para>
2214                                 </listitem>
2215
2216                         </itemizedlist>
2217
2218                         <para> The only workaround is to perform the join in a view, or in a subquery defined in
2219                                 the IDL as described in the previous subsection. </para>
2220                         <para> Some other things, while not impossible, require some ingenuity in the use of join
2221                                 filters. </para>
2222                         <para> For example: by default, json_query constructs a join condition using only a single
2223                                 pair of corresponding columns.  As long as the database is designed accordingly, a
2224                                 single pair of columns will normally suffice.  If you ever need to join on more than
2225                                 one pair of columns, you can use join filters for the extras. </para>
2226                         <para> Likewise, join conditions are normally equalities.  In raw SQL it is possible
2227                                 (though rarely useful) to base a join on an inequality, or to use a function call in
2228                                 a join condition, or to omit any join condition in order to obtain a Cartesian product.
2229                                 If necessary, you can devise such unconventional joins by combining the normal join
2230                                 conditions with join filters. </para>
2231                         <para> For example, here's how to get a Cartesian product: </para>
2232
2233                         <informalexample>
2234                                 <programlisting language="JSON">
2235         {
2236             "select": { "aou":[ "id" ], "aout":[ "name" ] },
2237             "from": {
2238                 "aout": {
2239                     "aou": {
2240                         "filter": {
2241                             "ou_type":{ "&lt;&gt;": { "+aout":"id" } }
2242                         },
2243                         "filter_op":"or"
2244                     }
2245                 }
2246             }
2247         }
2248                                 </programlisting>
2249                         </informalexample>
2250
2251                         <informalexample>
2252                                 <programlisting language="SQL">
2253         SELECT
2254             "aou".id AS "id",
2255             "aout".name AS "name"
2256         FROM
2257             actor.org_unit_type AS "aout"
2258                 INNER JOIN actor.org_unit AS "aou"
2259                     ON
2260                     (
2261                         "aou".ou_type = "aout".id
2262                         OR  ("aou".ou_type &lt;&gt; (  "aout".id  ))
2263                     ) ;
2264                                 </programlisting>
2265                         </informalexample>
2266
2267                         <para> Yes, it's ugly, but at least you're not likely to do it by accident. </para>
2268                 </sect3>
2269         </sect2>
2270
2271         <sect2>
2272                 <title>Selecting from Functions</title>
2273                 <para> In SQL, you can put a function call in the FROM clause.  The function may return
2274                         multiple columns and multiple rows.  Within the query, the function behaves like a
2275                         table. </para>
2276                 <para> A JSON query can also select from a function: </para>
2277
2278                 <informalexample>
2279                         <programlisting language="JSON">
2280         {
2281             "from": [ "actor.org_unit_ancestors", 5 ]
2282         }
2283                         </programlisting>
2284                 </informalexample>
2285
2286                 <para> The data associated with <literal>"from"</literal> is an array instead of a string
2287                                 or an object.  The first element in the array specifies the name of the function.
2288                                 Subsequent elements, if any, supply the parameters of the function; they must be
2289                                 literal values or nulls. </para>
2290                 <para> Here is the resulting query: </para>
2291
2292                 <informalexample>
2293                         <programlisting language="SQL">
2294         SELECT *
2295         FROM
2296             actor.org_unit_ancestors( '5' ) AS "actor.org_unit_ancestors" ;
2297                         </programlisting>
2298                 </informalexample>
2299
2300                 <para> In a JSON query this format is very limited, largely because the IDL knows
2301                         nothing about the available functions.  You can't join the function to a table or
2302                         to another function.  If you try to supply a SELECT list or a WHERE clause,
2303                         json_query will ignore it.  The generated query will always select every column,
2304                         via a wild card asterisk, from every row. </para>
2305         </sect2>
2306
2307         <sect2>
2308                 <title>The ORDER BY Clause</title>
2309                 <para> In most cases you can encode an ORDER BY clause as either an array or an object.
2310                         Let's take a simple example and try it both ways.  First the array: </para>
2311
2312                 <informalexample>
2313                         <programlisting language="JSON">
2314         {
2315             "select":{ "aou":[ "name" ] },
2316             "from": "aou",
2317             "order_by": [
2318                 { "class":"aou", "field":"name" }
2319             ]
2320         }
2321                         </programlisting>
2322                 </informalexample>
2323
2324                 <para> Now the object: </para>
2325
2326                 <informalexample>
2327                         <programlisting language="JSON">
2328         {
2329             "select":{ "aou":[ "name" ] },
2330             "from": "aou",
2331             "order_by": {
2332                 "aou":{ "name":{} }
2333             }
2334         }
2335                         </programlisting>
2336                 </informalexample>
2337
2338                 <para> The results are identical from either version: </para>
2339
2340                 <informalexample>
2341                         <programlisting language="SQL">
2342         SELECT
2343             "aou".name AS "name"
2344         FROM
2345             actor.org_unit AS "aou"
2346         ORDER BY
2347             "aou".name;
2348                         </programlisting>
2349                 </informalexample>
2350
2351                 <para> The array format is more verbose, but as we shall see, it is also more flexible.
2352                         It can do anything the object format can do, plus some things that the object format
2353                         <emphasis>can't</emphasis> do. </para>
2354
2355                 <sect3>
2356                         <title>ORDER BY as an Array</title>
2357                         <para> In the array format, each element of the array is an object defining one of the
2358                                 sort fields.  Each such object must include at least two tags: </para>
2359
2360                         <itemizedlist>
2361
2362                                 <listitem>
2363                                         <para> The <literal>"class"</literal> tag provides the name of the class,
2364                                                 which must be either the core class or a joined class. </para>
2365                                 </listitem>
2366
2367                                 <listitem>
2368                                         <para> The <literal>"field"</literal> tag provides the field name, corresponding
2369                                                 to one of the columns of the class. </para>
2370                                 </listitem>
2371
2372                         </itemizedlist>
2373
2374                         <para> If you want to sort by multiple fields, just include a separate object for each
2375                                 field. </para>
2376                         <para> If you want to sort a field in descending order, add a
2377                                 <literal>"direction"</literal> tag: </para>
2378
2379                         <informalexample>
2380                                 <programlisting language="JSON">
2381         {
2382             "select":{ "aou":[ "name" ] },
2383             "from": "aou",
2384             "order_by": [
2385                 { "class":"aou", "field":"name", "direction":"desc" }
2386             ]
2387         }
2388                                 </programlisting>
2389                         </informalexample>
2390
2391                         <informalexample>
2392                                 <programlisting language="SQL">
2393         SELECT
2394             "aou".name AS "name"
2395         FROM
2396             actor.org_unit AS "aou"
2397         ORDER BY
2398             "aou".name DESC;
2399                                 </programlisting>
2400                         </informalexample>
2401
2402                         <para> The string tagged as <literal>"direction"</literal> can be anything -- all that
2403                                 matters is the first character.  If the string starts with "D" or "d", the sort
2404                                 will be descending.  Otherwise it will be ascending.  So
2405                                 <literal>"diplodocus"</literal> or <literal>"Dioscorides"</literal> will work as
2406                                 well as <literal>"desc"</literal>, but <literal>"going down"</literal> means that
2407                                 the sort will go up. </para>
2408                         <para> You can also pass a column through some kind of transforming function, much as
2409                                 you can in the SELECT and WHERE clauses, using the <literal>"transform"</literal>
2410                                 tag.  For example, for a case-insensitive sort, you could raise to upper case: </para>
2411
2412                         <informalexample>
2413                                 <programlisting language="JSON">
2414         {
2415             "select":{ "aou":[ "name" ] },
2416             "from": "aou",
2417             "order_by": [
2418                 {
2419                     "class":"aou",
2420                     "field":"name",
2421                     "transform":"upper"
2422                 }
2423             ]
2424         }
2425                                 </programlisting>
2426                         </informalexample>
2427
2428                         <informalexample>
2429                                 <programlisting language="SQL">
2430         SELECT
2431             "aou".name AS "name"
2432         FROM
2433             actor.org_unit AS "aou"
2434         ORDER BY
2435             upper("aou".name );
2436                                 </programlisting>
2437                         </informalexample>
2438
2439                         <para> If you need additional parameters for the function, you can use the
2440                                 <literal>"params"</literal> tag to pass them: </para>
2441
2442                         <informalexample>
2443                                 <programlisting language="JSON">
2444         {
2445             "select":{ "aou":[ "name" ] },
2446             "from": "aou",
2447             "order_by": [
2448                 {
2449                     "class":"aou",
2450                     "field":"name",
2451                     "transform":"substr",
2452                     "params":[ 1, 8 ]
2453                 }
2454             ]
2455         }
2456                                 </programlisting>
2457                         </informalexample>
2458
2459                         <para> The additional parameters appear as elements in an array.  They may be numbers,
2460                                 strings, or nulls. </para>
2461
2462                         <informalexample>
2463                                 <programlisting language="SQL">
2464         SELECT
2465             "aou".name AS "name"
2466         FROM
2467             actor.org_unit AS "aou"
2468         ORDER BY
2469             substr("aou".name,'1','8' );
2470                                 </programlisting>
2471                         </informalexample>
2472
2473                         <para> As we have seen elsewhere, all literal values are passed as quoted strings,
2474                                 even if they are numbers. </para>
2475                         <para> If the function returns multiple columns, you can use the
2476                                 <literal>"result_field"</literal> tag to indicate which one you want (not shown). </para>
2477                 </sect3>
2478
2479                 <sect3>
2480                         <title>ORDER BY as an Object</title>
2481                         <para> When you encode the ORDER BY clause as an object, the keys of the object are
2482                                 class names.  Each class must be either the core class or a joined class.  The
2483                                 data for each class can be either an array or another layer of object.  Here's an
2484                                 example with one of each: </para>
2485
2486                         <informalexample>
2487                                 <programlisting language="JSON">
2488         {
2489             "select":{ "aout":"id", "aou":[ "name" ] },
2490             "from": { "aou":"aout" },
2491             "order_by": {
2492                 "aout":[ "id" ],
2493                 "aou":{ "name":{ "direction":"desc" } }
2494             }
2495         }
2496                                 </programlisting>
2497                         </informalexample>
2498
2499                         <para> For the <literal>"aout"</literal> class, the associated array is simply a list
2500                                 of field names (in this case, just one).  Naturally, each field must reside in
2501                                 the class with which it is associated. </para>
2502                         <para> However, a list of field names provides no way to specify the direction of
2503                                 sorting, or a transforming function.  You can add those details only if the
2504                                 class name is paired with an object, as in the example for the
2505                                 <literal>"aou"</literal> class.  The keys for such an object are field names, and
2506                                 the associated tags define other details. </para>
2507                         <para> In this example, we use the <literal>"direction"</literal> tag to specify that
2508                                 the name field be sorted in descending order.  This tag works the same way here as
2509                                 described earlier.  If the associated string starts with "D" or "d", the sort will
2510                                 be descending; otherwise it will be ascending. </para>
2511                         <para> Here is the resulting SQL: </para>
2512
2513                         <informalexample>
2514                                 <programlisting language="SQL">
2515         SELECT
2516             "aou".name AS "name"
2517         FROM
2518             actor.org_unit AS "aou"
2519                 INNER JOIN actor.org_unit_type AS "aout"
2520                     ON ( "aout".id = "aou".ou_type )
2521         ORDER BY
2522             "aout".id,
2523             "aou".name DESC;
2524                                 </programlisting>
2525                         </informalexample>
2526
2527                         <para> You can also use the  <literal>"transform</literal>", <literal>"params"</literal>,
2528                                 and <literal>"result_field"</literal> tags to specify the use of a transforming
2529                                 function, as described in the previous subsection.  For example: </para>
2530
2531                         <informalexample>
2532                                 <programlisting language="JSON">
2533         {
2534             "select":{ "aou":[ "name", "id" ] },
2535             "from": "aou",
2536             "order_by": {
2537                 "aou":{
2538                     "name":{ "transform":"substr", "params":[ 1, 8 ] }
2539                 }
2540             }
2541         }
2542                                 </programlisting>
2543                         </informalexample>
2544
2545                         <informalexample>
2546                                 <programlisting language="SQL">
2547         SELECT
2548             "aou".name AS "name",
2549             "aou".id AS "id"
2550         FROM
2551             actor.org_unit AS "aou"
2552         ORDER BY
2553             substr("aou".name,'1','8' );
2554                                 </programlisting>
2555                         </informalexample>
2556
2557                 </sect3>
2558
2559                 <sect3>
2560                         <title>Things You Can't Do</title>
2561                         <para> If you encode the ORDER BY clause as an object, you may encounter a couple of
2562                                 restrictions. </para>
2563                         <para> Because the key of such an object is the class name, all the fields from a given
2564                                 class must be grouped together.  You can't sort by a column from one table, followed
2565                                 by a column from another table, followed by a column from the first table.  If you
2566                                 need such a sort, you must encode the ORDER BY clause in the array format, which
2567                                 suffers from no such restrictions. </para>
2568                         <para> For similar reasons, with an ORDER BY clause encoded as an object, you can't
2569                                 reference the same column more than once.  Although such a sort may seem perverse,
2570                                 there are situations where it can be useful, provided that the column is passed to
2571                                 a transforming function. </para>
2572                         <para> For example, you might want a case-insensitive sort, except that for any given
2573                                 letter you want lower case to sort first.  For example, you want "diBona" to sort
2574                                 before "Dibona".  Here's a way to do that, coding the ORDER BY clause as an array: </para>
2575
2576                         <informalexample>
2577                                 <programlisting language="JSON">
2578         {
2579             "select":{ "au":[ "family_name", "id" ] },
2580             "from": "au",
2581             "order_by": [
2582                 { "class":"au", "field":"family_name", "transform":"upper" },
2583                 { "class":"au", "field":"family_name" }
2584             ]
2585         }
2586                                 </programlisting>
2587                         </informalexample>
2588
2589                         <informalexample>
2590                                 <programlisting language="SQL">
2591         SELECT
2592                 "au".family_name AS "family_name",
2593                 "au".id AS "id"
2594         FROM
2595                 actor.usr AS "au"
2596         ORDER BY
2597                 upper("au".family_name ),
2598                 "au".family_name;
2599                                 </programlisting>
2600                         </informalexample>
2601
2602                         <para> Such a sort is not possible where the ORDER BY clause is coded as an object. </para>
2603                 </sect3>
2604         </sect2>
2605
2606         <sect2>
2607                 <title>The GROUP BY Clause</title>
2608                 <para> A JSON query has no separate construct to define a GROUP BY clause.  Instead, the
2609                         necessary information is distributed across the SELECT clause.  However, the way it works
2610                         is a bit backwards from what you might expect, so pay attention. </para>
2611                 <para> Here's an example: </para>
2612
2613                 <informalexample>
2614                         <programlisting language="JSON">
2615         {
2616             "select": {
2617                 "aou": [
2618                     { "column":"parent_ou" },
2619                     { "column":"name", "transform":"max", "aggregate":true }
2620                 ]
2621             },
2622             "from": "aou"
2623         }
2624                         </programlisting>
2625                 </informalexample>
2626
2627                 <para> The <literal>"transform"</literal> tag is there just to give us an excuse to do a GROUP
2628                         BY.  What's important to notice is the <literal>"aggregate"</literal> tag. </para>
2629                 <para> Here's the resulting SQL: </para>
2630
2631                 <informalexample>
2632                         <programlisting language="SQL">
2633         SELECT
2634             "aou".parent_ou AS "parent_ou",
2635             max("aou".name ) AS "name"
2636         FROM
2637             actor.org_unit AS "aou"
2638         GROUP BY
2639             1;
2640                         </programlisting>
2641                 </informalexample>
2642
2643                 <para> The GROUP BY clause references fields from the SELECT clause by numerical reference,
2644                         instead of by repeating them.  Notice that the field it references, parent_ou, is the
2645                         one that <emphasis>doesn't</emphasis> carry the <literal>"aggregate"</literal> tag in
2646                         the JSON. </para>
2647                 <para> Let's state that more generally.  The GROUP BY clause includes only the fields that
2648                         do <emphasis>not</emphasis> carry the <literal>"aggregate"</literal> tag (or that carry
2649                         it with a value of false). </para>
2650                 <para> However, that logic applies only when some field somewhere <emphasis>does</emphasis>
2651                         carry the <literal>"aggregate"</literal> tag, with a value of true.  If there is no
2652                         <literal>"aggregate"</literal> tag, or it appears only with a value of false, then there
2653                         is no GROUP BY clause. </para>
2654                 <para> If you really want to include every field in the GROUP BY clause, don't use
2655                         <literal>"aggregate"</literal>.  Use the <literal>"distinct"</literal> tag, as described
2656                         in the next section. </para>
2657         </sect2>
2658
2659         <sect2>
2660                 <title>The DISTINCT Clause</title>
2661                 <para> JSON queries don't generate DISTINCT clauses.  However, they can generate GROUP
2662                         BY clauses that include every item from the SELECT clause.  The effect is the same
2663                         as applying DISTINCT to the entire SELECT clause. </para>
2664                 <para> For example: </para>
2665
2666                 <informalexample>
2667                         <programlisting language="JSON">
2668         {
2669             "select": {
2670                 "aou": [
2671                     "parent_ou",
2672                     "ou_type"
2673                 ]
2674             },
2675             "from":"aou",
2676             "distinct":"true"
2677         }
2678                         </programlisting>
2679                 </informalexample>
2680
2681                 <para> Note the <literal>"distinct"</literal> entry at the top level of the
2682                         query object, with a value of "true". </para>
2683
2684                 <informalexample>
2685                         <programlisting language="SQL">
2686         SELECT
2687             "aou".parent_ou AS "parent_ou",
2688             "aou".ou_type AS "ou_type"
2689         FROM
2690             actor.org_unit AS "aou"
2691         GROUP BY
2692             1, 2;
2693                         </programlisting>
2694                 </informalexample>
2695
2696                 <para> The generated GROUP BY clause references every column in the SELECT clause by
2697                         number. </para>
2698         </sect2>
2699
2700         <sect2>
2701                 <title>The HAVING Clause</title>
2702                 <para> For a HAVING clause, add a <literal>"having"</literal> entry at the top level
2703                         of the query object.  For the associated data, you can use all the same syntax
2704                         that you can use for a WHERE clause. </para>
2705                 <para> Here's a simple example: </para>
2706
2707                 <informalexample>
2708                         <programlisting language="JSON">
2709         {
2710             "select": {
2711                 "aou": [
2712                     "parent_ou", {
2713                         "column":"id",
2714                         "transform":"count",
2715                         "alias":"id_count",
2716                         "aggregate":"true"
2717                     }
2718                 ]
2719             },
2720             "from":"aou",
2721             "having": {
2722                 "id": {
2723                     "&gt;" : {
2724                         "transform":"count",
2725                         "value":6
2726                     }
2727                 }
2728             }
2729         }
2730                         </programlisting>
2731                 </informalexample>
2732
2733                 <para> We use the "aggregate" tag in the SELECT clause to give us a GROUP  BY to go
2734                         with the HAVING.  Results: </para>
2735
2736                 <informalexample>
2737                         <programlisting language="SQL">
2738         SELECT
2739             "aou".parent_ou AS "parent_ou",
2740             count("aou".id ) AS "id_count"
2741         FROM
2742             actor.org_unit AS "aou"
2743         GROUP BY
2744             1
2745         HAVING
2746             count("aou".id ) >  6 ;
2747                         </programlisting>
2748