Introduction

Your plugin is expected to expose two to four HTTP endpoints (2 optional authorization-related endpoints, and 2 mandatory data-related endpoints). These endpoints will be called by Luzmo on different points in time:

1. When creating a Connection to your plugin in Luzmo, the provided credentials will be sent to your plugin's /authorize endpoint . This allows your plugin to authorize whether the user can connect to it , and your plugin will be able to use these credentials to retrieve dataset(s) information and query data.

   • In case your plugin is configured to use OAuth2, the /exchange endpoint will be called to exchange an OAuth2 code with an OAuth2 Access and Refresh token.

2. When adding new datasets, or retrieving the schema of an existing dataset, your plugin's /datasets endpoint will be called to retrieve the available datasets and their schema(s) . The Connection's credentials or OAuth2 tokens will be passed along on each request.

3. When querying data from a Plugin dataset via Luzmo, your plugin's /query endpoint will be called to retrieve the available data . The Connection's credentials or OAuth2 tokens will be passed along on each request. Depending on whether your plugin is a Basic or Pushdown-enabled plugin , the request and response are different to ensure an optimal query performance:

POST /authorize

This endpoint is called whenever a user tries to create a new Connection for your Plugin (either via API, or via our UI). You can verify and authenticate the connection before creation , by using the credentials specified by the user. By returning an error code, the user can be alerted timely (during the connection setup vs. receiving an error later when retrieving datasets).

Authorize request parameters

Content-Type: application/json
X-Secret: m2c0vilcn9q1qkeph06oycynp
X-Property-Key: my_key
X-Property-Token: my_token
X-Property-Host: https://my.personal.example

{
  "id": "098b9355-d043-46d5-be27-a511a04e46b7",
  "name": "Bill Murray",
  "email": "bill.murray@example.com"
}

The following headers are sent:

The following payload is sent with Content-Type application/json :

Authorize response format

The following status codes are handled:

• 200 OK : the Account is created successfully with the credentials as sent to your Plugin. The user is then led to the dataset listing for your Plugin.

• 404 Not Found : your Plugin does not wish to handle the /authorize request and the Account is created successfully. The user is then led to the dataset listing for your Plugin.

• 501 Not Implemented : like 404

• Any other code : the credentials are presumed to be wrong and the Account is not created. An error is shown to the user, who can retry to connect (eg. with different key/token, ...)

In case of an OAuth2 authorization, a payload of Content-Type application/json should have the following property:

{
  "auth_url": "https://www.facebook.com/v3.0/dialog/oauth
    ?client_id=< your Facebook app id >
    &redirect_uri=https://app.luzmo.com/start/connect/< your plugin slug >"
}

Example error response from the plugin when an error happens upon receiving a /authorize request. The message will be shown to the end-user.

{
    "type": {
        "code": 400,
        "description": "Bad Request"
    },
    "message": "<message_displayed_upon_connection_creation_failure>"
}

POST /exchange

This endpoint is only used for OAuth2 Plugin Authentication , and is called after the user has confirmed connecting your app via OAuth2. Luzmo will send the code value received from the OAuth2 authentication. You can now exchange this code within your plugin for an OAuth2 Access and Refresh token , and send it back to Luzmo to be stored for authenticating future requests to your plugin for this Connection.

Exchange request parameters

Content-Type: application/json
X-Secret: m2c0vilcn9q1qkeph06oycynp

{
  "id": "098b9355-d043-46d5-be27-a511a04e46b7",
  "name": "Bill Murray",
  "email": "bill.murray@example.com",
  "code": "OAUTH2_CODE"
}

The following headers are sent:

The following payload is sent with Content-Type application/json :

Exchange response format

{
  "access_token": "OAUTH2_ACCESS_TOKEN",
  "refresh_token": "OAUTH2_REFRESH_TOKEN"
}

The payload of Content-Type application/json should have the following properties:

POST /datasets

This endpoint is called to retrieve a list of datasets, and their columns , that this Plugin makes available for the Plugin Connection.

Datasets request parameters

