Ontop

# Lenses

Since 4.2.0 (was experimental in 4.1.x)

Prior to 5.0.0, lenses were named Ontop views.

Lenses are relational views defined at the level of Ontop and unknown to the underlying database. Lenses can be defined from database relations and from other lenses.

As database relations, lenses have a name which can be used in the source part of the mapping entries. They are specified in a separate file that can be provided to Ontop through a dedicated parameter (--lenses for the CLI commands that support it, ONTOP_LENSES_FILE for the Docker image (opens new window)).

At the moment, 3 types of lenses are available:

  1. Basic lenses (defined over one base relation)
  2. Join lenses (defined over multiple base relations)
  3. SQL lenses (defined from an arbitrary SQL query).

Don't use lenses in complex source SQL queries

The Ontop mapping SQL parser only parses simple forms of SQL queries (without unions, aggregations, limits, order by, etc.). Non-parsed queries are treated as black-box views, that is as strings that are injected into the final SQL queries sent to the database. If some lenses appear in these black-box views, the resulting SQL queries will be rejected by the database because they refer to relations it does not know.

One interesting feature of lenses is that you can specify additional constraints holding on them (in addition to the ones that can be inferred from base relations). The constraints can be:

# Example

{
    "relations": [
        {
            "name": ["\"lenses\"","\"hr\"","\"persons\""],
            "baseRelation": ["\"hr\"","\"persons\""],
            "filterExpression": "\"firstName\" IS NOT NULL AND \"lastName\" IS NOT NULL", 
            "columns": {
                "added": [
                    {
                        "name": "\"fullName\"",
                        "expression": "CONCAT(UPPER(\"firstName\"),' ',\"lastName\")"
                    }
                ],
                "hidden": [
                    "\"firstName\"",
                    "\"lastName\""
                ]
            },
            "uniqueConstraints": {
                "added": [
                    {
                        "name": "uc2",
                        "determinants": "\"ssn\""
                    }
                ]
            },
            "otherFunctionalDependencies": {
                "added": [
                    {
                        "determinants": ["\"regionOfResidence\""],
                        "dependents": ["\"countryOfResidence\""]
                    }
                ] 
            },
            "foreignKeys": {
                "added": [
                    {
                        "name": "fk1",
                        "from": ["\"regionOfResidence\""],
                        "to": {
                            "relation": ["\"geo\"","\"regions\""],
                            "columns": ["\"reg_id\""]
                        }
                    }
                ]
            },
            "nonNullConstraints": {
                "added": [
                    "\"email\""
                ]
            },
            "iriSafeConstraints": {
                "added": [
                    "\"ssn\""
                ]
            },
            "type": "BasicLens"
        },
        {
        "name": [
            "\"lenses\"",
            "\"rooms\""
        ],
        "join": {
            "relations": [
            ["\"rooms\""],
            ["\"lenses\", \"hotels\""]
            ],
            "columnPrefixes": [
            "r_",
            "h_"
            ]
        },
        "filterExpression": "\"r_hotel_id\"=\"h_id\" AND (\"h_stars\" = '***' OR \"h_price\" = '€€€') AND \"r_guests\" = 2",
        "columns": {
            "added": [
            ],
            "hidden": [
            ]
        },
        "type": "JoinLens"
        },
        {
            "name": ["\"lenses\"","\"geo\"","\"top_region\""],
            "query": "SELECT \"regionOfResidence\" AS \"region\", COUNT(*) FROM \"hr\".\"persons\" GROUP BY \"regionOfResidence\" ORDER BY COUNT(*) DESC LIMIT 1",
            "type": "SQLLens"
        }
    ]
}

# Document root

The lenses document has the following JSON structure:

{ 
    "relations": [Lens]
}
Key Type
relations Array of Lens-s

# Lens

# Common fields

All the lenses accept the following fields (most of them are optional):

