subQueryColumn and bagItemColumn

subQueryColumn declares a virtual column computed by a sub-query. query is a dictionary with the same keys as the select of a formula: table, columns, where, and optionally order_by and limit. hash THIS correlates it with the outer row. mode chooses the shape of the result. With mode json, the rows become a JSON array. With mode xml, they become XML text. Without mode, the sub-query is a plain scalar sub-select, and it must return one column. A third argument, aggr, is shown in scene 4.

With mode json, PostgreSQL builds the array with jsonb_agg. The driver returns it as a Python list of dictionaries. The keys are the column names of the sub-query. Invoice A-003 has no rows, and its value is None, not an empty list. This is the opposite of to_json inside aggr, which returns an empty array. max_row_price has no mode. It is a scalar sub-select with dtype N, like a select formula.

With mode xml, each row becomes one XML element. Its children are the columns of the sub-query. The element name is built from the table and column names, escaped by PostgreSQL. There is no root element. An invoice without notes gets None. This format is kept from earlier releases. For XML in the Bag format, with a GenRoBag root and rows named r underscore position, use to_xml inside aggr, as in lesson 06, or the aggr argument of the next scene.

aggr takes exactly one call of the aggr grammar of lesson 06: count, sum, avg, min, max, join, to_json or to_xml. The call replaces the columns of the query dictionary. The order_by of the dictionary orders join, to_json and to_xml. The results on an empty set are those of lesson 06. row_count gives zero and product_list an empty string. aggr and mode together raise ValueError. Two calls in aggr raise ValueError. A mode other than json or xml raises ValueError and names aggr.

A column with dtype X holds an XML document. In PostgreSQL it is a text column. The value on the slide is a Bag document. specs contains weight, with a type attribute and a unit attribute, and color. A query with columns star leaves X columns out. The query option bagFields True includes them. Lesson 14 covers query options.

bagItemColumn reads one item of the XML document. bagcolumn is the column that holds the document. itempath is a dotted path inside it. A question mark followed by a name reads an attribute of the item. The column is computed with the PostgreSQL xpath function. The path is a bound parameter of the column. The text is cast to the declared dtype, here numeric. A missing document or a missing item gives None. A bag item column works in where and order_by too. For example, where detail_weight greater than ten returns only the desk.

The scalar form of subQueryColumn works on both dialects. The json and xml modes work only on PostgreSQL. On SQLite they raise UnsupportedFeatureError when the query is compiled. The aggr form follows lesson 06: everything works on SQLite except to_xml. bagItemColumn needs xpath, so it works only on PostgreSQL.

A sub-query column is scalar, a JSON or XML collection, or one aggr call. An X column holds an XML document, and a bag item column reads one value from it on PostgreSQL. The next lesson covers layered recipes.