Content-Type: application/json
X-Secret: m2c0vilcn9q1qkeph06oycynp
X-Property-Key: my_key
X-Property-Token: my_token
X-Property-Host: https://my.personal.example
X-Request-Id: luzmo-request-identifier

{
  "search": "< Optional searchphrase >",
  "ids": ["canonical_identifier_1", "canonical_identifier_2"],
  "user": {
    "id": "098b9355-d043-46d5-be27-a511a04e46b7",
    "name": "Bill Murray",
    "email": "bill.murray@example.com",
    "authorization_id": "d18b1f35-c53b-439b-b1aa-fd6db4d04c51",
    "metadata": {
      "client_id": 5
    }
  }
}

The following payload is sent with Content-Type application/json :

Datasets response format

The response should be a payload with Content-Type application/json , with an array of dataset objects:

[
  {
    "id": "canonical_dataset_id",
    "name": {
      "en": "Dataset name",
      "fr": "Nom du dataset"
    },
    "description": {
      "en": "A long-form description of this dataset.",
      "fr": "Une description longue de ce dataset."
    },
    "columns": [
      {
        "id": "Column id",
        "name": {
          "en": "Column name",
          "fr": "Nom de la colonne"
        },
        "description": {
          "en":"A long-form description of this column.",
          "fr": "Une description détaillée de cette colonne."
        },
        "type": "hierarchy"
      },
      ...
    ]
  },
  ...
]

POST /datasets
{ "search": "search_key" }

// Note that in the response, "columns" property is omitted
[
  { "id": "table1", "name": { "en": "My search_key dataset 1" } },
  { "id": "table2", "name": { "en": "My search_key dataset 2" } },
  ...
]

POST /datasets
{ "ids": [ "table1" ] }

// "columns" property must be included in response
[
  {
      "id": "table1",
      "name": { "en": "Table 1" },
      "columns": [
        {
          "id": "column1",
          "name": { "en": "Column 1" },
          "type": "hierarchy"
        },
        ...
      ]
  }
]

Example error response from the plugin when an internal error happens upon receiving a /datasets request. The message will be shown to the end-user.

{
    "type": {
        "code": 500,
        "description": "Internal Server Error"
    },
    "message": "<message_displayed_upon_datasets_retrieval_failure>"
}

POST /query

Your Plugin's /query endpoint is called to request (a slice of) data from a dataset exposed by your Plugin. Depending on your plugin implementation and the underlying data source, Luzmo requests row-level or aggregated data via this endpoint.

Below you can find more information about the two types of plugin implementations supported today ( Basic and Pushdown-enabled plugins ), and which one to use when. After that, the Plugin Query endpoint's specifications are provided for each plugin type. Last but not least, there is a set of optional plugin specifications which your pushdown-enabled plugin on spec v3 can adhere to, which could e.g. further enhance overall query performance with additional pushdown capabilities.

Basic vs Pushdown-Enabled Plugins

When creating your plugin in Luzmo, you can choose between two types of plugins: Basic and Pushdown-Enabled. Each serves different use cases based on the nature of your data and the capabilities of your data source.

• Basic Plugin

  • Retrieves transactional ( non-aggregated ) data.

  • Always retrieves all columns and row-level data.

  • Aggregations and filters are always applied by Luzmo after data retrieval, but filters can be optionally applied.

  ⇒ Use a Basic plugin to quickly connect a new data source , query relatively small datasets, or when your data source does not support aggregations (e.g. a time-series database).

  
  

• Pushdown-Enabled Plugin

  • Retrieves aggregated data.

  • Must apply aggregations and filters.

  ⇒ Use a Pushdown-Enabled Plugin to connect to a data source that supports high-performance aggregation and filtering (e.g. a multi-dimensional data store, OLAP cube, etc.). You can switch a Plugin to this mode by setting the pushdown field to true (see Registering a plugin ).

Basic plugin Query endpoint