{
    "name": [String],
    "uniqueConstraints": {
        "added": [UniqueConstraint]
    },
    "otherFunctionalDependencies": {
        "added": [OtherFunctionalDependency]
    },
    "foreignKeys": {
        "added": [ForeignKey]
    },
    "nonNullConstraints": {
        "added": [String]
    },
    "iriSafeConstraints": {
        "added": [String]
    },
    "type": String
}
Key Type Description
name Array of Strings View name components (with correct quoting)
uniqueConstraints JSON Object Optional
uniqueConstraints.added Array of UniqueConstraint-s
otherFunctionalDependencies JSON Object Optional
otherFunctionalDependencies. added Array of OtherFunctionalDependency-s
foreignKeys JSON Object Optional
foreignKeys.added Array of ForeignKey-s
nonNullConstraints JSON Object Optional
nonNullConstraints.added Array of Strings Names of non-null columns (with correct quoting). One string per column
iriSafeConstraints JSON Object Optional
iriSafeConstraints.added Array of Strings Names of IRI-safe columns (with correct quoting). One string per column
type String Either BasicLens, JoinLens or SQLLens

# BasicLens

A basic lens is defined from one base (parent) relation, over which it can apply a filter, an extended projection and additional constraints.

In addition to the common fields, basic lenses accept the following ones:

{
    "baseRelation": [String],
    "columns": {
        "added": [AddedColumn],
        "hidden": [String]
    },
    "filterExpression": String,
    "type": "BasicLens"
}
Key Type Description
baseRelation Array of Strings Name components of the base relation (with correct quoting)
columns JSON Object
columns.added Array of AddedColumn-s
columns.hidden Array of Strings Names of the columns from the base relation to be projected away (with correct quoting)
filterExpression String Expression expressed in the SQL dialect of the data source. Can only refer to columns from the base relation, not to added columns. Can be empty

# AddedColumn

Added columns have the following definition:

{
    "name": String,
    "expression": String
}
Key Type Description
name String New column name (with correct quoting)
expression String SQL expression defining the column. Can only refer to columns from the base relations, not to added columns

# JoinLens

A join lens is defined from multiple base relations, over which it can apply a filter (joining condition), an extended projection and additional constraints.

A prefix is assigned to each base relation and is added as a prefix to their column names. This allows to avoid conflicts due to columns with the same names in base relations.

In addition to the common fields, join lenses accept the following ones:

{
    "join": {
        "relations": [[String]],
        "columnPrefixes": [String]
    },
    "columns": {
        "added": [AddedColumn],
        "hidden": [String]
    },
    "filterExpression": String,
    "type": "JoinLens"
}
Key Type Description
join JSON Object
join.relations Array of arrays of Strings Arrays of the name components of each base relation (with correct quoting)
join.columnPrefixes Array of Strings Prefix for each base relation to be applied on its column names. Follows the same order as join.relations.
columns JSON Object
columns.added Array of AddedColumn-s
columns.hidden Array of Strings Names of the columns from the base relations to be projected away (with correct quoting)
filterExpression String Expression expressed in the SQL dialect of the data source. Can only refer to prefixed columns from the base relations, not to added columns. Can be empty

# SQLLens

A SQL lens is defined from an arbitrary SQL query. While expressive, it also comes with important restrictions. When applicable, other types of lenses should be used instead.

Avoid referring to lenses in the SQL query

As Ontop uses the same parser as from the mapping source queries, the same restriction apply: non-parsed queries will be treated internally as black-box views and will fail. Please consider using other types of lenses if possible.

No unique constraint and foreign key inferred from the base relations

Please consider using other types of lenses if possible.

In addition to the common fields, SQL lenses accept the following ones:

{
    "query": String,
    "type": "SQLLens"
}
Key Type Description
query String SQL query

# Constraints

# UniqueConstraint

{
    "name": String,
    "determinants": [String]
}
Key Type Description
name String Name of the unique constraint
determinants Array of Strings Column names (with correct quoting)

# OtherFunctionalDependency

Useful for dealing with denormalized data, where unique constraints cannot be applied.

{
    "determinants": [String],
    "dependents": [String]
}
Key Type Description
determinants Array of Strings Column names (with correct quoting) that determine the values of dependent columns
dependents Array of Strings Column names (with correct quoting) whose values are determined by determinant columns

# ForeignKey

{
    "name": String,
    "from": [String],
    "to": {
        "relation": [String],
        "columns": [String]
    }
}
Key Type Description
name String Name of the foreign key
from Array of Strings Source columns (with correct quoting)
to JSON Object
to.relation Array of Strings Name components of the target relation (with correct quoting)
to.columns Array of Strings Target columns (with correct quoting). Same order as for the source columns

← FAQ →