Do some clean up of development files. Adjust converting script to
[working/Evergreen.git] / 1.6 / development / json.xml
index 036200d..b445fd9 100644 (file)
                </programlisting>\r
                <para>In this minimal example we select from only one table. Later we will see how to join multiple tables.</para>\r
                <para>Since we don't supply a WHERE clause, json_query constructs a default WHERE clause for us, including all the available columns. The resulting SQL looks like this:</para>\r
-               <programlisting>\r
-               SELECT\r
-                   "aou".billing_address AS "billing_address",\r
-                   "aou".holds_address   AS "holds_address",\r
-                   "aou".id              AS "id",\r
-                   "aou".ill_address     AS "ill_address",\r
-                   "aou".mailing_address AS "mailing_address",\r
-                   "aou".name            AS "name",\r
-                   "aou".ou_type         AS "ou_type",\r
-                   "aou".parent_ou       AS "parent_ou",\r
-                   "aou".shortname       AS "shortname",\r
-                   "aou".email           AS "email",\r
-                   "aou".phone           AS "phone",\r
-                   "aou".opac_visible    AS "opac_visible"\r
-               FROM\r
-                       actor.org_unit        AS "aou" ;        \r
-               </programlisting>\r
+<programlisting language="sql">\r
+SELECT\r
+    "aou".billing_address AS "billing_address",\r
+    "aou".holds_address   AS "holds_address",\r
+    "aou".id              AS "id",\r
+    "aou".ill_address     AS "ill_address",\r
+    "aou".mailing_address AS "mailing_address",\r
+    "aou".name            AS "name",\r
+    "aou".ou_type         AS "ou_type",\r
+    "aou".parent_ou       AS "parent_ou",\r
+    "aou".shortname       AS "shortname",\r
+    "aou".email           AS "email",\r
+    "aou".phone           AS "phone",\r
+    "aou".opac_visible    AS "opac_visible"\r
+FROM\r
+       actor.org_unit        AS "aou" ;        \r
+</programlisting>\r
         </simplesect>\r
        <simplesect>\r
                <title>Default SELECT Clauses</title>\r
        <simplesect>\r
                <title>The SELECT Clause</title>\r
                <para>The following variation also produces a default SELECT clause:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": {\r
-                       "aou":"*"\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": {\r
+       "aou":"*"\r
+       }\r
+}\r
+</programlisting>\r
                <para>...and so does this one:</para>\r
-               <programlisting>\r
-               {\r
-                       "select": {\r
-                       "aou":null\r
-                       },\r
-                       "from":"aou"\r
-               }\r
-               </programlisting>\r
+<programlisting>\r
+{\r
+       "select": {\r
+       "aou":null\r
+       },\r
+       "from":"aou"\r
+}\r
+</programlisting>\r
                <para>While this syntax may not be terribly useful, it does illustrate the minimal structure of a SELECT clause in a JSON query: an entry in the outermost JSON object, \r
                with a key of <quote>select</quote>. The value associated with this key is another JSON object, whose keys are class names.</para>\r
                <para>(These two examples also illustrate another point: unlike SQL, a JSON query doesn't care whether the FROM clause or the SELECT clause comes first.)</para>\r
                <para>Usually you don't want the default SELECT clause. Here's how to select only some of the columns:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": {\r
-                       "aou":[ "id", "name" ]\r
-                        }\r
-               }\r
-               </programlisting>\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": {\r
+       "aou":[ "id", "name" ]\r
+        }\r
+}\r
+</programlisting>\r
                <para>The value associated with the class name is an array of column names. If you select columns from multiple tables (not shown here), you'll need a separate entry for each table, \r
                and a separate column list for each entry.</para>\r
                <para>The previous example results in the following SQL:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       "aou".id       AS "id",\r
-                       "aou".name     AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou" ;\r
-               </programlisting>\r
+<programlisting>\r
+SELECT\r
+       "aou".id       AS "id",\r
+       "aou".name     AS "name"\r
+FROM\r
+       actor.org_unit AS "aou" ;\r
+</programlisting>\r
        </simplesect>\r
        <simplesect>\r
                <title>Fancier SELECT Clauses</title>\r
                        </listitem>     \r
                </itemizedlist> \r
                <para>This example assigns a different column alias:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": {\r
-                               "aou": [\r
-                                       "id",\r
-                                       { "column":"name", "alias":"org_name" }\r
-                               ]\r
-                       }\r
-               }\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": {\r
+               "aou": [\r
+                       "id",\r
+                       { "column":"name", "alias":"org_name" }\r
+               ]\r
+       }\r
+}\r
 \r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aou".name AS "org_name"\r
-               FROM\r
-                       actor.org_unit AS "aou" ;\r
-               </programlisting>\r
+SELECT\r
+       "aou".id AS "id",\r
+       "aou".name AS "org_name"\r
+FROM\r
+       actor.org_unit AS "aou" ;\r
+</programlisting>\r
                <para>In this case, changing the column alias doesn't accomplish much. But if we were joining to the actor.org_unit_type table, which also has a "name" column, we could \r
                use different aliases to distinguish them.</para>\r
                <para>The following example uses a function to raise a column to upper case:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": {\r
-                               "aou": [\r
-                                       "id",\r
-                                       { "column":"name", "transform":"upper" }\r
-                               ]\r
-                       }\r
-               }\r
-                               \r
-               SELECT\r
-                       "aou".id           AS "id",\r
-                       upper("aou".name ) AS "name"\r
-               FROM\r
-                       actor.org_unit     AS "aou" ;\r
-               </programlisting>\r
-               <para>Here we take a substring of the name, using the "params" element to pass parameters:</para>\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": {\r
+               "aou": [\r
+                       "id",\r
+                       { "column":"name", "transform":"upper" }\r
+               ]\r
+       }\r
+}\r
+               \r
+SELECT\r
+       "aou".id           AS "id",\r
+       upper("aou".name ) AS "name"\r
+FROM\r
+       actor.org_unit     AS "aou" ;\r
+</programlisting>\r
+               <para>Here we take a substring of the name, using the <emphasis role="italics">params</emphasis> element to pass parameters:</para>\r
                <programlisting>\r
                {\r
                        "from":"aou",\r
                FROM\r
                        actor.org_unit AS "aou" ;\r
                </programlisting>\r
-               <para>The parameters specified with <quote>params</quote> are inserted after the applicable column (<quote>name</quote> in this case), which is always the first parameter. They are always                     passed as strings, i.e. enclosed in quotes, even if the JSON expresses them as numbers. PostgreSQL will ordinarily coerce them to the right type. However if the function name is overloaded                    to accept different types, PostgreSQL may invoke a function other than the one intended.</para>\r
+               <para>The parameters specified with <emphasis role="italics">params</emphasis> are inserted after the applicable column (<emphasis role="italics">name</emphasis> in this case), \r
+               which is always the first parameter. They are always passed as strings, i.e. enclosed in quotes, even if the JSON expresses them as numbers. PostgreSQL will ordinarily \r
+               coerce them to the right type. However if the function name is overloaded to accept different types, PostgreSQL may invoke a function other than the one intended.</para>\r
                <para>Finally we call a fictitious function "frobozz" that returns multiple columns, where we want only one of them:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": {\r
-                               "aou": [\r
-                                       "id", {\r
-                                               "column":"name",\r
-                                               "transform":"frobozz",\r
-                                               "result_field":"zamzam"\r
-                                       }\r
-                               ]\r
-                       }\r
-               }\r
-                               \r
-               SELECT\r
-                       "aou".id                        AS "id",\r
-                       (frobozz("aou".name ))."zamzam" AS "name"\r
-               FROM\r
-                       actor.org_unit                  AS "aou" ;\r
-               </programlisting>\r
-               <para>The "frobozz" function doesn't actually exist, but json_query doesn't know that. The query won't fail until json_query tries to execute it in \r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": {\r
+               "aou": [\r
+                       "id", {\r
+                               "column":"name",\r
+                               "transform":"frobozz",\r
+                               "result_field":"zamzam"\r
+                       }\r
+               ]\r
+       }\r
+}\r
+               \r
+SELECT\r
+       "aou".id                        AS "id",\r
+       (frobozz("aou".name ))."zamzam" AS "name"\r
+FROM\r
+       actor.org_unit                  AS "aou" ;\r
+</programlisting>\r
+               <para>The <emphasis role="italics">frobozz</emphasis> function doesn't actually exist, but json_query doesn't know that. The query won't fail until json_query tries to execute it in \r
                the database.</para>\r
        </simplesect>\r
        <simplesect>\r
                to them (and it has to be the first parameter).</para>  \r
                <para>You can't use a CASE expression. Instead, the client application can do the equivalent branching for itself.</para>\r
                <para>You can't select a subquery. In raw SQL you can do something like the following:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       id,\r
-                       name,\r
-                       (\r
-                               SELECT name\r
-                               FROM actor.org_unit_type AS aout\r
-                               WHERE aout.id = aou.ou_type\r
-                       ) AS type_name\r
-               FROM\r
-                       actor.org_unit AS aou;\r
-               </programlisting>\r
+<programlisting>\r
+SELECT\r
+       id,\r
+       name,\r
+       (\r
+               SELECT name\r
+               FROM actor.org_unit_type AS aout\r
+               WHERE aout.id = aou.ou_type\r
+       ) AS type_name\r
+FROM\r
+       actor.org_unit AS aou;\r
+</programlisting>\r
                <para>This contrived example is not very realistic. Normally you would use a JOIN in this case, and that's what you should do in a JSON query. Other cases may not be so \r
                easy to solve.</para>\r
        </simplesect>\r
        <simplesect>\r
                <title>The WHERE Clause</title>\r
                <para>Most queries need a WHERE clause, as in this simple example:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "parent_ou":"3"\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "parent_ou":"3"\r
+       }\r
+}\r
+</programlisting>\r
                <para>Like the SELECT clause, the WHERE clause gets its own entry in the top-level object of a JSON query. The key is <quote>where</quote>, and the associated value is either \r
                an object (as shown here) or an array (to be discussed a bit later). Each entry in the object is a separate condition.</para>\r
                <para>In this case, we use a special shortcut for expressing an equality condition. The column name is on the left of the colon, and the value to which we are equating it is on \r
                the right.</para>\r
                <para>Here's the resulting SQL:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       "aou".id       AS "id",\r
-                       "aou".name     AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       "aou".parent_ou = 3;\r
-               </programlisting>\r
+<programlisting>\r
+SELECT\r
+       "aou".id       AS "id",\r
+       "aou".name     AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       "aou".parent_ou = 3;\r
+</programlisting>\r
                <para>Like the SELECT clause, the generated WHERE clause qualifies each column name with the alias of the relevant table.</para>\r
                <para>If you want to compare a column to NULL, put <quote>null</quote> (without quotation marks) to the right of the colon instead of a literal value. The \r
                resulting SQL will include <quote>IS NULL</quote> instead of an equals sign.</para>\r
        <simplesect>\r
                <title>Other Kinds of Comparisons</title>\r
                <para>Here's the same query (which generates the same SQL) without the special shortcut:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "parent_ou":{ "=":3 }\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "parent_ou":{ "=":3 }\r
