...

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 ilong long term wise it should be the only API for querying data.

...

• Supports JOIN of tables

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

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

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

• Aggregation of the data

• Advanced expressions

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

...

The Table interface extends Map<String, SelectableExpression> interface. Thefore Therefore the table objects are used to reference columns.

...

Analytical tables are currently not supported, the plan is to suport support these in 15.0 release:

• Data sources

• Datamarts

• Rollups

• Model Class Tables

...

Attribute fields can be used in the QueryAPI only if you configure a name and data type for that attribute field. Attribute fields are accessed as a Map property by the configured namelabel, e.g. px.Cost, cp.Country. Therefore it is not recommended to configure attribute labels with spaces, since it makes the code worse readable, however such column names are supported. Attribute fields cannot be accessed by their system field name, e.g. p.attribute3 will not work.

System fields can be accessed as Map properties as well, but also using methods in the given table interface, e.g. p.sku(), c.customerId(), p.lastUpdateDate(). It si is recommended to use the provided methods, since it will help you when finding column references in the source code.

...

The first parameter is a mandatory one and accepts a reference to a table, the other paremeters parameters are optional. The second parameter specifies the list of columns to be returned. The third parameter is an expression that defines filter on the returned rows. So it is like a “WHERE” clause in SQL. Since existing Filter class was not sufficient for the purpose of QueryAPI, a new interface Expression has been introduced.

...

Returns sku and Brand fields from the table t1 and filters the rows to return row with sku = “MB-0001”.

Stream

QueryAPI provides the result always as a stream (ResultStream interface). So it is up to you how you will consume each individual row. The processing of the row should be fast, because it is holding a database connection. On the other hand collecting big amount of records to memory using .collect { it } may lead to OutOfMemory exception. So this needs to be wisely

Expressions

There is quite many methods that are available, kindly refer to the Javadoc.

...