Tilda JSON Syntax: View Columns

<-- View Syntax

Columns

Views contain columns as a baseline for their definition. That’s pretty obvious. Although many elements are shared with Object columns, view columns are very different: for example, they allow aggregates, expressions, are picked from a source column in either another view or a root table etc... A view column looks like the following:

// A regular view column
{ "sameAs":"someSchema.SomeTable.someColumn" // let's assume it's a date time column
 ,"name": "someColumnX"
 ,"coalesce":'1111-11-11'
 ,"as":"_2"
 ,"description":"Type"
 ,"formulaOnly": true|false
 ,"joinOnly": true|false
}

// An aggregate view column
{ "sameAs":"someSchema.SomeTable.someColumnInt" 
 ,"name": "someColumnIntX"
 ,"description":"ColumnInt X blah blah blah"
 ,"aggregate": "SUM"|"AVG"|"MIN"|"FIRST"|"MAX"|"LAST"|"DEV"|"VAR"|"COUNT"|"ARRAY"
 ,"orderBy":["col1","col2"]
 ,"distinct": true|false
 ,"filter": "\"colA\" > 10"
}

// An expression view column
{ "sameAs":"someSchema.SomeTable.someColumnDateTime" // let's assume it's a date time column
 ,"name": "someColumnDateTimeX"
 ,"description":"ColumnDate X blah blah blah"
 ,"expression":"?::DATE" // convert to a DATE
 ,"type": "DATE" // new type
 ,"size": 2 // only for STRING type expressions
}

// A .* view column
{ "sameAs":"someSchema.SomeTable2.*"
 ,"prefix":"abc_"
 ,"exclude":["col1","col2"]
 ,"block":["colA","colB"]
}

The main fields are:

• sameAs: the mandatory source of the column, which could come from a Table or another View. the format is <package_name>.<schema_name>.<table_name|view_name>.<column_name>. If referencing a column in the same schema as the defined view, <package_name>.<schema_name> doesn't need to be specified.
• name: an optional renaming of the column. This is useful if creating a view over multiple tables where there are duplicate column names. See the Tilda Naming Convention.
• coalesce: a value to coalesce to in case the original column has a null value. Tilda will check that the value is compatible to the type of the underlying column.
• as: A string, typically of the form "_2" or "_3" that is used to identify which joined table/view to use in case of a naming conflict.
• formulaOnly: true|false, false by default, indicating whether this column should be visible to formulas only, i.e., the column woldn't be exposed in the final view.,
• joinOnly: true|false, false by default, indicating whether the column is brought in purely to facility automated join resolution, i.e., the column is not exposed in the final view.
• description: a meaningful description of the column, which could contain plain HTML, and which will be output in the automatically generated documentation (JavaDocs as well as HTML SchemaDocs).

🎈 NOTE: Column types are inherited from the baseline columns referenced.

You can also define short expressions which is useful to cast results into different types for example, or simply transform some value. The attributes are:

• expression: "?::DATE" where "?" will be substituted for the column referenced. Any SQL fragment resulting in a value is allowed and can therefore make use of database-specific functionality.
• type: A type for the resulting value which may be different from the original column's type. See the Tilda Type System.

🎈 NOTE: Since you can use any valid code in the expression, Tilda can't validate that the type definition is correct. If incorrect, you will get an error when migrating your schema and the view is created.

Additionally, you can define aggregate attributes:

• aggregate: SUM|AVG|MIN|FIRST|MAX|LAST|DEV|VAR|COUNT|ARRAY. Some aggregates may not be supported by some databases by default and may need to be created. For example, FIRST and LAST are not supported out of the box in Postgres and are defined in the supporting Tilda SQL functions. ARRAY for example is not supported in SQLServer and may instead return a comma-separated concatenation of the data retrieved.
• orderBy: an optional array of 1 or more columns to order the results in the aggregate if applicable, e.g., first, last, array.
• distinct: true|false, false by default, indicating whether the results in the aggregate should be unique if applicable, e.g., first, last, array.
• filter: a boolean expression using SQL syntax that can be used to filter the results for an aggregate, for example, ""colA" > 10". The expression provided is used as is and can therefore make use of database-specific functionality. The filter feature is in and of itself not supported in all databases: it is in Postgres but not in SQLServer for example. Note that column names must be escaped properly.

🎈 NOTE: Since you can use any valid code in the filter, Tilda can't validate it. If incorrect, you will get an error when migrating your schema and the view is created. :balloon: NOTE: If you define an expression, it takes precedence, and any aggregate will operate over the final value. :balloon: NOTE: Coalesce happens at the final level and will consume any value returned by an expression or an aggregate (or combination).