+       }\r
+}\r
+</programlisting>\r
                <para>We still have an entry whose key is the column name, but this time the associated value is another JSON object. It must contain exactly one entry, \r
                with the comparison operator on the left of the colon, and the value to be compared on the right.</para>\r
                <para>The same syntax works for other kinds of comparison operators. For example:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "parent_ou":{ ">":3 }\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "parent_ou":{ ">":3 }\r
+       }\r
+}\r
+</programlisting>\r
                <para>...turns into:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       "aou".id       AS "id",\r
-                       "aou".name     AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       "aou".parent_ou > 3 ;\r
-               </programlisting>\r
+<programlisting>\r
+SELECT\r
+       "aou".id       AS "id",\r
+       "aou".name     AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       "aou".parent_ou > 3 ;\r
+</programlisting>\r
                <para>The condition '<quote>=</quote>:null' turns into IS NULL. Any other operator used with <quote>null</quote> turns into IS NOT NULL.</para>\r
                <para>You can use most of the comparison operators recognized by PostgreSQL:</para>\r
                <literallayout> \r
                any semicolons or white space, in order to prevent certain kinds of SQL injection. It also allows "similar to" as a special exception.</para>\r
                <para>As a result, you can slip an operator of your own devising into the SQL, so long as it doesn't contain any semicolons or white space, and doesn't create invalid syntax. \r
                Here's a contrived and rather silly example:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "parent_ou":{ "&lt;2+":3 }\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "parent_ou":{ "&lt;2+":3 }\r
+       }\r
+}\r
+</programlisting>\r
                <para>...which results in the following SQL:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       "aou".id       AS "id",\r
-                       "aou".name     AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       "aou".parent_ou &lt;2+ 3;\r
-               </programlisting>\r
+<programlisting>\r
+SELECT\r
+       "aou".id       AS "id",\r
+       "aou".name     AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       "aou".parent_ou &lt;2+ 3;\r
+</programlisting>\r
                <para>It's hard to come up with a realistic case where this hack would be useful, but it could happen.</para>\r
        </simplesect>\r
-\r
-\r
        <simplesect>\r
                <title>Comparing One Column to Another</title>\r
                <para>Here's how to put another column on the right hand side of a comparison:</para>\r
-               \r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "id": { "&gt;": { "+aou":"parent_ou" } }\r
-                       }\r
-               };\r
-               </programlisting>\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "id": { "&gt;": { "+aou":"parent_ou" } }\r
+       }\r
+};\r
+</programlisting>\r
                <para>This syntax is similar to the previous examples, except that instead of comparing to a literal value, we compare to an object. This object has only a single entry, \r
                whose key is a table alias preceded by a leading plus sign. The associated value is the name of the column.</para>\r
                <para>Here's the resulting SQL:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aou".name AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-               (\r
-                       "aou".id &gt; (  "aou".parent_ou  )\r
-               );\r
-               </programlisting>\r
+<programlisting>\r
+SELECT\r
+       "aou".id AS "id",\r
+       "aou".name AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+(\r
+       "aou".id &gt; (  "aou".parent_ou  )\r
+);\r
+</programlisting>\r
                <para>The table alias must correspond to the appropriate table. Since json_query doesn't validate the choice of alias, it won't detect an invalid alias until it tries to \r
                execute the query. In this simple example there's only one table to choose from. The choice of alias is more important in a subquery or join.</para>\r
                <para>The leading plus sign, combined with a table alias, can be used in other situations to designate the table to which a column belongs. We shall defer a discussion of \r
        <simplesect>\r
                <title>Testing Boolean Columns</title>\r
                <para>In SQL, there are several ways to test a boolean column such as actor.org_unit.opac_visible. The most obvious way is to compare it to true or false:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       id\r
-               FROM\r
-                       actor.org_unit\r
-               WHERE\r
-                       opac_visible = true;\r
-               </programlisting>\r
+<programlisting>\r
+SELECT\r
+       id\r
+FROM\r
+       actor.org_unit\r
+WHERE\r
+       opac_visible = true;\r
+</programlisting>\r
                <para>In a JSON query this approach doesn't work. If you try it, the "= true" test will turn into IS NULL. Don't do that. Instead, use a leading plus sign, as described in \r
                the preceding section, to treat the boolean column as a stand-alone condition:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id" ] },\r
-                       "where": {\r
-                               "+aou":"opac_visible"\r
-                       }\r
-               }\r
-               </programlisting>\r
-\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id" ] },\r
+       "where": {\r
+               "+aou":"opac_visible"\r
+       }\r
+}\r
+</programlisting>\r
                <para>Result:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       "aou".id AS "id"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       "aou".opac_visible ;\r
-               </programlisting>\r
+<programlisting>\r
+SELECT\r
+       "aou".id AS "id"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       "aou".opac_visible ;\r
+</programlisting>\r
                <para>If you need to test for falsity, then write a test for truth and negate it with the "-not" operator. We will discuss the "-not" operator later, but here's a preview:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id" ] },\r
-                       "where": {\r
-                               "-not": {\r
-                                       "+aou":"opac_visible"\r
-                               }\r
-                       }\r
-               }                       \r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id" ] },\r
+       "where": {\r
+               "-not": {\r
+                       "+aou":"opac_visible"\r
+               }\r
+       }\r
+}                      \r
 \r
-               SELECT\r
-                       "aou".id AS "id"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       NOT (  "aou".opac_visible  );           \r
-               </programlisting>\r
+SELECT\r
+       "aou".id AS "id"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       NOT (  "aou".opac_visible  );           \r
+</programlisting>\r
                <para>You can also compare a boolean column directly to a more complex condition:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id" ] },\r
-                       "where": {\r
-                               "opac_visible": {\r
-                               "=": { "parent_ou":{ "&gt;":3 } }\r
-                               }\r
-                        }\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id" ] },\r
+       "where": {\r
+               "opac_visible": {\r
+               "=": { "parent_ou":{ "&gt;":3 } }\r
                }\r
-               </programlisting>\r
+        }\r
+}\r
+</programlisting>\r
                <para>Here we compare a boolean column, not to a literal value, but to a boolean expression. The resulting SQL looks a little goofy, but it works:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       "aou".id AS "id"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       (\r
-                               "aou".opac_visible = ( "aou".parent_ou &gt; 3 )\r
-                       );\r
-               </programlisting>\r
+<programlisting>\r
+SELECT\r
+       "aou".id AS "id"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       (\r
+               "aou".opac_visible = ( "aou".parent_ou &gt; 3 )\r
+       );\r
+</programlisting>\r
                <para>In this case we compare the boolean column to a single simple condition. However you can include additional complications -- multiple conditions, IN lists, \r
                BETWEEN clauses, and other features as described below.</para>\r
        </simplesect>\r
        <simplesect>\r
                <title>Multiple Conditions</title>\r
                <para>If you need multiple conditions, just add them to the "where" object, separated by commas:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "parent_ou":{ "&gt;":3 },\r
-                               "id":{ "&lt;&gt;":7 }\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "parent_ou":{ "&gt;":3 },\r
+               "id":{ "&lt;&gt;":7 }\r
+       }\r
+}\r
+</programlisting>\r
                <para>The generated SQL connects the conditions with AND:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       "aou".id       AS "id",\r
-                       "aou".name     AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       "aou".parent_ou g 3\r
-                       AND "aou".id &lt;&gt; 7;\r
-               </programlisting>\r
+<programlisting>\r
+SELECT\r
+       "aou".id       AS "id",\r
+       "aou".name     AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       "aou".parent_ou g 3\r
+       AND "aou".id &lt;&gt; 7;\r
+</programlisting>\r
                <para>Later we will see how to use OR instead of AND.</para>\r
        </simplesect>\r
        <simplesect>\r
                <title>Using Arrays</title>\r
                <para>Here's a puzzler. Suppose you need two conditions for the same column. How do you code them in the same WHERE clause? For example, suppose you want something like this:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       id,\r
-                       name\r
-               FROM\r
-                       actor.org_unit\r
-               WHERE\r
-                       parent_ou &gt; 3\r
-                       AND parent_ou &lt;&gt; 7;\r
-               </programlisting>\r
+<programlisting>\r
+SELECT\r
+       id,\r
+       name\r
+FROM\r
+       actor.org_unit\r
+WHERE\r
+       parent_ou &gt; 3\r
+       AND parent_ou &lt;&gt; 7;\r
+</programlisting>\r
                <para>You might try a WHERE clause like this:</para>\r
-               <programlisting>\r
-               "where": {\r
-                       "parent_ou":{ "&gt;":3 },\r
-                       "parent_ou":{ "&lt;&gt;":7 }\r
               }\r
-               </programlisting>\r
+<programlisting>\r
+"where": {\r
+       "parent_ou":{ "&gt;":3 },\r
+       "parent_ou":{ "&lt;&gt;":7 }\r
+ }\r
+</programlisting>\r
                <para>Nope. Won't work. According to JSON rules, two entries in the same object can't have the same key.</para>\r
                <para>After slapping yourself in the forehead, you try something a little smarter:</para>\r
-               <programlisting>\r
-               "where": {\r
-                       "parent_ou": {\r
-                               "&gt;":3,\r
-                               "&lt;&gt;":7\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>\r
+"where": {\r
+       "parent_ou": {\r
+               "&gt;":3,\r
+               "&lt;&gt;":7\r
+       }\r
+}\r
+</programlisting>\r
                <para>Nice try, but that doesn't work either. Maybe it ought to work -- at least it's legal JSON -- but, no.</para>\r
                <para>Here's what works:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": [\r
-                               { "parent_ou":{ "&gt;":3 } },\r
-                               { "parent_ou":{ "&lt;>":7 } }\r
-                       ]\r
-               }\r
-               </programlisting>\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": [\r
+               { "parent_ou":{ "&gt;":3 } },\r
+               { "parent_ou":{ "&lt;>":7 } }\r
+       ]\r
+}\r
+</programlisting>\r
                <para>We wrapped the two conditions into two separate JSON objects, and then wrapped those objects together into a JSON array. The resulting SQL looks like this:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       "aou".id       AS "id",\r
-                       "aou".name     AS "name\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       ( "aou".parent_ou &gt; 3 )\r
-               AND\r
-                       ( "aou".parent_ou &lt;&gt; 7 );\r
-               </programlisting>\r
+<programlisting>\r
+SELECT\r
+       "aou".id       AS "id",\r
+       "aou".name     AS "name\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       ( "aou".parent_ou &gt; 3 )\r
+AND\r
+       ( "aou".parent_ou &lt;&gt; 7 );\r
+</programlisting>\r
                <para>That's not quite what we were hoping for, because the extra parentheses are so ugly. But they're harmless. This will do.</para>\r
                <para>If you're in the mood, you can use arrays to as many parentheses as you like, even if there is only one condition inside:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where":\r