When a Basic plugin's Query endpoint receives a request, row-level data for all columns should be returned , in the same order as defined in the /datasets metadata for this set. Besides that, a set of filters is also sent along with the /query request payload, which your plugin can optionally apply . Applying these filters can reduce the number of rows to be sent, and thus positively impact the query performance!

In any case, Luzmo will always verify and apply all filters for Basic Plugin query results (including eg. authorization filters that ensure your customers cannot see particular data), as some filters might only be applicable after linking multiple sets which can potentially be sourced from different data sources or Plugins.

Basic query request parameters

Content-Type: application/json
X-Secret: m2c0vilcn9q1qkeph06oycynp
X-Property-Key: my_key
X-Property-Token: my_token
X-Property-Host: https://my.personal.example
X-Request-Id: luzmo-request-identifier

{
  "id": "burrito_stats",
  "filters": [
    {
      "column_id": "date",
      "expression": ">",
      "value": "2018-01-01T00:00:00.000Z"
    }
  ],
  "user": {
    "id": "098b9355-d043-46d5-be27-a511a04e46b7",
    "name": "Bill Murray",
    "email": "bill.murray@example.com",
    "authorization_id": "d18b1f35-c53b-439b-b1aa-fd6db4d04c51",
    "metadata": {
      "client_id": 5
    }
  }
}

The following headers are sent:

The following payload is sent with Content-Type application/json :

Basic query response format

The return should be a payload of Content-Type application/json with an array of arrays (list of rows, with each row a list of columns). Columns should all be sent (i.e. for each column, there should be at least one value in the "row" list), and be in the same order as defined in the /datasets call .

Date/time data should be sent in RFC 3339 format, like 2018-05-05T12:23:45.000Z .

Example plugin response to return 2 rows of data, for a dataset with a text column, a numeric column, and a datetime column. These will be further processed by Luzmo to visualize the desired insights!

[
  ["Text value 1", 1, "2018-01-01T12:34:56.789Z"],
  ["Text value 2", 1, "2019-01-01T12:34:56.789Z"]
]

Example error response from the plugin when an internal error happens upon receiving a /query request. The message will be shown to the end-user when e.g. hovering over the "Query failed" error in a dashboard widget.

{
    "type": {
        "code": 500,
        "description": "Internal Server Error"
    },
    "message": "<message_displayed_upon_query_failed_hover>"
}

Basic query example

Given the following dataset, an example /query request and response for a basic plugin can be found below.

Example of the row data inside the 'burrito_stats' table, a dataset exposed by a basic plugin:

Example /query request sent to a basic plugin

{
  "id": "burrito_stats",
  "filters": [
    {
      "column_id": "date_savoured",
      "expression": ">",
      "value": "2018-01-01T00:00:00.000Z"
    }
  ],
  "user": {
    "id": "098b9355-d043-46d5-be27-a511a04e46b7",
    "name": "Bill Murray",
    "email": "bill.murray@example.com",
    "authorization_id": "d18b1f35-c53b-439b-b1aa-fd6db4d04c51",
    "metadata": {
      "client_id": 5
    }
  }
}

Row-level data is being sent as response, optionally filtered (in this example, the filter is not applied to the response, thus all 6 rows are returned in the response). Luzmo will use this response to further filter and aggregate the data based on the visualization query.

[
  ["salty", "2018-06-10T00:00:00.000Z", 173],
  ["spicy", "2018-06-12T00:00:00.000Z", 187],
  ["sweet", "2018-06-13T00:00:00.000Z", 301],
  ["salty", "2018-06-10T00:00:00.000Z", 301],
  ["spicy", "2018-06-11T00:00:00.000Z", 255],
  ["sweet", "2017-10-28T00:00:00.000Z", 190]
]

Pushdown-enabled plugin Query endpoint

When a Pushdown-enabled plugin receives a request on its Query endpoint, the request will contain the specific data points that the plugin is expected to return. This includes any filters that must be applied on group-by data points and aggregated data values, or even returning row-level data for the requested column(s).

Pushdown query request parameters

