Querying tables
How to query tables in shared memory with nq.
Using the nq SDK is a simple way to extract information from shared memory.
There are a few in-built functions available to query the tables in memory and retrieve the desired data. These functions can be either used from within an nq script or from the command-line interface.
get
The get function returns the latest record for the table that matches the Id parameter provided.
Syntax: Table.get(id)
mg_11000> Market.get(1)
Market {
Id: 1,
Status: 63,
ShortName: 'EQUITIES',
Name: 'Equities',
FeeBasis: 0,
FeeBuy: 0,
FeeSell: 0,
MinFeePerOrderBuy: 0,
MinFeePerOrderSell: 0,
Venue: 11,
Scenario: 0,
Session: 201,
OrderType: 134230017,
...
CreateTimestamp: [ 671594592, 176725352 ],
UpdateUser: -1,
UpdateTimestamp: [ 671594592, 176725352 ]
}
As you can see in the REPL example above, the Id parameter was just a number. However within an nq script, Typescript requires the type to be Id<Table>, where Table refers to the enum of table numbers.
Fields that are foreign keys to other tables will already be of type Id<Table.ForeignTable>, however if you are using an integer you will need to wrap it in an Id() function like so:
//function Id() returns Id<Table> type
const userA = User.get(Id(3010101));
//Check userA is defined
if (userA) {
//userA.Firm is already type Id<Table.Firm>
const firmA = Firm.get(userA.Firm);
}
at
The at function returns the record of the table at the Index given.
Syntax: Table.at(index)
mg_11000> Firm.at(4)
Firm {
Id: 30101,
Status: 1023,
ShortName: 'FirmA',
Name: 'Firm A',
ExternalFK: 0,
FirmType: 4,
PermissionGroup: null,
TemplateFirm: null,
FeeSet: null,
CheckAccounts: true,
TrackHoldings: true,
CanShortSell: true,
CanResellToday: true,
TrackCash: true,
CanUseTodaysCash: true,
CashCanBeNegative: true,
Enterprise: 301,
...
CreateTimestamp: [ 671594592, 162706222 ],
UpdateUser: -1,
UpdateTimestamp: [ 671594592, 162706222 ]
}
Similarly, the type of the Index parameter when using this function within an nq script must be Index<Table>, so the corresponding function Index() may need to be used in order to compose a table index from a number.
//function Index() returns Index<Table> type
const userA = User.at(Index(10));
where
The where function selects the records of a table that match all of the given filter predicates. If any records are found, the type returned is still a table object which can be queried again in the same fashion (as opposed to get and at which return single records).
Syntax: Table.where(...predicates)
mg_11000> Instrument.where(eq('ShortName','USD'))
Id Status ShortName Name ExternalFK FeeBasis FeeBuy FeeSell MinFeePerOrderBuy MinFeePerOrderSell ExternalAccountProvider ExternalCode InstrumentType InstrumentGroup Sector Session OrderType
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
1 0x3 USD US Dollar 0 NotSet MG USD Currency CURRENCY MG CONTINUOUS 0x0
The function accepts a list of comma-separated filter predicates which are implicitly joined together with AND logic, but there are any and all functions to explicitly join the clauses too.
Predicate functions
The table below is a list of functions available to use in the filter predicates when querying tables.
They all take a field name (as a string) for the first parameter, and then the value to compare to as the second parameter.
The type of the field specified and the value to compare with should match. In the case of a field with an enum domain, use
Enum.Valueas the comparison.
| Function | Symbol | Description | Example |
|---|---|---|---|
| eq | = | equal to | eq('InstrumentType', InstrumentType.Currency) |
| ne | != | not equal to | ne('UserType', UserType.System) |
| lt | < | less than | lt('Price', 100) |
| lte | <= | less than or equal to | lte('Quantity', 300) |
| gt | > | greater than | gt('Balance',10000) |
| gte | >= | greater than or equal to | gte('TotalQuantity', 200) |
| mask | & | bitwise AND (true if the record has the bit set) | mask('Status',Status.Active) |
| like | ~ | like | like('ShortName','adm') |
| is_null | null | is null | is_null('ParentAccount') |
As mentioned, there are also logical functions available to group clauses together (comma-separated). These can be used in conjunction with each other as well.
A comma-separated list of clauses is implicity wrapped in
all().
| Function | Description | Example |
|---|---|---|
| any | logical OR | any(like('ShortName','Firm'),eq('CheckAccounts',0)) |
| all | logical AND | all(mask('Type',OrderType.AllOrNone),gte('Matched',10)) |
There is also a logical not function, which can be wrapped around any predicates (including any and all) to negate the statement.
| Function | Description | Example |
|---|---|---|
| not | logical NOT | not(any(like('ShortName','Firm'),eq('CheckAccounts',0))) |
rows
The table returned from a where function has a property called rows. This is an array of the rows of the table, which can be iterated over in a loop if desired.
Syntax: Table.where(...predicates).rows
//Return all records of Holding (change) where AvailableBalance>=100,000
const holding_query = Holding.where(gte('AvailableBalance',100000));
//Loop through each row
for (const row of holding_query.rows) {
//Can access any of the columns in the row
const balance = row.Balance;
//Add to lists or call another function to operate on the data
another_list.push(row);
if (row.InstrumentType === InstrumentType.Currency) myfunc(row);
}
find
The find function returns the first record of a table that matches all of the given filter predicates. It is very similar to where, but the type returned is a single record as opposed to a Table object.
Syntax: Table.find(clauses)
mg_11000> Group.find(like('ShortName','Trade'))
Group {
Id: 100003,
Status: 3,
Firm: 2,
Name: 'Traders',
ShortName: 'Traders',
PermissionGroup: null,
IndexInFirm: 0,
UpdateNumber: 1,
OwnerUser: null,
OwnerGroup: 100003,
OwnerFirm: 2,
CreateUser: -1,
CreateTimestamp: [ 671847813, 307557971 ],
UpdateUser: -1,
UpdateTimestamp: [ 671847813, 307557971 ]
}
The available filter predicates for this function are described in detail in the Predicate functions section above.
sort
The sort function selects records according to the given sort order. It takes one or more sort predicates.
When multiple predicates are used, they are applied in left-to-right order.
Syntax: Table.sort(...predicates)
mg_11000> Holding.sort(desc('Firm'), asc('Instrument'))
Id Status Timestamp ExternalAccount Firm Account AccountName Instrument InstrumentType Tag ExternalId ExternalTransactionId QuantityDecimals OpenBalance PlannedBuy
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
537 0x1 2021-04-16 00:23:33.394562035Z MG:ExtHouse8 FirmH FirmH:House House Account AUD Currency 4 1,000,000.0000
556 0x1 2021-04-16 00:23:33.394562035Z MG:ExtAccount8.1 FirmH FirmH:Client-1 Client 1 AUD Currency 4 1,000,000.0000
575 0x1 2021-04-16 00:23:33.394562035Z MG:ExtAccount8.2 FirmH FirmH:Client-2 Client 2 AUD Currency 4 1,000,000.0000
594 0x1 2021-04-16 00:23:33.394562035Z MG:ExtFloat FirmH FirmH:Float Float Account AUD Currency 4 1,000,000.0000
543 0x1 2021-04-16 00:23:33.394562035Z MG:ExtHouse8 FirmH FirmH:House House Account BHP Standard 10,000
562 0x1 2021-04-16 00:23:33.394562035Z MG:ExtAccount8.1 FirmH FirmH:Client-1 Client 1 BHP Standard 10,000
581 0x1 2021-04-16 00:23:33.394562035Z MG:ExtAccount8.2 FirmH FirmH:Client-2 Client 2 BHP Standard 10,000
600 0x1 2021-04-16 00:23:33.394562035Z MG:ExtFloat FirmH FirmH:Float Float Account BHP Standard 10,000
Sort predicate functions
The following table lists functions that construct sort predicates. Each function takes a single argument, which is the name of the field by which to sort.
| Function | Description | Example |
|---|---|---|
| asc | ascending oder | asc('AccountCode') |
| desc | descending order | desc('Price') |
by
The by function selects records grouped by one or more fields. The result set contains the last record of each combination of the key fields.
Syntax: Table.by(field1, field2)
mg_11000> Holding.by('Firm','InstrumentType')
Id Status Timestamp ExternalAccount Firm Account AccountName Instrument InstrumentType Tag ExternalId ExternalTransactionId QuantityDecimals OpenBalance
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
72 0x1 2021-04-16 00:23:33.394562035Z MG:ExtFloat FirmA FirmA:Float Float Account RIO Standard 10,000
67 0x1 2021-04-16 00:23:33.394562035Z MG:ExtFloat FirmA FirmA:Float Float Account CNY Currency 4 1,000,000.0000
148 0x1 2021-04-16 00:23:33.394562035Z MG:ExtFloat FirmB FirmB:Float Float Account RIO Standard 10,000
143 0x1 2021-04-16 00:23:33.394562035Z MG:ExtFloat FirmB FirmB:Float Float Account CNY Currency 4 1,000,000.0000
224 0x1 2021-04-16 00:23:33.394562035Z MG:ExtFloat FirmC FirmC:Float Float Account RIO Standard 10,000
219 0x1 2021-04-16 00:23:33.394562035Z MG:ExtFloat FirmC FirmC:Float Float Account CNY Currency 4 1,000,000.0000
300 0x1 2021-04-16 00:23:33.394562035Z MG:ExtFloat FirmD FirmD:Float Float Account RIO Standard 10,000
295 0x1 2021-04-16 00:23:33.394562035Z MG:ExtFloat FirmD FirmD:Float Float Account CNY Currency 4 1,000,000.0000
376 0x1 2021-04-16 00:23:33.394562035Z MG:ExtFloat FirmE FirmE:Float Float Account RIO Standard 10,000
371 0x1 2021-04-16 00:23:33.394562035Z MG:ExtFloat FirmE FirmE:Float Float Account CNY Currency 4 1,000,000.0000
As with the where and sort functions, it returns a table object (which also has the rows property available for iterations).
caption
The caption function returns the short caption for a record, specified by Id. This is used on the MG UI to help identify a row.
The caption for most static tables is the
ShortNamefield.
Syntax: Table.caption(id)
mg_11000> Order.caption(1)
'0000000001'