-                       [[[[[[\r
-                               {\r
-                                       "parent_ou":{ "&gt;":3 }\r
-                                },\r
-                       ]]]]]]\r
-               }       \r
-               </programlisting>\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where":\r
+       [[[[[[\r
+               {\r
+                       "parent_ou":{ "&gt;":3 }\r
+                },\r
+       ]]]]]]\r
+}      \r
+</programlisting>\r
                <para>...yields:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       "aou".id       AS "id",\r
-                       "aou".name     AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       ( ( ( ( ( ( "aou".parent_ou &gt; 3 ) ) ) ) ) );\r
-               </programlisting>\r
+<programlisting>\r
+SELECT\r
+       "aou".id       AS "id",\r
+       "aou".name     AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       ( ( ( ( ( ( "aou".parent_ou &gt; 3 ) ) ) ) ) );\r
+</programlisting>\r
        </simplesect>\r
        <simplesect>\r
                <title>How to OR</title>\r
                <para>By default, json_query combines conditions with AND. When you need OR, here's how to do it:</para>                \r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "-or": {\r
-                                       "id":2,\r
-                                       "parent_ou":3\r
-                               }\r
-                       }\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "-or": {\r
+                       "id":2,\r
+                       "parent_ou":3\r
                }\r
-               </programlisting>\r
-               <para>We use "-or" as the key, with the conditions to be ORed in an associated object. The leading minus sign is there to make sure that the operator isn't confused with a \r
+       }\r
+}\r
+</programlisting>\r
+               <para>We use <quote>-or</quote> as the key, with the conditions to be ORed in an associated object. The leading minus sign is there to make sure that the operator isn't confused with a \r
                column name. Later we'll see some other operators with leading minus signs. In a couple of spots we even use plus signs.</para>\r
                <para>Here are the results from the above example:</para>\r
-               <programlisting>\r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aou".name AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       (\r
-                               "aou".id = 2\r
-                               OR "aou".parent_ou = 3\r
-                       );\r
-               </programlisting>\r
-               <para>The conditions paired with "-or" are linked by OR and enclosed in parentheses.</para>\r
+<programlisting>\r
+SELECT\r
+       "aou".id AS "id",\r
+       "aou".name AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       (\r
+               "aou".id = 2\r
+               OR "aou".parent_ou = 3\r
+       );\r
+</programlisting>\r
+               <para>The conditions paired with <quote>-or</quote> are linked by OR and enclosed in parentheses.</para>\r
                <para>Here's how to do the same thing using an array, except that it produces an extra layer of parentheses:</para>\r
-               <programlisting>\r
-               {\r
-                   "from":"aou",\r
-                   "select": { "aou":[ "id", "name" ] },\r
-                   "where": {\r
-                       "-or": [\r
-                           { "id":2 },\r
-                           { "parent_ou":3 }\r
-                       ]\r
-                   }\r
-               }\r
-               SELECT\r
-                   "aou".id AS "id",\r
-                   "aou".name AS "name"\r
-               FROM\r
-                   actor.org_unit AS "aou"\r
-               WHERE\r
-                   (\r
-                       ( "aou".id = 2 )\r
-                       OR ( "aou".parent_ou = 3 )\r
-                   );\r
-               </programlisting>\r
-               <para>It's possible, though not very useful, to have only a single condition subject to the "-or" operator. In that case, the condition appears by itself, since there's nothing \r
+<programlisting>\r
+{\r
+    "from":"aou",\r
+    "select": { "aou":[ "id", "name" ] },\r
+    "where": {\r
+       "-or": [\r
+           { "id":2 },\r
+           { "parent_ou":3 }\r
+       ]\r
+    }\r
+}\r
+SELECT\r
+    "aou".id AS "id",\r
+    "aou".name AS "name"\r
+FROM\r
+    actor.org_unit AS "aou"\r
+WHERE\r
+    (\r
+       ( "aou".id = 2 )\r
+       OR ( "aou".parent_ou = 3 )\r
+    );\r
+</programlisting>\r
+               <para>It's possible, though not very useful, to have only a single condition subject to the <quote>-or</quote> operator. In that case, the condition appears by itself, since there's nothing \r
                to OR it to. This trick is another way to add an extraneous layer of parentheses.</para>\r
        </simplesect>\r
        <simplesect>\r
                <title>Another way to AND</title>\r
-               <para>You can also use the <quote>-and</quote> operator. It works just like "-or", except that it combines conditions with AND instead of OR. Since AND is the default, we don't usually \r
+               <para>You can also use the <quote>-and</quote> operator. It works just like <quote>-or</quote>, except that it combines conditions with AND instead of OR. Since AND is the default, we don't usually \r
                need a separate operator for it, but it's available.</para>\r
                <para>In rare cases, nothing else will do -- you can't include two conditions in the same list because of the duplicate key problem, but you can't combine them with \r
                arrays either. In particular, you might need to combine them within an expression that you're comparing to a boolean column (see the subsection above on Testing Boolean Columns).</para>\r
        <simplesect>\r
                <title>Negation with NOT</title>\r
                <para>The <quote>-not</quote> operator negates a condition or set of conditions. For example:</para>\r
-               <programlisting>\r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "-not": {\r
-                                       "id":{ "&gt;":2 },\r
-                                       "parent_ou":3\r
-                               }\r
-                       }\r
+<programlisting>\r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "-not": {\r
+                       "id":{ "&gt;":2 },\r
+                       "parent_ou":3\r
                }\r
-                               \r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aou".name AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       NOT\r
-                       (\r
-                               "aou".id &gt; 2\r
-                               AND "aou".parent_ou = 3\r
-                       );\r
-               </programlisting>\r
+       }\r
+}\r
+               \r
+SELECT\r
+       "aou".id AS "id",\r
+       "aou".name AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       NOT\r
+       (\r
+               "aou".id &gt; 2\r
+               AND "aou".parent_ou = 3\r
+       );\r
+</programlisting>\r
                <para>In this example we merely negate a combination of two comparisons. However the condition to be negated may be as complicated as it needs to be. Anything that can be \r
                subject to <quote>where</quote> can be subject to <quote>-not</quote>.</para>\r
                <para>In most cases you can achieve the same result by other means. However the <quote>-not</quote> operator is the only way to represent NOT BETWEEN \r
                <title>EXISTS with Subqueries</title>\r
                <para>Two other operators carry a leading minus sign: <quote>-exists</quote> and its negation <quote>-not-exists</quote>. These operators apply to subqueries, which have the \r
                same format as a full query. For example:</para>\r
-               <programlisting>        \r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "-exists": {\r
-                                       "from":"asv",\r
-                                       "select":{ "asv":[ "id" ] },\r
-                                       "where": {\r
-                                               "owner":7\r
-                                       }\r
-                               }\r
-                       }\r
+<programlisting>       \r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "-exists": {\r
+                       "from":"asv",\r
+                       "select":{ "asv":[ "id" ] },\r
+                       "where": {\r
+                               "owner":7\r
+                       }\r
                }\r
-                               \r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aou".name AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-               EXISTS\r
-                       (\r
-                       SELECT "asv".id AS "id"\r
-                       FROM action.survey AS "asv"\r
-                       WHERE "asv".owner = 7\r
-                       );\r
-               </programlisting>\r
+       }\r
+}\r
+               \r
+SELECT\r
+       "aou".id AS "id",\r
+       "aou".name AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+EXISTS\r
+       (\r
+       SELECT "asv".id AS "id"\r
+       FROM action.survey AS "asv"\r
+       WHERE "asv".owner = 7\r
+       );\r
+</programlisting>\r
                <para>This kind of subquery is of limited use, because its WHERE clause doesn't have anything to do with the main query. It just shuts down the main query altogether \r
                if it isn't satisfied.</para>\r
                <para>More typical is a correlated subquery, whose WHERE clause refers to a row from the main query. For example:</para>\r
-               <programlisting>        \r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "-exists": {\r
-                                       "from":"asv",\r
-                                       "select":{ "asv":[ "id" ] },\r
-                                       "where": {\r
-                                               "owner":{ "=":{ "+aou":"id" }}\r
-                                       }\r
-                               }\r
-                        }\r
-               }       \r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "-exists": {\r
+                       "from":"asv",\r
+                       "select":{ "asv":[ "id" ] },\r
+                       "where": {\r
+                               "owner":{ "=":{ "+aou":"id" }}\r
+                       }\r
+               }\r
+        }\r
+}      \r
+</programlisting>\r
                <para>Note the use of <quote>+aou</quote> to qualify the id column in the inner WHERE clause.</para>\r
-               <programlisting>        \r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aou".name AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       EXISTS\r
-                       (\r
-                               SELECT  "asv".id AS "id"\r
-                               FROM action.survey AS "asv"\r
-                               WHERE ("asv".owner = (  "aou".id  ))\r
-                       );\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+       "aou".id AS "id",\r
+       "aou".name AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       EXISTS\r
+       (\r
+               SELECT  "asv".id AS "id"\r
+               FROM action.survey AS "asv"\r
+               WHERE ("asv".owner = (  "aou".id  ))\r
+       );\r
+</programlisting>\r
                <para>This latter example illustrates the syntax, but in practice, it would probably be more natural to use an IN clause with a subquery (to be discussed later).</para>\r
        </simplesect>\r
        <simplesect>\r
                <title>BETWEEN Clauses</title>\r
                <para>Here's how to express a BETWEEN clause:</para>\r
-               <programlisting>        \r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id" ] },\r
-                       "where": {\r
-                               "parent_ou": { "between":[ 3, 7 ] }\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id" ] },\r
+       "where": {\r
+               "parent_ou": { "between":[ 3, 7 ] }\r
+       }\r
+}\r
+</programlisting>\r
                <para>The value associated with the column name is an object with a single entry, whose key is "between". The corresponding value is an array with exactly two values, defining the \r
                range to be tested.</para>\r
                <para>The range bounds must be either numbers or string literals. Although SQL allows them to be null, a null doesn't make sense in this context, because a null never matches \r
                anything. Consequently json_query doesn't allow them.</para>\r
                <para>The resulting SQL is just what you would expect:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                       "aou".id AS "id"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       parent_ou BETWEEN '3' AND '7';\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+       "aou".id AS "id"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       parent_ou BETWEEN '3' AND '7';\r
+</programlisting>\r
        </simplesect>\r
        <simplesect>\r
                <title>IN and NOT IN Lists</title>\r
                <para>There are two ways to code an IN list. One way is simply to include the list of values in an array:</para>\r
-               <programlisting>        \r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "parent_ou": [ 3, 5, 7 ]\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "parent_ou": [ 3, 5, 7 ]\r
+       }\r
+}\r
+</programlisting>\r
                <para>As with a BETWEEN clause, the values in the array must be numbers or string literals. Nulls aren't allowed. Here's the resulting SQL, which again is just what \r
                you would expect:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aou".name AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       "aou".parent_ou IN (3, 5, 7);\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+       "aou".id AS "id",\r
+       "aou".name AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       "aou".parent_ou IN (3, 5, 7);\r
+</programlisting>\r
                <para>The other way is similar to the syntax shown above for a BETWEEN clause, except that the array may include any non-zero number of values:</para>\r
