Differences

This shows you the differences between two versions of the page.

--- documentation:tutorials:json_query [2013/02/26 17:48] – this is a rough conversion from openoffice format lebbeous
+++ documentation:tutorials:json_query [2022/02/10 13:34] (current) – external edit 127.0.0.1
@@ Line 115: / Line 115: @@
-  -  The entire JSON query is an object. In this simple case the object includes only one entry, for the FROM clause. Typically you'll also have entries for the SELECT clause and the WHERE clause, and possibly for HAVING, ORDER BY, LIMIT, or OFFSET clauses. There is no separate entry for a GROUP BY clause, which you can specify by other means.
+  * The entire JSON query is an object. In this simple case the object includes only one entry, for the FROM clause. Typically you'll also have entries for the SELECT clause and the WHERE clause, and possibly for HAVING, ORDER BY, LIMIT, or OFFSET clauses. There is no separate entry for a GROUP BY clause, which you can specify by other means.
-  -  Although all the other entries are optional, you must include an entry for the FROM clause. You cannot, for example, do a SELECT USER the way you can in psql.
+  *  Although all the other entries are optional, you must include an entry for the FROM clause. You cannot, for example, do a SELECT USER the way you can in psql.
-  -  Every column is qualified by an alias for the table. This alias is usually the class name for the table, as defined in the IDL, but you can use an alias of your own choosing for a table in a JOIN clause (not shown here).
+  *  Every column is qualified by an alias for the table. This alias is usually the class name for the table, as defined in the IDL, but you can use an alias of your own choosing for a table in a JOIN clause (not shown here).
-  -  Every column is aliased with the column name. There is a way to choose a different column alias (not shown here).
+  *  Every column is aliased with the column name. There is a way to choose a different column alias (not shown here).
 ===== The SELECT clause =====
@@ Line 187: / Line 186: @@
-  -  "column" -- the column name (required).
+  *  "column" -- the column name (required).
-  -  "alias" -- used to define a column alias, which otherwise defaults to the column name.
+  *  "alias" -- used to define a column alias, which otherwise defaults to the column name.
-  -  "aggregate" -- takes a value of true or false. Don't worry about this one yet. It concerns the use of GROUP BY clauses, which we will examine later.
+  *  "aggregate" -- takes a value of true or false. Don't worry about this one yet. It concerns the use of GROUP BY clauses, which we will examine later.
-  -  "transform" -- the name of an SQL function to be called.
+  *  "transform" -- the name of an SQL function to be called.
-  -  "result_field" -- used with "transform"; specifies an output column of a function that returns multiple columns at a time.
+  *  "result_field" -- used with "transform"; specifies an output column of a function that returns multiple columns at a time.
-  -  "params" -- used with "transform"; provides a list of parameters for the function. They may be strings, numbers, or nulls.
+  *  "params" -- used with "transform"; provides a list of parameters for the function. They may be strings, numbers, or nulls.
 This example assigns a different column alias:
@@ Line 2087: / Line 2086: @@
    JOIN actor.org_unit AS s ON (t.source = s.id)
    JOIN actor.org_unit AS d ON (t.dest = d.id)
-   WHERE s.parent_ou<nowiki> <> </nowiki>d.parent_ou ) AS "iatc" ;
+   WHERE s.parent_ou <> d.parent_ou ) AS "iatc" ;
 </code>
@@ Line 2095: / Line 2094: @@
+==== Choosing the Order of JOINs ====
+As of Evergreen 3.0.2, we support user-defined join order in cstore and friends. Previously, because the join structure of ''oils_sql'' beyond the specification of a single table was only allowed to be represented as a JSON object, it was subject to potential hash key reordering. By supporting an intervening array layer, one can now specify the exact join order of the tables in a join tree.
+For example, given the following JSON object passing through a modern Perl 5 interpreter as a nested hash:
+<code json>
+{
+  select : {
+    acp: ['id'],
+    acn: ['record'],
+    acpl: ['name']
+  },
+  from : {
+    acp: {
+      acn: {filter: {record: 12345} },
+      acpl: null
+    }
+  }
+}
+</code>
+the FROM clause of the query may end up as:
+<code sql>
+FROM acp
+    JOIN acn ON (acp.call_number = acn.id AND acn.record = 12345)
+    JOIN acpl ON (acp.location = acpl.id)
+</code>
+Or as:
+<code sql>
+FROM acp
+    JOIN acpl ON (acp.location = acpl.id)
+    JOIN acn ON (acp.call_number = acn.id AND acn.record = 12345)
+</code>
+In some situations, the join order will matter either to the semantics of the query plan, or to its performance. The following example of the newly supported syntax illustrates how to specify join order:
+<code json>
+{
+  select : {
+    acp: ['id'],
+    acn: ['record'],
+    acpl: ['name']
+  },
+  from : {
+    acp: [
+      {acn: {filter: {record: 12345} } },
+      'acpl'
+    ]
+  }
+}
+</code>
+And the only FROM clause the can be generated is:
+<code sql>
+FROM acp
+    JOIN acn ON (acp.call_number = acn.id AND acn.record = 12345)
+    JOIN acpl ON (acp.location = acpl.id)
+</code>
 ==== Things You Can't Do ====