Content-Type: application/json
X-Secret: m2c0vilcn9q1qkeph06oycynp
X-Property-Key: my_key
X-Property-Token: my_token
X-Property-Host: https://my.personal.example
X-Request-Id: luzmo-request-identifier

{
  "id": "burrito_stats",
  "columns": [
    {
      "column_id": "type_of_burrito"
    },
    {
      "column_id": "date_savoured",
      "level": "year"
    },
    {
      "column_id": "weight",
      "aggregation": "sum"
    }
  ],
  "filters": [
    {
      "column_id": "date",
      "expression": ">",
      "value": ["2018-01-01T00:00:00.000Z"]
    }
  ],
  "user": {
    "id": "098b9355-d043-46d5-be27-a511a04e46b7",
    "name": "Bill Murray",
    "email": "bill.murray@example.com"
  },
  "options": {
    "pushdown": true,
    "timezone_id": "Europe/Brussels"
  }
}

The following headers are sent:

The following payload is sent with Content-Type application/json . Differences with Basic Plugin are highlighted in blue in the table below.

Pushdown query response format

The return should be a payload of Content-Type application/json with an array of arrays (list of aggregated rows, with each row a list of the requested columns). Data should be filtered and aggregated.

Date/time data should be sent in RFC 3339 format, like 2018-05-05T12:23:45.000Z .

Example plugin response to return 2 rows of aggregated data for three columns queried (a text column, an aggregated numeric column, and a datetime column).

[
  ["Text value 1", 50, "2018-01-01T12:34:56.789Z"],
  ["Text value 2", 200, "2019-01-01T12:34:56.789Z"]
]

Example error response from the plugin when an internal error happens upon receiving a /query request. The message will be shown to the end-user when e.g. hovering over the "Query failed" error in a dashboard widget.

{
    "type": {
        "code": 500,
        "description": "Internal Server Error"
    },
    "message": "<message_displayed_upon_query_failed_hover>"
}

Pseudo-code query algorithm

The following pseudo-code algorithm shows what an equivalent SQL query for a /query call would look like:

select_columns = []
groupby_columns = []

# Loop through each column in the request
for column in request_body.columns:
    # Return raw column data if options.pushdown is false in the request
    if not options.pushdown:
        select_columns.append(column.id)
    # Apply aggregation if specified
    elif column.aggregation:
        if column.id == "*" and column.aggregation == "count":
            select_columns.append("COUNT(*)")
        else:
            select_columns.append(f"{column.aggregation}({column.id})")
    # Apply date-based grouping (with timezone handling if necessary)
    elif column.level:
        # _is_column_with_timezone is a custom method (checks if timezone shift can be applied on column)
        if options.timezone_id and _is_column_with_timezone(column.id):
            select_columns.append(f"DATE_TRUNC('{column.level}', {column.id} AT TIME ZONE '{options.timezone_id}')")
            groupby_columns.append(f"DATE_TRUNC('{column.level}', {column.id} AT TIME ZONE '{options.timezone_id}')")
        else:
            select_columns.append(f"DATE_TRUNC('{column.level}', {column.id})")
            groupby_columns.append(f"DATE_TRUNC('{column.level}', {column.id})")
    # Default: Group by column values
    else:
        select_columns.append(column.id)
        groupby_columns.append(column.id)

# Construct SQL query to request data from plugin's underlying data source
sql = f"SELECT {', '.join(select_columns)} FROM {request_body.id}"
if len(groupby_columns) > 0:
    sql += f" GROUP BY {', '.join(groupby_columns)}"

Pushdown query example

Given the following dataset, an example /query request and response for a pushdown-enabled plugin can be found below.

Example of the row data inside the 'burrito_stats' table, a dataset exposed by a pushdown-enabled plugin:

Example /query request sent to a pushdown-enabled plugin