-               <programlisting>        \r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "parent_ou": { "in": [ 3, 5, 7 ] }\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "parent_ou": { "in": [ 3, 5, 7 ] }\r
+       }\r
+}\r
+</programlisting>\r
                <para>This version results in the same SQL as the first one.</para>\r
                <para>For a NOT IN list, you can use the latter format, using the <quote>not in</quote> operator instead of <quote>in</quote>. Alternatively, you can use either format together with \r
                the <quote>-not</quote> operator.</para>        \r
                <title>IN and NOT IN Clauses with Subqueries</title>\r
                <para>For an IN clause with a subquery, the syntax is similar to the second of the two formats for an IN list (see the previous subsection). The "in" or "not in" operator \r
                is paired, not with an array of values, but with an object representing the subquery. For example:</para>\r
-               <programlisting>        \r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "id": {\r
-                                       "in": {\r
-                                               "from":"asv",\r
-                                               "select":{ "asv":[ "owner" ] },\r
-                                               "where":{ "name":"Voter Registration" }\r
-                                               }\r
-                                       }\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "id": {\r
+                       "in": {\r
+                               "from":"asv",\r
+                               "select":{ "asv":[ "owner" ] },\r
+                               "where":{ "name":"Voter Registration" }\r
+                               }\r
+                       }\r
+       }\r
+}\r
+</programlisting>\r
                <para>The results:</para>\r
-               <programlisting>        \r
+<programlisting>       \r
+SELECT\r
+       "aou".id AS "id",\r
+       "aou".name AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       "aou".id IN\r
+       (\r
                SELECT\r
-                       "aou".id AS "id",\r
-                       "aou".name AS "name"\r
+                       "asv".owner AS "owner"\r
                FROM\r
-                       actor.org_unit AS "aou"\r
+                       action.survey AS "asv"\r
                WHERE\r
-                       "aou".id IN\r
-                       (\r
-                               SELECT\r
-                                       "asv".owner AS "owner"\r
-                               FROM\r
-                                       action.survey AS "asv"\r
-                               WHERE\r
-                                       "asv".name = 'Voter Registration'\r
-                       );\r
-               </programlisting>\r
+                       "asv".name = 'Voter Registration'\r
+       );\r
+</programlisting>\r
                <para>In SQL the subquery may select multiple columns, but in a JSON query it can select only a single column.</para>\r
                <para>For a NOT IN clause with a subquery, use the <quote>not in</quote> operator instead of <quote>in</quote>.</para>\r
        </simplesect>\r
        <simplesect>\r
                <title>Comparing to a Function</title>\r
                <para>Here's how to compare a column to a function call:</para>\r
-               <programlisting>        \r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "id":{ ">":[ "sqrt", 16 ] }\r
-                        }\r
-               }\r
-               </programlisting>\r
-               <para>A comparison operator ("&gt;" in this case) is paired with an array. The first entry in the array must be a string giving the name of the function. The remaining parameters, \r
+<programlisting>       \r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "id":{ ">":[ "sqrt", 16 ] }\r
+        }\r
+}\r
+</programlisting>\r
+               <para>A comparison operator (<quote>&gt;</quote> in this case) is paired with an array. The first entry in the array must be a string giving the name of the function. The remaining parameters, \r
                if any, are the parameters. They may be strings, numbers, or nulls. The resulting SQL for this example:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aou".name AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       "aou".id > sqrt( '16' );\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+       "aou".id AS "id",\r
+       "aou".name AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       "aou".id > sqrt( '16' );\r
+</programlisting>\r
                <para>All parameters are passed as quoted strings -- even if, as in this case, they are really numbers.</para>\r
                <para>This syntax is somewhat limited in that the function parameters must be constants (hence the use of a silly example).</para>\r
        </simplesect>\r
                <para>In the discussion of the SELECT clause, we saw how you could transform the value of a selected column by passing it to a function. In the WHERE clause, you can \r
                use similar syntax to transform the value of a column before comparing it to something else.</para>\r
                <para>For example:</para>\r
-               <programlisting>        \r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "name": {\r
-                                       "=": {\r
-                                               "transform":"upper",\r
-                                               "value":"CARTER BRANCH"\r
-                                       }\r
-                               }\r
-                       }\r
+<programlisting>       \r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "name": {\r
+                       "=": {\r
+                               "transform":"upper",\r
+                               "value":"CARTER BRANCH"\r
+                       }\r
                }\r
-               </programlisting>\r
+       }\r
+}\r
+</programlisting>\r
                <para>The "transform" entry gives the name of the function that we will use on the left side of the comparison. The "value" entry designates the value on the right side \r
                of the comparison.</para>\r
-               <programlisting>        \r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aou".name AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-                       upper("aou".name ) =  'CARTER BRANCH' ;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+       "aou".id AS "id",\r
+       "aou".name AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+       upper("aou".name ) =  'CARTER BRANCH' ;\r
+</programlisting>\r
                <para>As in the SELECT clause, you can pass literal values or nulls to the function as additional parameters by using an array tagged as <quote>params</quote>:</para>\r
-               <programlisting>        \r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "name": {\r
-                                       "=": {\r
-                                               "transform":"substr",\r
-                                               "params":[ 1, 6 ],\r
-                                               "value":"CARTER"\r
-                                       }\r
-                               }\r
-                        }\r
+<programlisting>       \r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "name": {\r
+                       "=": {\r
+                               "transform":"substr",\r
+                               "params":[ 1, 6 ],\r
+                               "value":"CARTER"\r
+                       }\r
                }\r
-                       \r
-               SELECT\r
-                   "aou".id AS "id",\r
-                   "aou".name AS "name"\r
-               FROM\r
-                   actor.org_unit AS "aou"\r
-               WHERE\r
-                   substr("aou".name,'1','6' ) =  'CARTER' ;\r
-               </programlisting>\r
+        }\r
+}\r
+       \r
+SELECT\r
+    "aou".id AS "id",\r
+    "aou".name AS "name"\r
+FROM\r
+    actor.org_unit AS "aou"\r
+WHERE\r
+    substr("aou".name,'1','6' ) =  'CARTER' ;\r
+</programlisting>\r
                <para>The first parameter is always the column name, qualified by the class name, followed by any additional parameters (which are always enclosed in quotes even if they \r
                are numeric).</para>\r
                <para>As in the SELECT clause: if the function returns multiple columns, you can specify the one you want by using a "result_field" entry (not shown here).</para>\r
 \r
        <simplesect>\r
                <title>Putting Function Calls on Both Sides</title>\r
-               <para>If you want to compare one function call to another, you can use the same syntax shown in the previous subsection -- except that the "value" entry carries an \r
+               <para>If you want to compare one function call to another, you can use the same syntax shown in the previous subsection -- except that the <quote>value</quote> entry carries an \r
                array instead of a literal value. For example:</para>\r
-               <programlisting>        \r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "id": {\r
-                                       "&gt;": {\r
-                                               "transform":"factorial",\r
-                                               "value":[ "sqrt", 1000 ]\r
-                                       }\r
-                               }\r
-                       }\r
-               }               \r
-               SELECT\r
-                   "aou".id AS "id",\r
-                   "aou".name AS "name"\r
-               FROM\r
-                   actor.org_unit AS "aou"\r
-               WHERE\r
-                   factorial("aou".id ) &gt;  sqrt( '1000' ) ;\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "id": {\r
+                       "&gt;": {\r
+                               "transform":"factorial",\r
+                               "value":[ "sqrt", 1000 ]\r
+                       }\r
+               }\r
+       }\r
+}              \r
+SELECT\r
+    "aou".id AS "id",\r
+    "aou".name AS "name"\r
+FROM\r
+    actor.org_unit AS "aou"\r
+WHERE\r
+    factorial("aou".id ) &gt;  sqrt( '1000' ) ;\r
+</programlisting>\r
                <para>The format for the right side function is similar to what we saw earlier, in the subsection Comparing to a Function. Note that there are two different formats \r
                for defining function calls:</para>\r
                <itemizedlist>\r
        </simplesect>\r
        <simplesect>\r
                <title>Comparing a Function to a Condition</title>\r
-               <para>So far we have seen two kinds of data for the "value" tag. A string or number translates to a literal value, and an array translates to a function call. \r
+               <para>So far we have seen two kinds of data for the <quote>value</quote> tag. A string or number translates to a literal value, and an array translates to a function call. \r
                The third possibility is a JSON object, which translates to a condition. For example:</para>\r
