getRecordsByTable

JSON DB "getRecordsByTable" action retrieves records from a database table in table order and performs a full scan of all records in a table

The "getRecordsByTable" action is the most efficient way to retrieve all records from a table and can also retrieve records from the beginning of a table. When a table contains too many records to retrieve all at once, use a cursor, which makes it easy, fast, and efficient to paginate results. See Most effective ways to return data from the JSON DB API. Returning a cursor and returning records directly are mutually exclusive operations.

  • The "getRecordsByTable" action can always read records forwards starting from the beginning of a table.
  • "getRecordsByTable" can read records backward starting from the end of a table but only when the table contains fixed-length records.
    • A record is fixed length only when all its fields are fixed length. In other words, a table contains variable-length records when it has one or more fields with the following data types: "varchar", "lvarchar", "varbinary", "lvarbinary", and "json".
    • If you need to fetch records backward, create a cursor based on an index, such as "getRecordsByIndex", "getRecordsByPartialKeyRange", "getRecordsInKeyRange", and "getRecordsStartingAtKey".
  • Skipping records is fast, but slows down linearly as the number of skipped records increases because skipping walks through each record in the table.
  • An optional "tableFilter" can be applied to each record to further filter the results.
    • A "tableFilter" is a table filter expression, that contains comparisons to fields in the record, such as "salary > 90000".
  • The "getRecordsByTable" action returns records in table order, which is typically the order records are inserted, but this is not always the case.
    • Records may be inserted in the middle of a table to reuse space freed by deleted records; in addition, updated records that require more space are moved to the end of a table.
    • To always get records in insert order, use the "getRecordsByIndex" action with the ID index, which can walk records in insert order forwards and backwards.

Note Returning a cursor and returning records are mutually exclusive operations.

Limitations of variable-length records:
  • When using the "getRecordsByTable" action on a table containing variable-length records, you must set "skipRecords" to a positive integer number, and set "reverseOrder" to false.
  • When using a cursor returned by the "getRecordsByTable" action, you must set "skipRecords" and "fetchRecords" to positive integer numbers.
  • You can only set the "startFrom" property to "beforeFirstRecord" and "currentPosition".
  • To start walking the data again from the beginning, at any time you can set "startFrom" to "beforeFirstRecord".

 

Request examples

Minimal

