Skip to content

Tilda JSON Syntax: View Columns

Jump to bottom
Laurent Hasson edited this page Aug 28, 2019 · 24 revisions

<-- 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"
 ,"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"]
 ,"coalesce":'1111-11-11'
 ,"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 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.

    • Tilda uses isomorphic names across all the programming models it works with (i.e., SQL, JSON, JavaScript, Java). As such the most common variable naming convention (starting with a lower case and use camel-case) is preferred. This often clashes with DB-oriented teams who prefer all lower-case identifiers with underscores. Naming conventions are hard! If your project is mainly application-driven, then a Java/JavaScript naming convention would be preferred, but if you use Tilda mostly for data warehousing or database-centric work, then the more common naming convention in those environment would obviously be preferred. In general, it's a god thing to define a naming convention at the schema-level at the very least and stick to it.
    • Column names are case-sensitive across most programming environments. However, within a single table, duplicates are checked in a case-insensitive way (for example, “helloWorld“and “helloworld” are treated as equivalent when checking for column definition duplicates. Some databases use case-insensitive names by default.
    • Column names must abide to identifier syntax rules, i.e., they must start with a letter or an underscore, and must only contain letters, digits or underscores (no spaces, no dash or other such characters).

  • type: A type for the column. Tilda uses a generic type system. See the Tilda Type System.

  • 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).

Clone this wiki locally