-               <programlisting>        \r
-               {\r
-                       "from":"aou",\r
-                       "select": { "aou":[ "id", "name" ] },\r
-                       "where": {\r
-                               "id": {\r
-                                        "=": {\r
-                                               "value":{ "parent_ou":{ ">":3 } },\r
-                                               "transform":"is_prime"\r
-                                       }\r
-                               }\r
-                        }\r
+<programlisting>       \r
+{\r
+       "from":"aou",\r
+       "select": { "aou":[ "id", "name" ] },\r
+       "where": {\r
+               "id": {\r
+                        "=": {\r
+                               "value":{ "parent_ou":{ ">":3 } },\r
+                               "transform":"is_prime"\r
+                       }\r
                }\r
-               </programlisting>\r
+        }\r
+}\r
+</programlisting>\r
                <para>The function tagged as <quote>transform</quote> must return boolean, or else json_query will generate invalid SQL. The function used here, <quote>is_prime</quote>, \r
                is fictitious.</para>\r
-               <programlisting>        \r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aou".name AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-               WHERE\r
-               (\r
-                       is_prime("aou".id ) = ( "aou".parent_ou > 3 )\r
-               );\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+       "aou".id AS "id",\r
+       "aou".name AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+WHERE\r
+(\r
+       is_prime("aou".id ) = ( "aou".parent_ou > 3 )\r
+);\r
+</programlisting>\r
                <para>If we left out the <quote>transform</quote> entry, json_query would compare the column on the left (which would to be boolean) to the condition on the right. The results are similar \r
                to those for a simpler format described earlier (see the subsection Testing Boolean Columns).</para>\r
                <para>In the example above we compared the boolean to a simple condition. However the expression on the right may include multiple conditions, IN lists, subqueries, \r
                the class name of the relevant table.</para>\r
                <para>When the FROM clause joins multiple tables, the corresponding JSON naturally gets more complicated.</para>\r
                <para>SQL provides two ways to define a join. One way is to list both tables in the FROM clause, and put the join conditions in the WHERE clause:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                       aou.id,\r
-                       aout.name\r
-               FROM\r
-                       actor.org_unit aou,\r
-                       actor.org_unit_type aout\r
-               WHERE\r
-                       aout.id = aou.ou_type;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+       aou.id,\r
+       aout.name\r
+FROM\r
+       actor.org_unit aou,\r
+       actor.org_unit_type aout\r
+WHERE\r
+       aout.id = aou.ou_type;\r
+</programlisting>\r
                <para>The other way is to use an explicit JOIN clause:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                       aou.id,\r
-                       aout.name\r
-               FROM\r
-                       actor.org_unit aou\r
-                               JOIN actor.org_unit_type aout\r
-                                       ON ( aout.id = aou.ou_type );\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+       aou.id,\r
+       aout.name\r
+FROM\r
+       actor.org_unit aou\r
+               JOIN actor.org_unit_type aout\r
+                       ON ( aout.id = aou.ou_type );\r
+</programlisting>\r
                <para>JSON queries use only the second of these methods. The following example expresses the same query in JSON:</para>\r
-               <programlisting>        \r
-               {\r
-                       "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
-                       "from": {\r
-                               "aou":"aout"\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+       "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
+       "from": {\r
+               "aou":"aout"\r
+       }\r
+}\r
+</programlisting>\r
                <para>First, let's review the SELECT clause. Since it selects rows from two different tables, the data for <quote>select</quote> includes two entries, one for each table.</para>\r
                <para>As for the FROM clause, it's no longer just a string. It's a JSON object, with exactly one entry. The key of this entry is the class name of the core table, i.e. \r
                the table named immediately after the FROM keyword. The data associated with this key contains the rest of the information about the join. In this simple example, \r
                that information consists entirely of a string containing the class name of the other table.</para>\r
                <para>So where is the join condition?</para>\r
                <para>It's in the IDL. Upon reading the IDL, json_query knows that actor.org_unit has a foreign key pointing to actor.org_unit_type, and builds a join condition accordingly:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aout".name AS "name"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-                               INNER JOIN actor.org_unit_type AS "aout"\r
-                                       ON ( "aout".id = "aou".ou_type ) ;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+       "aou".id AS "id",\r
+       "aout".name AS "name"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+               INNER JOIN actor.org_unit_type AS "aout"\r
+                       ON ( "aout".id = "aou".ou_type ) ;\r
+</programlisting>\r
                <para>In this case the core table is the child table, and the joined table is the parent table. We could just as well have written it the other way around:</para>\r
-               <programlisting>        \r
-               {\r
-                        "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
-                       "from": {\r
-                               "aout":"aou"\r
-                       }\r
-               }\r
-                       \r
-               SELECT\r
-                   "aou".id AS "id",\r
-                   "aout".name AS "name"\r
-               FROM\r
-                   actor.org_unit_type AS "aout"\r
-                       INNER JOIN actor.org_unit AS "aou"\r
-                           ON ( "aou".ou_type = "aout".id ) ;\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+        "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
+       "from": {\r
+               "aout":"aou"\r
+       }\r
+}\r
+       \r
+SELECT\r
+    "aou".id AS "id",\r
+    "aout".name AS "name"\r
+FROM\r
+    actor.org_unit_type AS "aout"\r
+       INNER JOIN actor.org_unit AS "aou"\r
+           ON ( "aou".ou_type = "aout".id ) ;\r
+</programlisting>\r
        </simplesect>\r
        <simplesect>\r
                <title>Specifying The Join Columns Explicitly</title>\r
                Json_query can't guess which one you want if you don't tell it.</para>\r
                <para>(Actually it will try to guess. It will pick the first matching link that it finds in the IDL, which may or may not be the one you want.)</para>\r
                <para>Here's how to define exactly which columns you want for the join:</para>\r
-               <programlisting>        \r
-               {\r
-                       "select": { "aou":[ "id" ], "aoa":[ "street1" ] },\r
-                       "from": {\r
-                               "aou": {\r
-                                       "aoa": {\r
-                                               "fkey":"holds_address",\r
-                                               "field":"id"\r
-                                       }\r
-                               }\r
-                       }\r
+<programlisting>       \r
+{\r
+       "select": { "aou":[ "id" ], "aoa":[ "street1" ] },\r
+       "from": {\r
+               "aou": {\r
+                       "aoa": {\r
+                               "fkey":"holds_address",\r
+                               "field":"id"\r
+                       }\r
                }\r
-               </programlisting>\r
+       }\r
+}\r
+</programlisting>\r
                <para>Before, the table we were joining was represented merely by its class name. Now it's represented by an entry in a JSON object. The key of that entry is the \r
                class name, and the associated data is another layer of JSON object containing the attributes of the join.</para>\r
                <para>Later we'll encounter other kinds of join attributes. For now, the only attributes that we're looking at are the ones that identify the join columns: \r
                <para>When there are only two tables involved, the core table is on the left, and the non-core table is on the right. In more complex queries neither table may be the \r
                core table.</para>\r
                <para>Here is the result of the preceding JSON:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                   "aou".id AS "id",\r
-                   "aoa".street1 AS "street1"\r
-               FROM\r
-                   actor.org_unit AS "aou"\r
-                       INNER JOIN actor.org_address AS "aoa"\r
-                           ON ( "aoa".id = "aou".holds_address ) ;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+    "aou".id AS "id",\r
+    "aoa".street1 AS "street1"\r
+FROM\r
+    actor.org_unit AS "aou"\r
+       INNER JOIN actor.org_address AS "aoa"\r
+           ON ( "aoa".id = "aou".holds_address ) ;\r
+</programlisting>\r
                <para>In this example the child table is on the left and the parent table is on the right. We can swap the tables if we swap the join columns as well:</para>\r
-               <programlisting>        \r
-               {\r
-                       "select": { "aou":[ "id" ], "aoa":[ "street1" ] },\r
-                       "from": {\r
-                               "aoa": {\r
-                                       "aou": {\r
-                                               "fkey":"id",\r
-                                               "field":"holds_address"\r
-                                       }\r
-                               }\r
-                       }\r
+<programlisting>       \r
+{\r
+       "select": { "aou":[ "id" ], "aoa":[ "street1" ] },\r
+       "from": {\r
+               "aoa": {\r
+                       "aou": {\r
+                               "fkey":"id",\r
+                               "field":"holds_address"\r
+                       }\r
                }\r
-                               \r
-               SELECT\r
-                   "aou".id AS "id",\r
-                   "aoa".street1 AS "street1"\r
-               FROM\r
-                   actor.org_address AS "aoa"\r
-                       INNER JOIN actor.org_unit AS "aou"\r
-                           ON ( "aou".holds_address = "aoa".id ) ;\r
-               </programlisting>\r
+       }\r
+}\r
+               \r
+SELECT\r
+    "aou".id AS "id",\r
+    "aoa".street1 AS "street1"\r
+FROM\r
+    actor.org_address AS "aoa"\r
+       INNER JOIN actor.org_unit AS "aou"\r
+           ON ( "aou".holds_address = "aoa".id ) ;\r
+</programlisting>\r
                <para>When you specify both of the join columns, json_query assumes that you know what you're doing. It doesn't check the IDL to confirm that the join makes sense. \r
                The burden is on you to avoid absurdities.</para>\r
        </simplesect>\r
                <title>Specifying Only One Join Column</title>\r
                <para>We just saw how to specify both ends of a join. It turns out that there's a shortcut -- most of the time you only need to specify one end. Consider \r
                the following variation on the previous example:</para>\r
-               <programlisting>        \r
-               {\r
-                       "select": { "aou":[ "id" ], "aoa":[ "street1" ] },\r
-                       "from": {\r
-                               "aoa": {\r
-                                       "aou": {\r
-                                               "field":"holds_address"\r
-                                       }\r
-                               }\r
-                        }\r
+<programlisting>       \r
+{\r
+       "select": { "aou":[ "id" ], "aoa":[ "street1" ] },\r
+       "from": {\r
+               "aoa": {\r
+                       "aou": {\r
+                               "field":"holds_address"\r
+                       }\r
                }\r
-               </programlisting>\r
+        }\r
+}\r
+</programlisting>\r
                <para>..which results in exactly the same SQL as before.</para>\r
                <para>Here we specified the join column from the child table, the column that is a foreign key pointing to another table. As long as that linkage is defined in the IDL, \r
                json_query can look it up and figure out what the corresponding column is in the parent table.</para>\r
                <title>Joining to Multiple Tables</title>\r
                <para>So far we have joined only two tables at a time. What if we need to join one table to two different tables?</para>\r
                <para>Here's an example:</para>\r
-               <programlisting>        \r
-               {\r
-                       "select": { "aou":[ "id" ], "aout":[ "depth" ], "aoa":[ "street1" ] },\r
-                       "from": {\r
-                               "aou": {\r
-                                       "aout":{},\r
-                                       "aoa": {\r
-                                               "fkey":"holds_address"\r
-                                       }\r
-                               }\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+       "select": { "aou":[ "id" ], "aout":[ "depth" ], "aoa":[ "street1" ] },\r
+       "from": {\r
+               "aou": {\r
+                       "aout":{},\r
+                       "aoa": {\r
+                               "fkey":"holds_address"\r
+                       }\r
+               }\r
+       }\r
+}\r
+</programlisting>\r
                <para>The first join, to actor.org_unit_type, is simple. We could have specified join columns, but we don't have to, because json_query will construct that join on the basis of \r
                what it finds in the IDL. Having no join attributes to specify, we leave that object empty.</para>\r
                <para>For the second join, to actor.org_address, we have to specify at least the join column in the child table, as discussed earlier. We could also have specified the join \r
                column from the parent table, but we don't have to, so we didn't.</para>\r
                <para>Here is the resulting SQL:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aout".depth AS "depth",\r
-                       "aoa".street1 AS "street1"\r
-               FROM\r
-                       actor.org_unit AS "aou"\r
-                               INNER JOIN actor.org_unit_type AS "aout"\r
-                                       ON ( "aout".id = "aou".ou_type )\r
-                               INNER JOIN actor.org_address AS "aoa"\r
-                                       ON ( "aoa".id = "aou".holds_address ) ;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+       "aou".id AS "id",\r
+       "aout".depth AS "depth",\r
+       "aoa".street1 AS "street1"\r
+FROM\r
+       actor.org_unit AS "aou"\r
+               INNER JOIN actor.org_unit_type AS "aout"\r
+                       ON ( "aout".id = "aou".ou_type )\r
+               INNER JOIN actor.org_address AS "aoa"\r
+                       ON ( "aoa".id = "aou".holds_address ) ;\r
+</programlisting>\r
                <para>Since there can be only one core table, the outermost object in the FROM clause can have only one entry, whose key is the class name of the core table. The next \r
                level has one entry for every table that's joined to the core table.</para>\r
        </simplesect>\r
                <title>Nested Joins</title>\r
                <para>Let's look at that last query again. It joins three tables, and the core table is the one in the middle. Can we make one of the end tables the core table instead?</para>\r
                <para>Yes, we can:</para>\r
-               <programlisting>        \r
-               {\r
-                       "select": { "aou":[ "id" ], "aout":[ "depth" ], "aoa":[ "street1" ] },\r
-                       "from": {\r
-                               "aoa": {\r
-                                       "aou": {\r
-                                               "field":"holds_address",\r
-                                               "join": {\r
-                                                       "aout":{ "fkey":"ou_type" }\r
-                                               }\r
-                                       }\r
-                               }\r
-                       }\r
+<programlisting>       \r
+{\r
+       "select": { "aou":[ "id" ], "aout":[ "depth" ], "aoa":[ "street1" ] },\r
+       "from": {\r
+               "aoa": {\r
+                       "aou": {\r
+                               "field":"holds_address",\r
+                               "join": {\r
+                                       "aout":{ "fkey":"ou_type" }\r
+                               }\r
+                       }\r
                }\r
-               </programlisting>\r
-               <para>The "join" attribute introduces another level of join. In this case "aou" is the left table for the nested join, and the right table for the original join. \r
+       }\r
+}\r
+</programlisting>\r
+               <para>The <quote>join</quote> attribute introduces another level of join. In this case "aou" is the left table for the nested join, and the right table for the original join. \r
                Here are the results:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aout".depth AS "depth",\r
-                       "aoa".street1 AS "street1"\r
-               FROM\r
-                       actor.org_address AS "aoa"\r
-                               INNER JOIN actor.org_unit AS "aou"\r
-                                       ON ( "aou".holds_address = "aoa".id )\r
-                                               INNER JOIN actor.org_unit_type AS "aout"\r
-                                                       ON ( "aout".id = "aou".ou_type ) ;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+       "aou".id AS "id",\r
+       "aout".depth AS "depth",\r
+       "aoa".street1 AS "street1"\r
+FROM\r
+       actor.org_address AS "aoa"\r
+               INNER JOIN actor.org_unit AS "aou"\r
+                       ON ( "aou".holds_address = "aoa".id )\r
+                               INNER JOIN actor.org_unit_type AS "aout"\r
+                                       ON ( "aout".id = "aou".ou_type ) ;\r
+</programlisting>\r
        </simplesect>\r
        <simplesect>\r
                <title>Outer Joins</title>\r
                <para>By default, json_query constructs an inner join. If you need an outer join, you can add the join type as an attribute of the join:</para>\r
                <para>Yes, we can:</para>\r
-               <programlisting>        \r
-               {\r
-                       "select": { "aou":[ "id" ], "aoa":[ "street1" ] },\r
-                       "from": {\r
-                               "aoa": {\r
-                                       "aou": {\r
-                                               "field":"mailing_address",\r
-                                               "type":"left"\r
-                                       }\r
-                               }\r
-                       }\r
+<programlisting>       \r
+{\r
+       "select": { "aou":[ "id" ], "aoa":[ "street1" ] },\r
+       "from": {\r
+               "aoa": {\r
+                       "aou": {\r
+                               "field":"mailing_address",\r
+                               "type":"left"\r
+                       }\r
                }\r
-               </programlisting>\r
+       }\r
+}\r
+</programlisting>\r
                <para>Here is the resulting SQL for this example:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                   "aou".id AS "id",\r
-                   "aoa".street1 AS "street1"\r
-               FROM\r
-                   actor.org_address AS "aoa"\r
-                       LEFT JOIN actor.org_unit AS "aou"\r
-                           ON ( "aou".mailing_address = "aoa".id ) ;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+    "aou".id AS "id",\r
+    "aoa".street1 AS "street1"\r
+FROM\r
+    actor.org_address AS "aoa"\r
+       LEFT JOIN actor.org_unit AS "aou"\r
+           ON ( "aou".mailing_address = "aoa".id ) ;\r
+</programlisting>\r
        </simplesect>\r
        <simplesect>\r
                <title>Referring to Joined Tables in the WHERE Clause</title>\r
                <para>In the WHERE clause of the generated SQL, every column name is qualified by a table alias, which is always the corresponding class name.</para>\r
                <para>If a column belongs to the core table, this qualification happens by default. If it belongs to a joined table, the JSON must specify what class name \r
                to use for an alias. For example:</para>\r
-               <programlisting>        \r
-               {\r
-                       "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
-                       "from": {\r
-                               "aout":"aou"\r
-                       },\r
-                       "where": {\r
-                               "+aou":{ "parent_ou":2 }\r
-                       }\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+       "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
+       "from": {\r
+               "aout":"aou"\r
+       },\r
+       "where": {\r
+               "+aou":{ "parent_ou":2 }\r
+       }\r
+}\r
+</programlisting>\r
                <para>Note the peculiar operator <quote>+aou</quote> -- a plus sign followed by the relevant class name. This operator tells json_query to apply the specified class to the condition that \r
                follows. The result:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                       "aou".id AS "id",\r
-                       "aout".name AS "name"\r
-               FROM\r
-                       actor.org_unit_type AS "aout"\r
-                       INNER JOIN actor.org_unit AS "aou"\r
-                               ON ( "aou".ou_type = "aout".id )\r
-               WHERE\r
-                       ( "aou".parent_ou = 2 );\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+       "aou".id AS "id",\r
+       "aout".name AS "name"\r
+FROM\r
+       actor.org_unit_type AS "aout"\r
+       INNER JOIN actor.org_unit AS "aou"\r
+               ON ( "aou".ou_type = "aout".id )\r
+WHERE\r
+       ( "aou".parent_ou = 2 );\r
+</programlisting>\r
                <para>The plus-class operator may apply to multiple conditions:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
-                   "from": {\r
-                       "aout":"aou"\r
-                   },\r
-                   "where": {\r
-                       "+aou":{\r
-                           "parent_ou":2,\r
-                           "id":{ "&lt;":42 }\r
-                       }\r
-                   }\r
-               }\r
-                               \r
-               SELECT\r
-                   "aou".id AS "id",\r
-                   "aout".name AS "name"\r
-               FROM\r
-                   actor.org_unit_type AS "aout"\r
-                       INNER JOIN actor.org_unit AS "aou"\r
-                           ON ( "aou".ou_type = "aout".id )\r
-               WHERE\r
-                   (\r
-                       "aou".parent_ou = 2\r
-                       AND "aou".id &lt; 42\r
-                   );\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+    "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
+    "from": {\r
+       "aout":"aou"\r
+    },\r
+    "where": {\r
+       "+aou":{\r
+           "parent_ou":2,\r
+           "id":{ "&lt;":42 }\r
+       }\r
+    }\r
+}\r
+               \r
+SELECT\r
+    "aou".id AS "id",\r
+    "aout".name AS "name"\r
+FROM\r
+    actor.org_unit_type AS "aout"\r
+       INNER JOIN actor.org_unit AS "aou"\r
+           ON ( "aou".ou_type = "aout".id )\r
+WHERE\r
+    (\r
+       "aou".parent_ou = 2\r
+       AND "aou".id &lt; 42\r
+    );\r
+</programlisting>\r
                <para>For these artificial examples, it would have been simpler to swap the tables, so that actor.org_unit is the core table. Then you wouldn't need to go through any \r
                special gyrations to apply the right table alias. In a more realistic case, however, you might need to apply conditions to both tables. Just swapping the tables \r
                wouldn't solve the problem.</para>\r
                <para>You can also use a plus-class operator to compare columns from two different tables:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
-                   "from": {\r
-                       "aout":"aou"\r
-                   },\r
-                   "where": {\r
-                       "depth": { "&gt;": { "+aou":"parent_ou" } }\r
-                   }\r
-               }\r
-                               \r
+<programlisting>       \r
+{\r
+    "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
+    "from": {\r
+       "aout":"aou"\r
+    },\r
+    "where": {\r
+       "depth": { "&gt;": { "+aou":"parent_ou" } }\r
+    }\r
+}\r
+               \r
 \r
-               SELECT\r
-                   "aou".id AS "id",\r
-                   "aout".name AS "name"\r
-               FROM\r
-                   actor.org_unit_type AS "aout"\r
-                       INNER JOIN actor.org_unit AS "aou"\r
-                           ON ( "aou".ou_type = "aout".id )\r
-               WHERE\r
-                   (\r
-                       "aout".depth &gt; (  "aou".parent_ou  )\r
-                   );\r
-               </programlisting>\r
+SELECT\r
+    "aou".id AS "id",\r
+    "aout".name AS "name"\r
+FROM\r
+    actor.org_unit_type AS "aout"\r
+       INNER JOIN actor.org_unit AS "aou"\r
+           ON ( "aou".ou_type = "aout".id )\r
+WHERE\r
+    (\r
+       "aout".depth &gt; (  "aou".parent_ou  )\r
+    );\r
+</programlisting>\r
                <para>Please don't expect that query to make any sense. It doesn't. But it illustrates the syntax.</para>\r
        </simplesect>\r
        <simplesect>\r
                <title>Join Filters</title>\r
                <para>While the above approach certainly works, the special syntax needed is goofy and awkward. A somewhat cleaner solution is to include a condition in the JOIN clause:</para>\r
-               \r
-               <programlisting>        \r
-               {\r
-                   "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
-                   "from": {\r
-                       "aout": {\r
-                           "aou": {\r
-                               "filter": {\r
-                                   "parent_ou":2\r
-                               }\r
-                           }\r
-                       }\r
-                   }\r
-               }                       \r
+<programlisting>       \r
+{\r
+    "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
+    "from": {\r
+       "aout": {\r
+           "aou": {\r
+               "filter": {\r
+                   "parent_ou":2\r
+               }\r
+           }\r
+       }\r
+    }\r
+}                      \r
 \r
-               SELECT\r
-                   "aou".id AS "id", "aout".name AS "name"\r
-               FROM\r
-                   actor.org_unit_type AS "aout"\r
-                       INNER JOIN actor.org_unit AS "aou"\r
-                           ON ( "aou".ou_type = "aout".id\r
-                                AND  "aou".parent_ou = 2 ) ;\r
-               </programlisting>\r
+SELECT\r
+    "aou".id AS "id", "aout".name AS "name"\r
+FROM\r
+    actor.org_unit_type AS "aout"\r
+       INNER JOIN actor.org_unit AS "aou"\r
+           ON ( "aou".ou_type = "aout".id\r
+                AND  "aou".parent_ou = 2 ) ;\r
+</programlisting>\r
                <para>By default, json_query uses AND to combine the <quote>filter</quote> condition with the original join condition. If you need OR, you can use the <quote>filter_op</quote> attribute to \r
                say so:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
-                   "from": {\r
-                       "aout": {\r
-                           "aou": {\r
-                               "filter": {\r
-                                   "parent_ou":2\r
-                               },\r
-                               "filter_op":"or"\r
-                           }\r
-                       }\r
-                   }\r
-               }               \r
+<programlisting>       \r
+{\r
+    "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
+    "from": {\r
+       "aout": {\r
+           "aou": {\r
+               "filter": {\r
+                   "parent_ou":2\r
+               },\r
+               "filter_op":"or"\r
+           }\r
+       }\r
+    }\r
+}              \r
 \r
-               SELECT\r
-                   "aou".id AS "id",\r
-                   "aout".name AS "name"\r
-               FROM\r
-                   actor.org_unit_type AS "aout"\r
-                       INNER JOIN actor.org_unit AS "aou"\r
-                           ON ( "aou".ou_type = "aout".id\r
-                                OR  "aou".parent_ou = 2 ) ;\r
-               </programlisting>\r
+SELECT\r
+    "aou".id AS "id",\r
+    "aout".name AS "name"\r
+FROM\r
+    actor.org_unit_type AS "aout"\r
+       INNER JOIN actor.org_unit AS "aou"\r
+           ON ( "aou".ou_type = "aout".id\r
+                OR  "aou".parent_ou = 2 ) ;\r
+</programlisting>\r
                <para>If the data tagged by <quote>filter_op</quote> is anything but <quote>or</quote> (in upper, lower, or mixed case), json_query uses AND instead of OR.</para>\r
                <para>The condition tagged by <quote>filter</quote> may be much more complicated. In fact it accepts all the same syntax as the WHERE clause.</para>\r
                <para>Remember, though, that it all gets combined with the the original join condition with an AND, or with an OR if you so specify. If \r
                <title>Joining to a Subquery</title>\r
                <para>In SQL you can put a subquery in a FROM clause, and select from it as if it were a table. A JSON query has no way to do that directly. The IDL, however, \r
                can define a class as a subquery instead of as a table. When you SELECT from it, json_query inserts the corresponding subquery into the FROM clause. For example:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select":{ "iatc":[ "id", "dest", "copy_status" ] },\r
-                   "from": "iatc"\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+    "select":{ "iatc":[ "id", "dest", "copy_status" ] },\r
+    "from": "iatc"\r
+}\r
+</programlisting>\r
                <para>There's nothing special-looking about this JSON, but json_query expands it as follows:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                   "iatc".id AS "id",\r
-                   "iatc".dest AS "dest",\r
-                   "iatc".copy_status AS "copy_status"\r
-               FROM\r
-                   (\r
-                       SELECT  t.*\r
-                       FROM\r
-                           action.transit_copy t\r
-                               JOIN actor.org_unit AS s\r
-                                   ON (t.source = s.id)\r
-                               JOIN actor.org_unit AS d\r
-                                   ON (t.dest = d.id)\r
-                       WHERE\r
-                           s.parent_ou &lt;&gt; d.parent_ou\r
-                   ) AS "iatc" ;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+    "iatc".id AS "id",\r
+    "iatc".dest AS "dest",\r
+    "iatc".copy_status AS "copy_status"\r
+FROM\r
+    (\r
+       SELECT  t.*\r
+       FROM\r
+           action.transit_copy t\r
+               JOIN actor.org_unit AS s\r
+                   ON (t.source = s.id)\r
+               JOIN actor.org_unit AS d\r
+                   ON (t.dest = d.id)\r
+       WHERE\r
+           s.parent_ou &lt;&gt; d.parent_ou\r
+    ) AS "iatc" ;\r
+</programlisting>\r
                <para>The <quote>iatc</quote> class is like a view, except that it's defined in the IDL instead of the database. In this case it provides a way to do a join that would otherwise be \r
                impossible through a JSON query, because it joins the same table in two different ways (see the next subsection).</para>\r
        </simplesect>\r
                condition, or to omit any join condition in order to obtain a Cartesian product. If necessary, you can devise such unconventional joins by combining the normal join \r
                conditions with join filters.</para>\r
                <para>For example, here's how to get a Cartesian product:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
-                   "from": {\r
-                       "aout": {\r
-                           "aou": {\r
-                               "filter": {\r
-                                   "ou_type":{ "&lt;&gt;": { "+aout":"id" } }\r
-                               },\r
-                               "filter_op":"or"\r
-                           }\r
-                       }\r
-                   }\r
-               }\r
-                       \r
+<programlisting>       \r
+{\r
+    "select": { "aou":[ "id" ], "aout":[ "name" ] },\r
+    "from": {\r
+       "aout": {\r
+           "aou": {\r
+               "filter": {\r
+                   "ou_type":{ "&lt;&gt;": { "+aout":"id" } }\r
+               },\r
+               "filter_op":"or"\r
+           }\r
+       }\r
+    }\r
+}\r
+       \r
 \r
-               SELECT\r
-                   "aou".id AS "id",\r
-                   "aout".name AS "name"\r
-               FROM\r
-                   actor.org_unit_type AS "aout"\r
-                       INNER JOIN actor.org_unit AS "aou"\r
-                           ON\r
-                           (\r
-                               "aou".ou_type = "aout".id\r
-                               OR  ("aou".ou_type &lt;&gt; (  "aout".id  ))\r
-                           ) ;\r
-               </programlisting>\r
+SELECT\r
+    "aou".id AS "id",\r
+    "aout".name AS "name"\r
+FROM\r
+    actor.org_unit_type AS "aout"\r
+       INNER JOIN actor.org_unit AS "aou"\r
+           ON\r
+           (\r
+               "aou".ou_type = "aout".id\r
+               OR  ("aou".ou_type &lt;&gt; (  "aout".id  ))\r
+           ) ;\r
+</programlisting>\r
                <para>Yes, it's ugly, but at least you're not likely to do it by accident.</para>\r
        </simplesect>\r
        <simplesect>\r
                <title>Selecting from Functions</title>\r
                <para>In SQL, you can put a function call in the FROM clause. The function may return multiple columns and multiple rows. Within the query, the function behaves like a table.</para>\r
                <para>A JSON query can also select from a function:</para>\r
-               <programlisting>        \r
-               {\r
-                   "from": [ "actor.org_unit_ancestors", 5 ]\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+    "from": [ "actor.org_unit_ancestors", 5 ]\r
+}\r
+</programlisting>\r
                <para>The data associated with <quote>from</quote> is an array instead of a string or an object. The first element in the array specifies the name of the function. Subsequent elements, \r
                if any, supply the parameters of the function; they must be literal values or nulls.</para>\r
                <para>Here is the resulting query:</para>\r
-               <programlisting>        \r
-               SELECT *\r
-               FROM\r
-                   actor.org_unit_ancestors( '5' ) AS "actor.org_unit_ancestors" ;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT *\r
+FROM\r
+       actor.org_unit_ancestors( '5' ) AS "actor.org_unit_ancestors" ;\r
+</programlisting>\r
                <para>In a JSON query this format is very limited, largely because the IDL knows nothing about the available functions. You can't join the function to a table or to \r
                another function. If you try to supply a SELECT list or a WHERE clause, json_query will ignore it. The generated query will always select every column, via a wild card asterisk, \r
                from every row.</para>\r
        <simplesect>\r
                <title>The ORDER BY Clause</title>\r
                <para>In most cases you can encode an ORDER BY clause as either an array or an object. Let's take a simple example and try it both ways. First the array:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select":{ "aou":[ "name" ] },\r
-                   "from": "aou",\r
-                   "order_by": [\r
-                       { "class":"aou", "field":"name" }\r
-                   ]\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+    "select":{ "aou":[ "name" ] },\r
+    "from": "aou",\r
+    "order_by": [\r
+       { "class":"aou", "field":"name" }\r
+    ]\r
+}\r
+</programlisting>\r
                <para>Now the object:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select":{ "aou":[ "name" ] },\r