{
  "id": "burrito_stats",
  "columns": [
    {
      "column_id": "type_of_burrito"
    },
    {
      "column_id": "date_savoured",
      "level": "year"
    },
    {
      "column_id": "weight",
      "aggregation": "sum"
    }
  ],
  "filters": [
    {
      "column_id": "date_savoured",
      "expression": ">",
      "value": ["2018-01-01T00:00:00.000Z"]
    }
  ],
  "user": {
    "id": "098b9355-d043-46d5-be27-a511a04e46b7",
    "name": "Bill Murray",
    "email": "bill.murray@example.com"
  },
  "options": {
    "pushdown": true,
    "timezone_id": "Europe/Brussels"
  }
}

SQL equivalent query of the example above (this is what your plugin could use to retrieve the requested data from e.g. a SQL data source):

SELECT
    type_of_burrito AS c1,
    DATE_TRUNC('year', date_savoured AT TIME ZONE 'Europe/Brussels') AS c2,
    SUM(weight) AS c3
FROM burrito_stats
WHERE
    date_savoured > TIMESTAMP '2018-01-01T00:00:00.000Z'
GROUP BY
    type_of_burrito,
    DATE_TRUNC('year', date_savoured AT TIME ZONE 'Europe/Brussels');

Aggregated and filtered data is being sent as response.

[
  ["salty", "2018-01-01T00:00:00.000Z", 474],
  ["spicy", "2018-01-01T00:00:00.000Z", 472],
  ["sweet", "2018-01-01T00:00:00.000Z", 187]
]

Optional pushdown-enabled plugin specifications

There's a set of optional plugin specifications you can opt in to implement for your Pushdown-enabled plugin , which could further enhance query performance or even add extra functionality :

• "Distinct count" pushdown

• "Order & limit" pushdown

• "Join" pushdown

• "Like filter" pushdown

• "Nested filters" pushdown

• "SQL datasets" support

• "Array unnesting" pushdown

Each of these plugin specifications are dependent on your plugin being marked as adhering to spec v3 , as well as setting the supported supports_XXX flag(s) to true . You can create or update a plugin to spec v3 by setting the property "protocol_version": "3.0.0" through the API . This version is currently not the default version, nor can you already select it from the UI.

Distinct count pushdown

By setting the plugin property supports_distinctcount = true , Luzmo will now push through distinct count aggregations to your plugins.

When enabled, your plugin should support the following aggregation types: sum , count , distinctcount , min , and max .

POST /query request
{
  "id": "table1",
  ...
  "columns": [
     { "id": "column1", "aggregation": "distinctcount" }
  ]
}

Order & limit pushdown

By setting the plugin property supports_order_limit = true , Luzmo will now push through ORDER & LIMITs to your plugin, if applicable. Orders can occur on aggregated values (e.g. sorting descending on sum of sales), or on columns selected (e.g. sorting ascending on dates, sorting text columns alphabetically, etc.).

When enabled, both ordering and limiting should be implemented to avoid undefined plugin behaviour (e.g. limiting data without having a defined order, typically leads to indeterministic results).

POST /query request
{
  "id": "table1",
  ...
  "columns": [
    { "column_id": "column2", "level": "month" },
    { "column_id": "column3" },
    { "column_id": "column1", "aggregation": "sum" },
    { "column_id": "column4" }
  ],
  "order": [
    { "column_id": "column1", "aggregation": "sum", "order": "desc" },
    { "column_id": "column2", "level": "month", "order": "asc" },
    { "column_id": "column3", "order": "asc" }
  ],
  "limit": {
    "by": 20,
    "offset": 1000
  }
}

This can be translated to SQL as:

SELECT
  "table"."column2" AS c1,
  DATE_TRUNC('month', "table"."column3") AS c2,
  SUM("table"."column1") AS c3,
  "table"."column4" AS c2
FROM
  "table"
ORDER BY
  SUM("table"."column1") DESC
  DATE_TRUNC('month', "table"."column2") ASC NULLS LAST
  "table"."column3" ASC NULLS LAST
LIMIT BY 20 OFFSET 1000

Join pushdown

By setting the plugin property supports_join = true , Luzmo will push through joins to your plugin (for linked datasets of the same Plugin and Connection), if applicable.