{
  "action": "getRecordsByTable",
  "params": {
    "tableName": "athlete"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Cursor

{
  "api": "db",
  "requestId": "2",
  "action": "getRecordsByTable",
  "params": {
    "tableName": "athlete",
    "tableFilter": "ranking <= 3",
    "returnCursor": true
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Maximal

{
  "api": "db",
  "apiVersion": "1.0",
  "requestId": "3",
  "action": "getRecordsByTable",
  "params": {
    "databaseName": "ctreeSQL",
    "ownerName": "admin",
    "tableName": "athlete",
    "tableFilter": "ranking <= 3",
    "fixedLengthCharFormat": "sql",
    "returnCursor": false,
    "reverseOrder": false,
    "skipRecords": 0,
    "maxRecords": 20,
    "id": 2084, 
    "bookmark": “000000FF”
  },
  "responseOptions": {
    "includeBookmarks": false,
    "binaryFormat": "hex",
    "dataFormat": "objects",
    "numberFormat": "string",
    "variantFormat": "variantObject",
    "includeFields": [
      "name",
      "ranking"
    ],
    "excludeFields": []
  },
  "debug": "max",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Response examples

Minimal

{
  "result": {
    "dataFormat": "objects",
    "binaryFormat": "hex",
    "fields": [
      {
        "name": "id",
        "type": "bigint",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": false,
        "primaryKey": 1,
        "autoValue": "incrementOnInsert"
      },
      {
        "name": "changeId",
        "type": "bigint",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "changeId"
      },
      {
        "name": "name",
        "type": "varchar",
        "length": 30,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "ranking",
        "type": "smallint",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": false,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "birthDate",
        "type": "date",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "playerNumber",
        "type": "number",
        "length": 32,
        "scale": 6,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "livedPast2000",
        "type": "bit",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "earnings",
        "type": "money",
        "length": 32,
        "scale": 4,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "favoriteSaying",
        "type": "varchar",
        "length": 500,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      }
    ],
    "data": [
      {
        "birthDate": "1963-02-17",
        "changeId": 1291366,
        "earnings": 1700000000,
        "favoriteSaying": "There is no 'i' in team but there is in win.",
        "id": 1,
        "livedPast2000": true,
        "name": "Michael Jordan",
        "playerNumber": 23,
        "ranking": 1
      },
      {
        "birthDate": "1942-01-17",
        "changeId": 1291366,
        "earnings": 60000000,
        "favoriteSaying": "Float like a butterfly, sting like a bee.",
        "id": 3,
        "livedPast2000": true,
        "name": "Muhammad Ali",
        "playerNumber": 1,
        "ranking": 3
      },
      {
        "birthDate": "1895-02-06",
        "changeId": 1291366,
        "earnings": 800000,
        "favoriteSaying": "Every strike brings me closer to the next home run.",
        "id": 2,
        "livedPast2000": false,
        "name": "Babe Ruth",
        "playerNumber": 3,
        "ranking": 2
      },
      {
        "birthDate": "1940-10-23",
        "changeId": 1291366,
        "earnings": 115000000,
        "favoriteSaying": "Everything is practice.",
        "id": 4,
        "livedPast2000": true,
        "name": "Pele",
        "playerNumber": 10,
        "ranking": 4
      },
      {
        "birthDate": "1961-01-26",
        "changeId": 1291366,
        "earnings": 1720000,
        "favoriteSaying": "You miss 100 percent of the shots you never take.",
        "id": 5,
        "livedPast2000": true,
        "name": "Wayne Gretzky",
        "playerNumber": 99,
        "ranking": 5
      },
      {
        "birthDate": "1969-01-03",
        "changeId": 1291366,
        "earnings": 990000000,
        "favoriteSaying": "Once something is a passion, the motivation is there.",
        "id": 6,
        "livedPast2000": true,
        "name": "Michael Schumacher",
        "playerNumber": 1,
        "ranking": 6
      }
    ],
    "primaryKeyFields": [
      "id"
    ],
    "changeIdField": "changeId",
    "moreRecords": false,
    "requestedRecordCount": 20,
    "returnedRecordCount": 6,
    "totalRecordCount": 6
  },
  "debugInfo": {
    "request": {
      "action": "getRecordsByTable",
      "params": {
        "tableName": "athlete"
      },
      "debug": "max",
      "authToken": "replaceWithAuthTokenFromCreateSession"
    },
    "serverSuppliedValues": {
      "databaseName": "ctreeSQL",
      "ownerName": "admin"
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Cursor

{
  "result": {
    "cursorId": "cursorId",
    "totalRecordCount": -1
  },
  "requestId": "2",
  "debugInfo": {
    "request": {
      "api": "db",
      "action": "getRecordsByTable",
      "params": {
        "tableName": "athlete",
        "tableFilter": "ranking <= 3",
        "returnCursor": true
      },
      "requestId": "2",
      "debug": "max",
      "authToken": "replaceWithAuthTokenFromCreateSession"
    },
    "serverSuppliedValues": {
      "databaseName": "ctreeSQL",
      "ownerName": "admin"
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Maximal

{
  "result": {
    "dataFormat": "objects",
    "binaryFormat": "hex",
    "fields": [
      {
        "name": "name",
        "type": "varchar",
        "length": 30,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      },
      {
        "name": "ranking",
        "type": "smallint",
        "length": null,
        "scale": null,
        "defaultValue": null,
        "nullable": false,
        "primaryKey": 0,
        "autoValue": "none"
      }
    ],
    "data": [
      {
        "name": "Michael Jordan",
        "ranking": "1"
      },
      {
        "name": "Muhammad Ali",
        "ranking": "3"
      },
      {
        "name": "Babe Ruth",
        "ranking": "2"
      }
    ],
    "primaryKeyFields": [
      "id"
    ],
    "changeIdField": "changeId",
    "moreRecords": false,
    "requestedRecordCount": 20,
    "returnedRecordCount": 3,
    "totalRecordCount": 3
  },
  "requestId": "3",
  "debugInfo": {
    "request": {
      "api": "db",
      "action": "getRecordsByTable",
      "params": {
        "databaseName": "ctreeSQL",
        "ownerName": "admin",
        "tableName": "athlete",
        "tableFilter": "ranking <= 3",
        "returnCursor": false,
        "reverseOrder": false,
        "skipRecords": 0,
        "maxRecords": 20
      },
      "apiVersion": "1.0",
      "requestId": "3",
      "responseOptions": {
        "binaryFormat": "hex",
        "dataFormat": "objects",
        "numberFormat": "string",
        "variantFormat": "variantObject",
        "includeFields": [
          "name",
          "ranking"
        ],
        "excludeFields": []
      },
      "debug": "max",
      "authToken": "replaceWithAuthTokenFromCreateSession"
    },
    "serverSuppliedValues": {
      "databaseName": "ctreeSQL",
      "ownerName": "admin"
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Properties

Request properties ("params")

Property Description Default Type Limits (inclusive)

bookmark

or

_bookmark_

The "bookmark" (or in some cases "_bookmark_") property designates a universal record identifier (aka bookmark). You can always use a bookmark to identify a record, even when the table lacks a primary key. 

 

The "bookmark" value is used when "startFrom": "bookmark", in which case the user will receive records starting from the value specified by the "bookmark" property (this does not apply to "_bookmark_"). This allows applications to quickly jump to a specific record and retrieve it along with its surrounding data.

 

Using "bookmark" to position the starting record:

{
  "api": "db",
  "action": "getRecordsFromCursor",
  "params": {
    "cursorId": "replaceWithCursorIdFromGetRecords",
    "startFrom": "bookmark",
    "bookmark": "000000FF",
    "skipRecords": -3,
    "fetchRecords": 6
  },
  "responseOptions": {
    "includeBookmarks": true,
    "dataFormat":     "objects"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 Optional with default of "" string 0 to 2048 bytes

databaseName

The "databaseName" property specifies the database that contains an object, such as a table or code package. If it is set to null or is omitted, it defaults to the default database of the JSON Action session, see "createSession" and the "defaultDatabaseName" property. 

You specify this property when you want to use a different database instead of the default. 

This property is useful because objects, such as tables and code packages, can have the same name in multiple databases. This feature allows you to create multiple environments in the same server and reuse the same JSON actions in each environment. For example, you can create "dev", "test", "stage", and "prod" databases on the same server and use the "defaultDatabaseName" or "databaseName" properties to specify the desired environment.

It is an error to set "databaseName" to the empty string "".

If no default database is specified during "createSession", the server sets the "defaultDatabaseName" to the "defaultDatabaseName" value specified in the services.json file.

 Defaults to the session's "defaultDatabaseName" property string 1 to 64 bytes

fixedLengthCharFormat

The "fixedLengthCharFormat" property controls how the "getRecords" actions return values from fields with the "char" data type (CT_FSTRING), which is FairCom's fixed-length string field. It does not apply to other field types, such as "varchar", "lvarchar", "binary", "varbinary", and "lvarbinary".

 

For more details, see "fixedLengthCharFormat". 

 Optional with default "sql" string

"sql"

"trimTrailingSpaces"

"trimTrailingPadding"

id

The "id" property is a unique identifier automatically generated by the server. In JSON, you may use an integer number or a string containing an integer number. The server automatically generates the "id" when you create an object such as a label or thing and stores it in the table as an integer. You cannot alter the "id" value. If your application needs to specify a specific numeric identifier for a label, use the "enum" property. You can use the "id" or "thingName" properties to identify and look up a thing.

 

 Automatically generated by the server integer

0 to 2147483647

0 to 9223372036854770000 in the Thing API

indexName

The "indexName" property specifies the name of an index. A zero-length "indexName" is invalid.

"params": {
  "indexName": "index1"
}

Required - No default value

 string 1 to 64 bytes

maxRecords

 The "maxRecords" property specifies the maximum number of records to be returned. It is used with "skipRecords" to paginate the results. If the value is not null or omitted, the server returns the maximum number of results specified by "maxRecords". Optional with default of 20  integer -1 to 65535

ownerName

The "ownerName" property specifies the account that owns an object, such as a table or code package. See "createSession" and the "defaultOwnerName" property for more details. 

You specify this property when you want to use a different account instead of the default. Your session's account must have the appropriate privileges to access the code package. 

This property is useful because objects, such as tables and code packages, can have the same name in the same database as long as different accounts own each object. This feature allows you to create duplicate objects for different users on the same server and reuse the same JSON actions on those objects. For example, an administrator can copy objects from a production environment to her account so she can troubleshoot an issue using the same JSON actions, JavaScript, and SQL code.

It is an error to set "ownerName" to the empty string "".

If no default owner is specified during "createSession", the server sets the "defaultOwnerName" to the "defaultOwnerName" value specified in the services.json file.

 Optional with default of the session's "defaultOwnerName" property string 1 to 64 bytes

returnCursor

The "returnCursor" property causes the action to return a cursor instead of directly returning records when set to true.

  • To retrieve records, call the "getRecordsFromCursor" action and pass the "cursorId" value into it.
  • When "returnCursor" is "true" and one of the "skipRecords", "maxRecords", and "reverseOrder" properties is specified, an error is returned.
    • "returnCursor" is mutually exclusive with the "skipRecords", "maxRecords", and "reverseOrder" properties.
 Optional with default of false Boolean

true

false

reverseOrder

The "reverseOrder" returns results in reverse order when set to true. 

  • It works with "maxRecords" and "skipRecords" to provide pagination.
  • An error is returned when "returnCursor" is true.
 Optional with default of false Boolean

true

false

skipRecords

 The "skipRecords" property specifies the number of records to skip over when paginating the results. It is used with "maxRecords" to paginate the results. If the value is not null or omitted, the server returns results from the beginning. If it is > 0, the server skips over the specified number of records and returns results starting from that point up until it returns the maximum number of results as defined by "maxRecords". Optional with default of 0 integer 0 to 9223372036854775807

tableFilter

tableFilter

The "tableFilter" property filters the results by the value of the "tableName" property. No table filter is applied when it is an empty string, a null value, or is omitted. It is a server-side filter of the records in a table. It includes records in the result only when they match the filter requirements. It works like a SQL WHERE clause except for using C syntax and C functions.

  • See Use Table Filters for more information and examples.
  • It uses FairCom's expression language, which is based on C syntax and supports arbitrarily nested expressions, operators, and functions, such as:
    • "tableFilter": "((name IS NOT NULL && name != \"Michael Jordan\" && strnicmp( name, \"m\", 1 ) == 0 && (ranking - 5) * 2 <= 6 && livedPast2000 ) || ( earnings < 1000000 && ! livedPast2000 )) && (ranking % 2 == 1)"
  • A zero-length table filter, such as "tableFilter": "" does not filter any records.
  • A "tableFilter" can be combined with other query techniques. For example, the "getRecordsInKeyRange" action can be used to retrieve a limited range of records that are further filtered by a "tableFilter".
  • To include a double quote character in a "tableFilter" expression, precede it with the backslash (escape) character, \".
  • The first time you include a "tableFilter" string in a "getRecords..." action, the server processes the string to produce an optimized filter. The server automatically reuses the optimized filter in subsequent calls to eliminate the initial processing overhead.
 Optional with default of "" string

tableName

The "tableName" property contains the unique, user-defined name of a table.

See table name in System specifications for the table naming requirements and limitations.

 

"params": {
  "tableName": "ctreeTable"
}
 Required - No default value string 1 to 64 bytes

 

Response properties ("result")

Property Description Type Limits (inclusive)

binaryFormat

 The "binaryFormat" property designates the format of binary values embedded in JSON strings. See binaryFormat for more details.  string One of the following: "base64", "hex", or "byteArray".

changeIdField

This property's value designates the name of the field used for change-tracking functionality if you are not using the "changeId" field for change tracking.

"createTable" automatically creates the "changeId" field to hold the change tracking number used for optimistic locking. Using the "changeId" field for optimistic locking is a best practice.

However, if you use the name "changeId" for another purpose, you can use the "changeIdField" property to designate a different field as the change tracking number field.

 string 1 to 64 bytes

cursorid

The "cursorId" property is a unique identifier returned by the server.

  • The "getRecordsFromCursor" action uses it to quickly and efficiently retrieve paginated records.
  • It is not returned when "returnCursor" is false.

Important Do not assume the "cursorId" is a number embedded in a string.

 string 0 to 255 bytes

data

The "data" property contains a response message. Its contents are defined by the action. It is an empty array when no results are available. The following is an example of the data property from a code package action.

  "result": {
    "data": [
      {
        "codeId": 6,
        "databaseName": "faircom",
        "ownerName": "admin",
        "codeName": "convertAndCategorizeTemperature",
        "codeVersion": 1,
        "clonedCodeId": 1,
        "codeStatus": "active",
        "codeLanguage": "javascript",
        "serviceName": "javascript",
        "codeType": "module",
        "description": "optional new description",
        "metadata": {},
        "createdBy": "ADMIN",
        "createdOn": "2025-08-25T21:48:38.109",
        "updatedBy": "ADMIN",
        "updatedOn": "2025-08-25T21:48:38.109",
        "comment": "Cloned from convertTemperature",
        "codeFormat": "utf8"
      },
    ]
 array of objects The action determines its contents.

dataFormat

The "dataFormat" property (case-insensitive) defines the format of the "data" property. The default for "dataFormat" can be changed during a "createSession" action by assigning a different value to the "dataFormat" property in "defaultResponseOptions".

  • "dataFormat" in the response shows the client how the server formatted the "data" property.
    • Possible values include:
      • "arrays"
        • This is the default and causes the server to return results as an array of arrays, which is the most efficient.
      • "objects"
        • This returns results as an array of objects. This is less efficient but is simpler to generate, read, and troubleshoot.
 string

"autoDetect"

"arrays"

"objects"

fields

The "fields" property is an array of objects. Each object in the array defines a field by specifying its properties.

 

"fields": [
  {
    "autoValue": "none",
    "name": "name",
    "type": "varchar",
    "length": 50,
    "scale": null,
    "defaultValue": null,
    "nullable": false
  }
]
 array

"autoTimestamp"

"autoValue"

"primaryKey"
"name"
"type"
"length"
"scale"
"defaultValue"
"nullable"

 

updateRecords and deleteRecords only: 

"bookmark"

fields

.autoValue

 The "autoValue" property indicates when and how the server automatically sets the field value. See autoValue for more details.  string

"none"

"incrementOnInsert"

"timestampOnInser"

"timestampOnUpdate"

"timestampOnUpdateAndInsert"

"changeid"

 

Some actions only:

"bookmark"

fields

.defaultValue

 The "defaultValue" property specifies the default value of a field. string 0 to 65,500 bytes

fields

.length

 Identifies the length of the field. integer 1 to 65500 

fields

.name

The "name" property is the name of a label or field. 

The "group" and "name" properties combined uniquely identify each label. The "createLabel" and "alterLabel" actions return an error if the "group" and "name" properties do not create a unique label name.

The "id" property also uniquely identifies each label so you can rename a label's group and name without breaking "id" references to the label.

 

 string 1 to 64 bytes

fields

.nullable

 "nullable" identifies whether a field can contain a NULL value. Boolean

true

false

fields

.primaryKey

 When > 0, the "primaryKey" property identifies the ordinal position of the field within the table's primary key. integer 0 to 32

fields

.scale

 If the type is "number" or "money", the "scale" property identifies the number of places to the right of the decimal point,. integer 0 to 32

fields

.type

 Identifies the type of the field. See Data types. string

"bit"

"tinyint"

"smallint"

"integer"

"bigint"

"real"

"float"

"number"

"money"

"date"

"time"

"timestamp"

"char"

"varchar"

"lvarchar"

"binary"

"varbinary"

"lvarbinary"

"json"

moreRecords

 The "moreRecords" property indicates if there are more records that match the filters in the request.  Boolean

true

false

primaryKeyFields

This property specifies the fields of the table’s primary key when multiple fields are combined to form the primary key.

Note The best practice is not to use the "primaryKeyFields" or "primaryKey" properties, so the "createTable" action will automatically create a primary key field named "id" with a unique index named "id_pk".

The order of fields in this property is the order of fields in the primary key index. The "fields" property contains the name and type of each field that is specified in the "primaryKeyFields" array. 

A primary key with multiple fields has an index named "pk". If you specify just one field, the index is named "<fieldname>_pk".

If only one field is used as the primary key, the "primaryKey" property defines the primary key.

Note The "primaryKeyFields" and "primaryKey" properties cannot be used together.

 

Example

"primaryKeyFields": [
  "a",
  "b",
  "c"
],
"fields": [
  {
    "name": "a",
    "type": "tinyint"
  },
  {
    "name": "b",
    "type": "smallint"
  },
  {
    "name": "c",
    "type": "integer"
  }
]
 array an array

requestedRecordCount

The "requestedRecordCount" property is a signed, 32-bit integer set by the server in response to the "getRecordsFromCursor" method.

  • It makes it easy to know how many records were requested in the last call to "getRecordsFromCursor".
  • An application can use "requestedRecordCount" in conjunction with "returnedRecordCount" to determine if fewer records were returned than requested, which occurs when the cursor reaches the end of the recordset.
 integer

0 to 2147483647

returnedRecordCount

The "returnedRecordCount" is a 32-bit integer set by the server in response to the "getRecordsFromCursor" method.

  • It makes it easy to know how many records were returned from the last call to "getRecordsFromCursor".
  • An application can use "returnedRecordCount" in conjunction with "requestedRecordCount" to determine if fewer records were returned than requested, which occurs when the cursor reaches the end of the recordset.
 integer

0 to 2147483647

totalRecordCount

The "totalRecordCount" property contains the total available number of records that can be returned from a query.

  • The "totalRecordCount" is set to -1, when the server does not know the total record count.
  • A very fast way to get the total number of records in a table is to call the "getRecordsByTable" method without applying a "tableFilter". This immediately returns the count without reading and counting records.
  • For most methods, the server does not calculate "totalRecordCount" because calculating it requires walking all records in the query, which may take a significant amount of time.
  • When the result is returned as a cursor, "totalRecordCount" is the total number of records that the cursor can traverse.
    • This does not apply to cursor responses.
  • When the result returns records directly, "totalRecordCount" is the total number of records that can be retrieved – not necessarily the number of records returned.
 integer

-1 to 99999999999999999999999999999999

Was this article helpful?

Give feedback about this article

Related Articles