-                   "from": "aou",\r
-                   "order_by": {\r
-                       "aou":{ "name":{} }\r
-                   }\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+    "select":{ "aou":[ "name" ] },\r
+    "from": "aou",\r
+    "order_by": {\r
+       "aou":{ "name":{} }\r
+    }\r
+}\r
+</programlisting>\r
                <para>The results are identical from either version:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                   "aou".name AS "name"\r
-               FROM\r
-                   actor.org_unit AS "aou"\r
-               ORDER BY\r
-                   "aou".name;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+    "aou".name AS "name"\r
+FROM\r
+    actor.org_unit AS "aou"\r
+ORDER BY\r
+    "aou".name;\r
+</programlisting>\r
                <para>The array format is more verbose, but as we shall see, it is also more flexible. It can do anything the object format can do, plus some things that the object \r
                format can't do.</para>\r
        </simplesect>\r
                </itemizedlist> \r
                <para>If you want to sort by multiple fields, just include a separate object for each field.</para>\r
                <para>If you want to sort a field in descending order, add a <quote>direction</quote> tag:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select":{ "aou":[ "name" ] },\r
-                   "from": "aou",\r
-                   "order_by": [\r
-                       {\r
-                           "class":"aou",\r
-                           "field":"name",\r
-                           "transform":"upper"\r
-                       }\r
-                   ]\r
-               }\r
-                               \r
+<programlisting>       \r
+{\r
+    "select":{ "aou":[ "name" ] },\r
+    "from": "aou",\r
+    "order_by": [\r
+       {\r
+           "class":"aou",\r
+           "field":"name",\r
+           "transform":"upper"\r
+       }\r
+    ]\r
+}\r
+               \r
 \r