The join keys of the join are user-defined in Luzmo (via our Linking UI , or via API ), and are sent in a new join property. A new dataset_id property is specified for each requested column, and identifies the dataset it belongs to (as column identifiers might not be unique across different datasets).

POST /query request
{
  "id": "orderline",
  ...
  "columns": [
    { "dataset_id": "order", "id": "customer" },
    { "dataset_id": "orderline", "id": "amount", "aggregation": "sum" }
  ],
  "join": [
    {
      "type": "lookup",
      "criteria": [
        // This particular lookup join has 2 join criteria:
        // orderline.order_id = order.id AND orderline.tenant = order.tenant
        [
          {
            "dataset_id": "orderline",
            "column_id": "order_id"
          },
          {
            "dataset_id": "order",
            "column_id": "id"
          }
        ],
        [
          {
            "dataset_id": "orderline",
            "column_id": "tenant"
          },
          {
            "dataset_id": "order",
            "column_id": "tenant"
          }
        ],
      ]
    }
  ]
]}

// Expected response with data from both datasets
[
  ['Client A', 500000],
  ['Client B', 450000],
  ...
]

Two different join types currently exist:

• lookup

  This is a join that retrieves, for every row of A, exactly zero or one rows from B that match the row A. These are typically queries on a "many" dataset, which also query or filter on columns from a "one" dataset. This does not directly correspond to a SQL join type, but can be implemented like:

  sql

  SELECT
    ...
  FROM
    A
  LEFT JOIN (
    SELECT DISTINCT ON (B.join_key_1[, ...]) * FROM B
  ) B ON A.join_key_1 = B.join_key_1 [AND ...]

• exists This is a join that retrieves only rows from A for which at least one matching row from B is found. These are typically queries on a "one" dataset, which are filtered by one or more columns from a "many" dataset. This can be implemented in SQL like:

  sql

  SELECT
    ...
  FROM
    A
  WHERE
    EXISTS (SELECT * FROM B WHERE A.join_key_1 = B.join_key_1 [AND...])

Like filter pushdown

By setting the new plugin property supports_like = true , Luzmo will push through "Like" filters (e.g. Luzmo filter expressions such as "contains", "(does not) start with", "(does not) end with", etc.) to your plugin, if applicable. The filter expression is set to either ilike or not ilike , the value will use % as a wildcard character. Filters should always be applied case-insensitive , and use a UTF-8 collation.

POST /query request
{
  "id": "table1",
  ...
  "filters": [
    {
      "column_id": "...",
      "expression": "ilike",
      "value": ["%searchphrase%"]
    }
  ]
}

Nested filters pushdown

By setting the plugin property supports_nested_filters = true , Luzmo will push through composite (nested) filters to your plugin, if applicable. At the moment the supported pushed down composite filters are AND and OR . This allows Luzmo to further optimize dashboard queries that define OR -filters.

• Before, filters was a list of filters that would be AND'ed together, now filters is a single object containing AND and OR expressions where appropriate. This is also the case for filters that don’t contain OR -expressions. The filter syntax itself is still identical.

• Before, when no filters were specified an empty array ( filters:[] ) was sent, now the filters property will not be present in the query.

• filters can be nested arbitrarily deeply .

POST /query request
{
  "id": "table1",
  ...
  "filters": {
    "and": [
      {
        "column_id": "...",
        "expression": ">",
        "value": [123]
      },
      {
        "or": [
          {
            "column_id": "...",
            "expression": "in",
            "value": ["value_1", "..."]
          },
          {
            "column_id": "...",
            "expression": "is not null"
          }
        ]
      }
    ]
  }
}

Plugin SQL datasets

By setting the plugin property supports_sql = true , you can indicate that your plugin can handle custom SQL as defined by the user in Luzmo’s SQL Datasets feature. Note that this requires support in your plugin's /datasets and /query endpoints, as described below.

When creating a new SQL dataset (via UI or via the Dataprovider API Service), the plugin's /datasets endpoint will first be called to determine the schema (columns and column types) of the SQL dataset, similar to adding a regular plugin dataset:

