deleteRecords

JSON DB "deleteRecords" action deletes records from a table using its primary key

The "deleteRecords" action deletes records from a database table using its primary key, such as the id field.

  • The action returns the values from the deleted records in case you want to insert them again, view them, or verify the correct records are deleted.
  • If the specified records do not exist, the action returns success because this is the desired end result.
  • The "totalRecordCount" property in the response is the number of records that are deleted by this action.
  • A table must have a primary key to delete records.
    • Tables created by the "createTable" action, automatically have an auto increment "id" field indexed as 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 "deleteRecords" action provides an optional "primaryKeys" property that can retrieve records using any primary key index.
    • The "primaryKeys" and "ids" properties are mutually exclusive.
  • Use the "primaryKeys" property to delete records from a table that uses a natural primary key instead of the identity field.
    • The outer array allows you to delete multiple records.
    • The inner array defines the field values of each primary key field. It must be an array because primary keys may contain multiple fields.

 

Request examples

Minimal

{
  "action": "deleteRecords",
  "params": {
    "tableName": "test1",
    "ids": [
      1,
      2
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Delete one record using a multi-field primary key

{
  "api": "db",
  "action": "deleteRecords",
  "params":
  {
    "tableName": "pk_example",
    "primaryKeys": 
    [ 
      [
        {
          "fieldName": "first_name",
          "value": "Sam"
        },
        {
          "fieldName": "last_name",
          "value": "I-am"
        }
      ]
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Delete multiple records using a multi-field primary key

This example deletes two records. The outer array identifies each record. The inner array specifies the field values of each record’s primary key: "first_name" and "last_name".

{
  "api": "db",
  "action": "deleteRecords",
  "params":
  {
    "tableName": "pk_example",
    "primaryKeys": 
    [ 
      [
        {
          "fieldName": "first_name",
          "value": "Sam"
        },
        {
          "fieldName": "last_name",
          "value": "I-am"
        }
      ],
      [
        {
          "fieldName": "first_name",
          "value": "The Cat"
        },
        {
          "fieldName": "last_name",
          "value": "in the Hat"
        }
      ]
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Maximal

{
  "api": "db",
  "apiVersion": "1.0",
  "requestId": "4",
  "action": "deleteRecords",
  "params": {
    "databaseName": "ctreeSQL",
    "ownerName": "admin",
    "tableName": "test1",
    "bookmarks": [
      "00000001",
      "00000002" 
    ]
    "ids": [
      1,
      2
    ]
  },
  "responseOptions": {
    "binaryFormat": "hex",
    "dataFormat": "objects",
    "numberFormat": "string",
    "includeFields": [],
    "excludeFields": [],
  },
  "debug": "max",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

 

Response examples

Note If the target object does not exist, the response returns success, and the debug property contains a warning that the object was not found.

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": 50,
        "scale": null,
        "defaultValue": null,
        "nullable": true,
        "primaryKey": 0,
        "autoValue": "none"
      }
    ],
    "data": [],
    "primaryKeyFields": [
      "id"
    ],
    "changeIdField": "changeId",
    "totalRecordCount": 0
  },
  "errorCode": 0,
  "errorMessage": "",
  "authToken": "replaceWithAuthTokenFromCreateSession"
}
 
 

Maximal

{
  "result": {
      "dataFormat": "objects",
      "binaryFormat": "hexadecimal",
      "fields": [
          {
              "name": "id",
              "type": "bigint",
              "length": null,
              "scale": null,
              "autoTimestamp": "none",
              "defaultValue": null,
              "nullable": false
          },
          {
              "name": "changeId",
              "type": "bigint",
              "length": null,
              "scale": null,
              "autoTimestamp": "none",
              "defaultValue": null,
              "nullable": true
          },
          {
              "name": "name",
              "type": "varchar",
              "length": 50,
              "scale": null,
              "autoTimestamp": "none",
              "defaultValue": null,
              "nullable": true
          }
      ],
      "data": [
          {
              "changeId": 22788,
              "id": 2,
              "name": "jane"
          }
      ],
      "totalRecordCount": 1
  },
  "requestId": "4",
  "debugInfo": {
      "request": {
          "api": "db",
          "action": "deleteRecords",
          "params": {
              "databaseName": "ctreeSQL",
              "ownerName": "admin",
              "tableName": "test1",
              "ids": [
                  2
              ]
          },
          "apiVersion": "1.0",
          "requestId": "4",
          "responseOptions": {},
          "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. 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 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

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 limits 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.

data

.changeId

 The "changeId" property specifies a unique, server-generated id. When this property is present, the database compares the value of "changeId" to the current transaction number stored in the "changeId" field of the record. This ensures an update does not modify a record that has subsequently been modified by another operation.  integer No limit

data

.id

The "id" property is the unique identifier of an object such as a label or thing. In JSON, you may use an integer number or a string containing an integer number. The server automatically generates the "id" when you create a label and stores it in the label 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.

 integer

0 to 2147483647

0 to 9223372036854770000 in the Thing API 

data

.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

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

.autoTimestamp

This "autoTimestamp" property works only with fields that have a type of "timestamp". The default value is "none", which prevents the server from populating the value of the field automatically.

The value is an official timestamp of the date and time when the server inserted or updated the associated field. The timezone is always UTC time (ZULU time). Once set, the value cannot be changed.

When the value is "onInsert", the server automatically populates the value of the field when the associated field is inserted.

When the value is "onUpdate", the server automatically populates the value of the field each time the associated field is updated.

When the value is "onUpdateOrInsert", the server automatically populates the value of the field each time the associated field is inserted or updated.

"fields": [
  {
    "name": "name",
    "type": "timestamp",
    "autoTimestamp": "onupdate"
  }
]
 string

"none"

"onInsert"

"onUpdate"

"onUpdateAndInsert"

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 field's default value. string 0 to 65,500 bytes

fields

.length

 The "length" property 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

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

true

false

fields

.primaryKey

The "primaryKey" property identifies a table's primary key. For more details, see "primaryKey".

 integer 0 to 32

fields

.scale

The "scale" property specifies the number of fixed decimal places to the right of the decimal point. Its value must always be less than or equal to the field's length. It is required only for the "number" and "money" data types because they require fixed precision. It is ignored for all other data types. See also Data types. The scale 

The value of "scale" must be an integer from 0 to the number of digits specified by the "length" property. 

A scale of 0 creates an integer number. A scale equal to the length creates a number that can only have a fractional value.

The "money" field type must have a scale of 2 or 4. The default is 4.

You may optionally use the "length" property to specify fewer than 32 total digits to limit the total number of digits available to the number. A length limit reduces the maximum size of the scale. For example, a length of 3 allows the scale of a "number" to be 0, 1, 2, or 3. 

Example numbers allowed in "number" and "money" field types with a length of 4 and a scale from 0 to 4.

 

Request Example

Create a table that contains all field types that use the "scale" property.

"fields": [
  {
    "name": "j",
    "type": "number",
    "scale": 32
  },
  {
    "name": "k",
    "type": "number",
    "scale": 4
  }


],
 integer 0 to 32

fields

.type

The "type" property specifies the field's data type. See Data types for the limits, valid values, and whether "length" and "scale" are required.

 

Request example

"fields": [
  {
    "name": "j",
    "type": "number"
  }
]
 string

"json"

"bit"

"tinyint"

"smallint"

"integer"

"bigint"

"real"

"float"

"number"

"money"

"date"

"time"

"timestamp"

"char"

"varchar"

"lvarchar"

"binary"

"varbinary"

"lvarbinary"

primaryKeyFields

 The "primaryKeyFields" property specifies the fields to use for the table’s primary key. For more details, see "primaryKeyFields". array of strings ["field1", …,"fieldN"]

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