-               SELECT\r
-                   "aou".name AS "name"\r
-               FROM\r
-                   actor.org_unit AS "aou"\r
-               ORDER BY\r
-                   upper("aou".name );\r
-               </programlisting>\r
+SELECT\r
+    "aou".name AS "name"\r
+FROM\r
+    actor.org_unit AS "aou"\r
+ORDER BY\r
+    upper("aou".name );\r
+</programlisting>\r
                <para>If you need additional parameters for the function, you can use the <quote>params</quote> tag to pass them:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select":{ "aou":[ "name" ] },\r
-                   "from": "aou",\r
-                   "order_by": [\r
-                       {\r
-                           "class":"aou",\r
-                           "field":"name",\r
-                           "transform":"substr",\r
-                           "params":[ 1, 8 ]\r
-                       }\r
-                   ]\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+    "select":{ "aou":[ "name" ] },\r
+    "from": "aou",\r
+    "order_by": [\r
+       {\r
+           "class":"aou",\r
+           "field":"name",\r
+           "transform":"substr",\r
+           "params":[ 1, 8 ]\r
+       }\r
+    ]\r
+}\r
+</programlisting>\r
                <para>The additional parameters appear as elements in an array. They may be numbers, strings, or nulls.</para>\r
-               <programlisting>        \r
-               SELECT\r
-                   "aou".name AS "name"\r
-               FROM\r
-                   actor.org_unit AS "aou"\r
-               ORDER BY\r
-                   substr("aou".name,'1','8' );\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+    "aou".name AS "name"\r
+FROM\r
+    actor.org_unit AS "aou"\r
+ORDER BY\r
+    substr("aou".name,'1','8' );\r
+</programlisting>\r
                <para>As we have seen elsewhere, all literal values are passed as quoted strings, even if they are numbers.</para>\r
                <para>If the function returns multiple columns, you can use the <quote>result_field</quote> tag to indicate which one you want (not shown).</para>\r
        </simplesect>\r
                <title>ORDER BY as an Object</title>\r
                <para>When you encode the ORDER BY clause as an object, the keys of the object are class names. Each class must be either the core class or a joined class. The data for \r
                each class can be either an array or another layer of object. Here's an example with one of each:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select":{ "aout":"id", "aou":[ "name" ] },\r
-                   "from": { "aou":"aout" },\r
-                   "order_by": {\r
-                       "aout":[ "id" ],\r
-                       "aou":{ "name":{ "direction":"desc" } }\r
-                   }\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+    "select":{ "aout":"id", "aou":[ "name" ] },\r
+    "from": { "aou":"aout" },\r
+    "order_by": {\r
+       "aout":[ "id" ],\r
+       "aou":{ "name":{ "direction":"desc" } }\r
+    }\r
+}\r
+</programlisting>\r
                <para>For the <quote>aout</quote> class, the associated array is simply a list of field names (in this case, just one). Naturally, each field must reside in the class with which \r
                it is associated.</para>\r
                <para>However, a list of field names provides no way to specify the direction of sorting, or a transforming function. You can add those details only if the class \r
                <para>In this example, we use the <quote>direction"</quote> tag to specify that the name field be sorted in descending order. This tag works the same way here as described earlier. \r
                If the associated string starts with "D" or "d", the sort will be descending; otherwise it will be ascending.</para>\r
                <para>Here is the resulting SQL:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                   "aou".name AS "name"\r
