QueryAPI - What is Query API?

Owned by Michal Smíšek
Last updated: Dec 17, 2024
2 min read

What is Query API?

Available since 14.0 Caribou Lou release

Query API is a Groovy API available in all logics since 14.0 release to query the data from Pricefx. It was introduced to provide a unified access to data in Pricefx and long term wise it should be the only API for querying data.

The main driver is to support JOIN of data from two or more tables to utilize the performance of the database engine since joining data using various techniques in groovy logic in the memory of the backend was not that efficient. It can also save the number of queries made to the database.

Main features:

  • Supports JOIN of tables

  • Provide a single unified interface for querying data for both transactional as well as analytical database

  • Works natively with field names (analytical tables) or field labels (master and transactional tables)

  • Data is provided in a stream (supported in the input generation mode)

  • Aggregation of the data

  • Advanced expressions

  • Allows to use column aliases to retrieve column under different name

Use of joins can be a significant performance improvement. But at the same time it can also cause a huge harm if not used properly. Therefore a knowledge of how databases work is required.

Note: Joining data between analytical database and transactional database (other modules) is not possible (e.g. a product extensions with a data source). You can join tables only within the same database.

Simple Query

The main entrance point for use of QueryAPI is a method api.queryApi() that returns a QueryAPI interface. This interface provides access to a set of methods which you will need for querying.

Let’s look at this query which reads a single row with sku = “MB-0001” from the Product master table:

def qapi = api.queryApi() def p = qapi.tables().products() return qapi.source(p, [p.sku(), p.label], p.sku().equal("MB-0001")) .stream { it.collect { it } }

Line 1: the QueryAPI reference is stored in qapi variable for later use.

Line 3: the reference to product master table is stored in p variable.

Line 5: the source() method says to query products table with a filter expression that defines which rows to return in the result

Line 6: QueryAPI provides the result always as a stream (ResultStream interface). So it is up to you how you will consume each individual row. In our example, all rows are collected to a List.

Let’s look on each part in the next sections.

 

 

 

 

Found an issue in documentation? Write to us.