POST /datasets request
{
  "user": ...,
  "ids": [
    {
      "dataset_id": "custom_sql_dataset_identifier",
      "sql": "SELECT my_field FROM my_table"
    }
  ]
}

// You can typically run a similar query as follows to get the schema of a SQL dataset:
// SELECT * FROM ( sql_definition ) t LIMIT 0

[
  {
    /* The "id" should be equal to the "dataset_id" in the request */
    "id": "custom_sql_dataset_identifier",
    "name": { "en": "SQL dataset" },
    "description": { "en": "A SQL dataset" },
    "columns": [
      {
        "id": "my_field",
        "name": { "en": "My field" },
        "type": "hierarchy"
      }
    ]
  }
]

Requests to your /query endpoint will contain a new property sql for SQL dataset queries. This property contains a list of the SQL dataset(s) referenced in the query, together with their SQL definition:

POST /query request
{
  "id": "custom_sql_dataset_identifier",
  "columns": [
    { "dataset_id": "custom_sql_dataset_identifier", "id": "my_field" }
  ],
  "sql": [
    {
      "dataset_id": "custom_sql_dataset_identifier",
      "sql": "SELECT my_field FROM my_table"
    }
  ],
  ...
}

// Expected response with data from the SQL dataset
[
    ["My field value"]
]

Generally, these can be implemented in SQL by wrapping the SQL dataset as a subquery, like:

SELECT
  "custom_sql_dataset_identifier"."my_field"
FROM
  (
    SELECT my_field FROM my_table
  ) "custom_sql_dataset_identifier"
GROUP BY
  "custom_sql_dataset_identifier"."my_field"
;

Array Unnesting Pushdown

By setting the plugin property supports_array_unnest = true , Luzmo will allow plugins to expose and query array columns. For grouped or aggregated queries, array columns should be unnested before further processing - this should be handled by the plugin. When a query is requesting multiple columns to be unnested, the plugin should unnest them by forming the Cartesian product of the requested unnested array columns (e.g. a column with values ['a', 'b'] and a column with values [1, 2] should result in [['a', 1], ['a', 2], ['b', 1], ['b', 2]] ).

To correctly handle filters on array columns, the plugin should apply different logic based on whether the referenced column is unnested in columns or not:

• If the column filtered on is requested in columns to be unnested, the plugin should apply the filter on the unnested values (e.g. array_column = 'a' would result in only returning 'a' values from the array).

• Otherwise, the plugin should apply the filter on the complete array as follows:

  1. For each column, group its filters combined with AND ,

  2. Apply the grouped filters on each individual array values - if the filter is true for at least one of the values , the filter should evaluate to true . If all filters in a column's grouped filters evaluate to true (as they should be AND'ed), the full array should be returned as-is.

  3. not in and != filters should be applied in a way where the result set is basically the inverse what the result set would be if an in / = filter with the same arguments were applied. E.g. if a filter array_column IN ('a', 'b') would return results [['test-1', ['a', 'b', 'c']], ['test-2', ['d', 'a']]] , a filter array_column NOT IN ('a', 'b') should return e.g. results [['test-3', ['c', 'd']]] .

The unnest property can appear in the following sections for column references:

• columns

• filters

• order

POST /query request
{
  "id": "table1",
  "columns": [
    { "column_id": "column1", "unnest": true },
    { "column_id": "column2": "unnest": true, "aggregation": "sum" }
  ],
  "filters": {
    "and": {
      {
        "column_id": "column3",
        "expression": "? = ?",
        "value": 5
      }
    }
  },
  "order": [
    { "column_id": "column1", "unnest": true, "order": "asc" }
  ]
}

// Expected response
[
  ["foo", 15],
  ["qux", 15]
]

When an array column is requested without unnest: true , it should be returned in a JSON array:

POST /query request
{
  "id": "table1",
  "columns": [
    { "column_id": "column1" },
    { "column_id": "column2" }
  ]
}

// Expected response
[
  [["foo", "qux"], [5, 10]]
]