-               FROM\r
-                   actor.org_unit AS "aou"\r
-                       INNER JOIN actor.org_unit_type AS "aout"\r
-                           ON ( "aout".id = "aou".ou_type )\r
-               ORDER BY\r
-                   "aout".id,\r
-                   "aou".name DESC;\r
-               </programlisting>\r
-               <programlisting>\r
-               {\r
-                   "select":{ "aou":[ "name", "id" ] },\r
-                   "from": "aou",\r
-                   "order_by": {\r
-                       "aou":{\r
-                           "name":{ "transform":"substr", "params":[ 1, 8 ] }\r
-                       }\r
-                   }\r
-               }                       \r
+<programlisting>       \r
+SELECT\r
+    "aou".name AS "name"\r
+FROM\r
+    actor.org_unit AS "aou"\r
+       INNER JOIN actor.org_unit_type AS "aout"\r
+           ON ( "aout".id = "aou".ou_type )\r
+ORDER BY\r
+    "aout".id,\r
+    "aou".name DESC;\r
+</programlisting>\r
+<programlisting>\r
+{\r
+    "select":{ "aou":[ "name", "id" ] },\r
+    "from": "aou",\r
+    "order_by": {\r
+       "aou":{\r
+           "name":{ "transform":"substr", "params":[ 1, 8 ] }\r
+       }\r
+    }\r
+}                      \r
 \r
-               SELECT\r
-                   "aou".name AS "name",\r
-                   "aou".id AS "id"\r
-               FROM\r
-                   actor.org_unit AS "aou"\r
-               ORDER BY\r
-                   substr("aou".name,'1','8' );\r
-               </programlisting>\r
+SELECT\r
+    "aou".name AS "name",\r
+    "aou".id AS "id"\r
+FROM\r
+    actor.org_unit AS "aou"\r
+ORDER BY\r
+    substr("aou".name,'1','8' );\r
+</programlisting>\r
        </simplesect>\r
        <simplesect>\r
                <title>Things You Can't Do</title>\r
                there are situations where it can be useful, provided that the column is passed to a transforming function.</para>\r
                <para>For example, you might want a case-insensitive sort, except that for any given letter you want lower case to sort first. For example, you want <quote>diBona</quote> to sort \r
                before <quote>Dibona</quote>. Here's a way to do that, coding the ORDER BY clause as an array:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select":{ "au":[ "family_name", "id" ] },\r
-                   "from": "au",\r
-                   "order_by": [\r
-                       { "class":"au", "field":"family_name", "transform":"upper" },\r
-                       { "class":"au", "field":"family_name" }\r
-                   ]\r
-               }\r
-               SELECT\r
-                       "au".family_name AS "family_name",\r
-                       "au".id AS "id"\r
-               FROM\r
-                       actor.usr AS "au"\r
-               ORDER BY\r
-                       upper("au".family_name ),\r
-                       "au".family_name;\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+    "select":{ "au":[ "family_name", "id" ] },\r
+    "from": "au",\r
+    "order_by": [\r
+       { "class":"au", "field":"family_name", "transform":"upper" },\r
+       { "class":"au", "field":"family_name" }\r
+    ]\r
+}\r
+SELECT\r
+       "au".family_name AS "family_name",\r
+       "au".id AS "id"\r
+FROM\r
+       actor.usr AS "au"\r
+ORDER BY\r
+       upper("au".family_name ),\r
+       "au".family_name;\r
+</programlisting>\r
                <para>Such a sort is not possible where the ORDER BY clause is coded as an object.</para>\r
        </simplesect>\r
        <simplesect>\r
                <para>A JSON query has no separate construct to define a GROUP BY clause. Instead, the necessary information is distributed across the SELECT clause. However, \r
                the way it works is a bit backwards from what you might expect, so pay attention.</para>\r
                <para>Here's an example:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select": {\r
-                       "aou": [\r
-                           { "column":"parent_ou" },\r
-                           { "column":"name", "transform":"max", "aggregate":true }\r
-                       ]\r
-                   },\r
-                   "from": "aou"\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+    "select": {\r
+       "aou": [\r
+           { "column":"parent_ou" },\r
+           { "column":"name", "transform":"max", "aggregate":true }\r
+       ]\r
+    },\r
+    "from": "aou"\r
+}\r
+</programlisting>\r
                <para>The <quote>transform</quote> tag is there just to give us an excuse to do a GROUP BY. What's important to notice is the <quote>aggregate</quote> tag.</para>\r
                <para>Here's the resulting SQL:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                   "aou".parent_ou AS "parent_ou",\r
-                   max("aou".name ) AS "name"\r
-               FROM\r
-                   actor.org_unit AS "aou"\r
-               GROUP BY\r
-                   1;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+    "aou".parent_ou AS "parent_ou",\r
+    max("aou".name ) AS "name"\r
+FROM\r
+    actor.org_unit AS "aou"\r
+GROUP BY\r
+    1;\r
+</programlisting>\r
                <para>The GROUP BY clause references fields from the SELECT clause by numerical reference, instead of by repeating them. Notice that the field it references, \r
                parent_ou, is the one that doesn't carry the <quote>aggregate</quote> tag in the JSON.</para>\r
                <para>Let's state that more generally. The GROUP BY clause includes only the fields that do not carry the <quote>aggregate</quote> tag (or that carry it with a value of false).</para>\r
                <para>JSON queries don't generate DISTINCT clauses. However, they can generate GROUP BY clauses that include every item from the SELECT clause. The effect is the same as \r
                applying DISTINCT to the entire SELECT clause.</para>\r
                <para>For example:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select": {\r
-                       "aou": [\r
-                           "parent_ou",\r
-                           "ou_type"\r
-                       ]\r
-                   },\r
-                   "from":"aou",\r
-                   "distinct":"true"\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+    "select": {\r
+       "aou": [\r
+           "parent_ou",\r
+           "ou_type"\r
+       ]\r
+    },\r
+    "from":"aou",\r
+    "distinct":"true"\r
+}\r
+</programlisting>\r
                <para>Note the <quote>distinct</quote> entry at the top level of the query object, with a value of <quote>true</quote>.</para>\r
-               <programlisting>        \r
-               SELECT\r
-                   "aou".parent_ou AS "parent_ou",\r
-                   "aou".ou_type AS "ou_type"\r
-               FROM\r
-                   actor.org_unit AS "aou"\r
-               GROUP BY\r
-                   1, 2;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+    "aou".parent_ou AS "parent_ou",\r
+    "aou".ou_type AS "ou_type"\r
+FROM\r
+    actor.org_unit AS "aou"\r
+GROUP BY\r
+    1, 2;\r
+</programlisting>\r
                <para>The generated GROUP BY clause references every column in the SELECT clause by number.</para>\r
        </simplesect>\r
        <simplesect>\r
                <para>For a HAVING clause, add a <quote>having</quote> entry at the top level of the query object. For the associated data, you can use all the same syntax \r
                that you can use for a WHERE clause.</para>\r
                <para>Here's a simple example:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select": {\r
-                       "aou": [\r
-                           "parent_ou", {\r
-                               "column":"id",\r
-                               "transform":"count",\r
-                               "alias":"id_count",\r
-                               "aggregate":"true"\r
-                           }\r
-                       ]\r
-                   },\r
-                   "from":"aou",\r
-                   "having": {\r
-                       "id": {\r
-                           "&gt;" : {\r
-                               "transform":"count",\r
-                               "value":6\r
-                           }\r
-                       }\r
-                   }\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+    "select": {\r
+       "aou": [\r
+           "parent_ou", {\r
+               "column":"id",\r
+               "transform":"count",\r
+               "alias":"id_count",\r
+               "aggregate":"true"\r
+           }\r
+       ]\r
+    },\r
+    "from":"aou",\r
+    "having": {\r
+       "id": {\r
+           "&gt;" : {\r
+               "transform":"count",\r
+               "value":6\r
+           }\r
+       }\r
+    }\r
+}\r
+</programlisting>\r
                <para>We use the <quote>aggregate</quote> tag in the SELECT clause to give us a GROUP BY to go with the HAVING. Results:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                   "aou".parent_ou AS "parent_ou",\r
-                   count("aou".id ) AS "id_count"\r
-               FROM\r
-                   actor.org_unit AS "aou"\r
-               GROUP BY\r
-                   1\r
-               HAVING\r
-                   count("aou".id ) &gt;  6 ;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+    "aou".parent_ou AS "parent_ou",\r
+    count("aou".id ) AS "id_count"\r
+FROM\r
+    actor.org_unit AS "aou"\r
+GROUP BY\r
+    1\r
+HAVING\r
+    count("aou".id ) &gt;  6 ;\r
+</programlisting>\r
                <para>In raw SQL we could have referred to <quote>count( 1 )</quote>. But since JSON queries cannot encode arbitrary expressions, we applied the count function to a column that \r
                cannot be null.</para>\r
        </simplesect>\r
        <simplesect>\r
                <title>The LIMIT and OFFSET Clauses</title>\r
                <para>To add an LIMIT or OFFSET clause, add an entry to the top level of a query object. For example:</para>\r
-               <programlisting>        \r
-               {\r
-                   "select": {\r
-                       "aou": [ "id", "name" ]\r
-                   },\r
-                   "from":"aou",\r
-                   "order_by": { "aou":[ "id" ] },\r
-                   "offset": 7,\r
-                   "limit": 42\r
-               }\r
-               </programlisting>\r
+<programlisting>       \r
+{\r
+    "select": {\r
+       "aou": [ "id", "name" ]\r
+    },\r
+    "from":"aou",\r
+    "order_by": { "aou":[ "id" ] },\r
+    "offset": 7,\r
+    "limit": 42\r
+}\r
+</programlisting>\r
                <para>The data associated with <quote>offset</quote> and <quote>limit</quote> may be either a number or a string, but if it's a string, it should have a number inside.</para>\r
                <para>Result:</para>\r
-               <programlisting>        \r
-               SELECT\r
-                  "aou".id AS "id",\r
-                  "aou".name AS "name"\r
-               FROM\r
-                  actor.org_unit AS "aou"\r
-               ORDER BY\r
-                  "aou".id\r
-               LIMIT 42 \r
-               OFFSET 7;\r
-               </programlisting>\r
+<programlisting>       \r
+SELECT\r
+   "aou".id AS "id",\r
+   "aou".name AS "name"\r
+FROM\r
+   actor.org_unit AS "aou"\r
+ORDER BY\r
+   "aou".id\r
+LIMIT 42 \r
+OFFSET 7;\r
+</programlisting>\r
        </simplesect>\r
 </chapter>\r