getRecordsByIds

JSON DB "getRecordsByIds" action retrieves records from a database table using their IDs

The "getRecordsByIds" action retrieves one or more records from a database table using their IDs. The response message contains one record for each ID specified in the "id" array.

  • A table must have a primary key or ID to retrieve records.
  • Tables created by the "createTable" action, automatically have an auto increment "id" field indexed as a primary key.
  • Tables created by other APIs may not have an "id" field; instead, they may have a primary key index that includes one or more fields in the table.
  • The "getRecordsByIds" action provides an optional "primaryKeys" property that can retrieve records using any primary key index.
  • The "primaryKeys" property:
    • can be used to retrieve one or more records from a database table using primary key identifiers
    • is required if "id" is omitted
    • is "null" if using "ids"
  • The "id" property:
    • is required if "primaryKeys" is omitted and supplied by the client to uniquely identify each request
    • is "null" if using the "primaryKeys" property
    • is the client's message identifier and should be a string containing a value that is unique to the client
    • may also be a number, array, or object (this is discouraged)
  • In asynchronous protocols, such as MQTT, "getRecordsByIds" enables the client to associate response messages with the original request messages since the server adds this property (with the client's value) to each response message.
  • JSON DB API sends a response message with an error if "id" is missing or null. Although jsonAction allows the "id" to be null or omitted, JSON DB API does not.
  • The server includes the "id" in reply messages using the exact value and data type that the client supplied in the request. If the client sends a string, the server replies with the identical string. If it sends a number, the server replies with the identical number. This behavior is required by jsonAction. A string is preferred because it is the fastest and easiest for the server to return the identical value. If a number is used, it should be an integer to avoid differences in binary rounding behavior between the client and the server.
  • The "id" should be unique from the client's perspective. From the server's perspective, the true unique "id" of a request-response message pair is the combination of the client id plus the server’s auth id for that client. The "id" will likely not be unique from the server's perspective since different clients can use the same "id" values).
Usage scenarios:
  • Retrieving one record by its ID
  • Loading a previously saved set of IDs to quickly retrieve a set of records
  • Using foreign key IDs to look up records
Performance considerations:
  • The fastest way to retrieve a specific record is to look it up by its ID.
  • Results start to return more slowly as more records are requested.

 

Request examples

Arrays

{
  "api": "db",
  "action": "getRecordsByIds",
  "requestId": "1",
  "params": {
    "tableName": "athlete",
    "ids": [
      3
    ]
  },
  "responseOptions": {
    "dataFormat": "arrays"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

"primaryKeys"

{
  "api": "db",
  "action": "getRecordsByIds",
  "requestId": "2",
  "params": {
    "databaseName": "ctreeSQL",
    "tableName": "athlete",
    "primaryKeys": [
      [
        {
          "fieldName": "id",
          "value": 3
        }
      ]
    ]
  },
  "responseOptions": {
    "dataFormat": "objects"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Return objects

{
  "api": "db",
  "apiVersion": "1.0",
  "requestId": "3",
  "action": "getRecordsByIds",
  "params": {
    "databaseName": "ctreeSQL",
    "ownerName": "admin",
    "tableName": "athlete",
    "ids": [
      6,
      2,
      4
    ]
  },
  "responseOptions": {
    "binaryFormat": "hex",
    "dataFormat": "objects",
    "numberFormat": "string",
    "includeFields": [],
    "excludeFields": []
  },
  "debug": "max",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Maximal

{
  "api": "db",
  "action": "getRecordsByIds",
  "requestId": "1",
  "params": {
    "databaseName": "ctreeSQL",
    "ownerName": "admin",
    "tableName": "athlete",
    "fixedLengthCharFormat": "sql",
    "bookmarks": [
      "00000001",
      "00000002" 
    ]
    "ids": [
      3
    ]
  },
  "responseOptions": {
    "includeBookmarks": false, 
    "dataFormat": "objects",
    "binaryFormat": "hex",
    "numberFormat": "string",
    "variantFormat": "variantObject",
    "includeFields": [
      "name",
      "ranking"
    ],
    "excludeFields": []
  },
  "debug": "max",
  "requestId": "3",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Response examples

Arrays

{
  "result": {
    "dataFormat": "arrays",
    "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": [
      [
        3,
        1291366,
        "Muhammad Ali",
        3,
        "1942-01-17",
        1,
        true,
        60000000,
        "Float like a butterfly, sting like a bee."
      ]
    ],
    "primaryKeyFields": [
      "id"
    ],
    "changeIdField": "changeId",
    "moreRecords": false,
    "requestedRecordCount": 0,
    "returnedRecordCount": 1,
    "totalRecordCount": 1
  },
  "requestId": "3",
  "debugInfo": {
    "request": {
      "api": "db",
      "action": "getRecordsByIds",
      "params": {
        "databaseName": "ctreeSQL",
        "tableName": "athlete",
        "primaryKeys": [
          [
            {
              "fieldName": "id",
              "value": 3
            }
          ]
        ]
      },
      "requestId": "2",
      "responseOptions": {
        "dataFormat": "arrays"
      },
      "debug": "max",
      "authToken": "replaceWithAuthTokenFromCreateSession"
    },
    "serverSuppliedValues": {
      "databaseName": "ctreeSQL",
      "ownerName": "admin"
    },
    "errorData": {
      "errorData": null
    },
    "warnings": []
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Objects

{
  "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": "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"
      },
      {
        "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"
      }
    ],
    "primaryKeyFields": [
      "id"
    ],
    "changeIdField": "changeId",
    "moreRecords": false,
    "requestedRecordCount": 3,
    "returnedRecordCount": 3,
    "totalRecordCount": 3
  },
  "requestId": "3",
  "debugInfo": {
    "request": {
      "api": "db",
      "action": "getRecordsByIds",
      "params": {
        "databaseName": "ctreeSQL",
        "ownerName": "admin",
        "tableName": "athlete",
        "ids": [
          6,
          2,
          4
        ]
      },
      "apiVersion": "1.0",
      "requestId": "3",
      "responseOptions": {
        "binaryFormat": "hex",
        "dataFormat": "objects",
        "numberFormat": "string",
        "includeFields": [],
        "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)

bookmarks

The "bookmarks" property designates one or more universal record identifiers (aka bookmarks). You can always use a "bookmark" to identify a record, even when the table lacks a primary key. 

 

Example

{
   "api": "db",
   "action": "getRecordsByIds",
    "params": {
        "tableName": "no_key",
        "bookmarks": [
            "00000001",
            "00000002"
        ]
    },
    "authToken": "anAuthorizationTokenFromTheServer"
}
 Optional with default of [] array of strings 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"

ids

The "ids" property specifies 0 or more ids. Each identifier in the array uniquely specifies a table row, indicating which records the action affects. It is required if the "primaryKeys" property is "null" or not specified.

  • The "ids" property is mutually exclusive with the "primaryKeys" property meaning it is required when "primaryKeys" is omitted or an error is returned if both have values.
  • It is typically an array of integers ("ids": [1,3,5]).
  • It can be an array of an array of strings ("ids": ["9555444333222111","9555444333222112", "9555444333222113"]).
    • A string "id" supports numbers larger than 9,007,199,254,740,991.
    • This is the largest number supported by many programming languages and JSON parser implementations that use IEEE double-precision floats to hold numbers.
  • It can be the primary key value of another field in the table making it useful when your table is created by another API, such as SQL, that allows any field in the table to be the primary key.
    • If your table does not have an "id" field but uses a "vin" field as the primary key, you can use vin values to look up records ("ids": [ "4Y1SL65848Z411439", "1HGBH41JXMN109186" ]).
  • If your table uses more than one field as the primary key, you must use the "primaryKeys" property to look up records.

Tip The "getRecordsByIds" action uses a primary key index to look up records. A primary key index must be a unique, non-null index without conditional filtering. For best performance and maximum simplicity, create tables using the JSON DB API because it automatically creates an auto increment "id" field that is indexed as a primary key.

Optional with default of "null".

Required when "primaryKeys" is omitted

 array 0 or more ids

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

primaryKeys

The "primaryKeys" property specifies primary keys to use as search criteria and to show the results found. For more details, see "primaryKeys".

Optional with default of "null".

Required when "ids" is omitted.

array of arrays

 1 or more array of key/value pairs.

primaryKeys

.fieldName

 The "fieldName" property specifies the name of a field in a table. Required - No default value string 1 to 64 bytes

primaryKeys

.value

The "value" property is used by the server to compare the value assigned to "value" to the appropriate field data in records.

 

In Key-Value actions, the required "value" property contains a JSON value, which may be up to 2 gigabytes in length. It can be any JSON value, such as an object, array, string, number, true, false, or null.

 Required - No default value string

"string"

"integer"

"number"

"boolean"

"null"

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

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