Bulk Retrieval Using Record ID

kintone provides a REST API that allows you to retrieve saved record information from other apps or external services.

However, there is a limit to the number of records that can be retrieved at once, and if this limit is exceeded, multiple retrievals are necessary.

There are several approaches to retrieving records in multiple batches:

• Using cursors
• Using record IDs

Among these, the cursor API is intended for use in nightly batch processing and has a restriction that only 10 cursors can be created simultaneously within the same domain.

Therefore, for regular applications, the method of retrieving records using record IDs is more suitable.

This page introduces the seek method, which allows bulk retrieval based on record IDs.

About the Retrieval Method

The flow of the retrieval method we will implement is broadly as follows:

• Sort by record ID
• Retrieve records
• If the number of retrieved records is the same as the retrieval limit, retrieve records with IDs smaller (or larger) than the last retrieved record ID
• If the number of retrieved records is less than the limit, return all retrieved records

In essence, it is a method of gradually collecting records in order of record ID.

It is very simple and easier to understand than using the cursor API.

The Code on the Developer Network is Confusing

First, I checked the method using record IDs posted on the developer network. While I understood what it was trying to do, the sample code was confusing.

Click the arrow to view the entire source code

/*
 * get all records function by using record id sample program
 * Copyright (c) 2019 Cybozu
 *
 * Licensed under the MIT License
 */

/*
 * @param {Object} params
 *   - app {String}: App ID (default is the current app)
 *   - filterCond {String}: Filter condition
 *   - sortConds {Array}: Array of sort conditions
 *   - fields {Array}: Array of fields to retrieve
 * @return {Object} response
 *   - records {Array}: Array of retrieved records
 */
const getRecords = (_params) => {
  const MAX_READ_LIMIT = 500;

  const params = _params || {};
  const app = params.app || kintone.app.getId();
  const filterCond = params.filterCond;
  const sortConds = params.sortConds || ['$id asc'];
  const fields = params.fields;
  let data = params.data;

  if (!data) {
    data = {
      records: [],
      lastRecordId: 0,
    };
  }

  const conditions = [];
  const limit = MAX_READ_LIMIT;
  if (filterCond) {
    conditions.push(filterCond);
  }

  conditions.push('$id > ' + data.lastRecordId);

  const sortCondsAndLimit = ` order by ${sortConds.join(', ')} limit ${limit}`;
  const query = conditions.join(' and ') + sortCondsAndLimit;
  const body = {
    app: app,
    query: query,
  };

  if (fields && fields.length > 0) {
    // Add the "$id" field to the fields to retrieve if it is not already included, as sorting is done by $id
    if (fields.indexOf('$id') <= -1) {
      fields.push('$id');
    }
    body.fields = fields;
  }

  return kintone.api(kintone.api.url('/k/v1/records', true), 'GET', body).then((r) => {
    data.records = data.records.concat(r.records);
    if (r.records.length === limit) {
      // If the number of retrieved records is the same as the limit, there may be remaining records to retrieve, so recursively call getRecords to retrieve the remaining records
      data.lastRecordId = r.records[r.records.length - 1].$id.value;
      return getRecords({
        app: app,
        filterCond: filterCond,
        sortConds: sortConds,
        fields: fields,
        data: data,
      });
    }
    delete data.lastRecordId;
    return data;
  });
};

The scenarios where this retrieval method is suitable are introduced as follows:

This method is suitable for scenarios such as:

When no specific sort order is required (record ID order is acceptable) When records can be sorted by program logic after retrieving them in record ID order Bulk retrieval of kintone records considering the offset limit

However, the sample code allows specifying the sort order as an argument, and it does not work correctly if the sort order is specified.

Check the Official Library

Next, I checked the officially provided SDK.

The part that performs bulk retrieval of records is here.

class RecordClient {
  /* ..omitted.. */

  public async getAllRecordsWithId<T extends Record>(params: {
    app: AppID;
    fields?: string[];
    condition?: string;
  }): Promise<T[]> {
    const { fields: originalFields, ...rest } = params;
    let fields = originalFields;
    // Append $id if $id doesn't exist in fields
    if (fields && fields.length > 0 && fields.indexOf('$id') === -1) {
      fields = [...fields, '$id'];
    }
    return this.getAllRecordsRecursiveWithId({ ...rest, fields }, '0', []);
  }

  private async getAllRecordsRecursiveWithId<T extends Record>(
    params: {
      app: AppID;
      fields?: string[];
      condition?: string;
    },
    id: string,
    records: T[]
  ): Promise<T[]> {
    const GET_RECORDS_LIMIT = 500;

    const { condition, ...rest } = params;
    const conditionQuery = condition ? `(${condition}) and ` : '';
    const query = `${conditionQuery}$id > ${id} order by $id asc limit ${GET_RECORDS_LIMIT}`;
    const result = await this.getRecords<T>({ ...rest, query });
    const allRecords = records.concat(result.records);
    if (result.records.length < GET_RECORDS_LIMIT) {
      return allRecords;
    }
    const lastRecord = result.records[result.records.length - 1];
    if (lastRecord.$id.type === '__ID__') {
      const lastId = lastRecord.$id.value;
      return this.getAllRecordsRecursiveWithId(params, lastId, allRecords);
    }
    throw new Error(
      'Missing `$id` in `getRecords` response. This error is likely caused by a bug in Kintone REST API Client. Please file an issue.'
    );
  }

  /* ..omitted.. */
}

After organizing the data in the getAllRecordsWithId part, the getAllRecordsRecursiveWithId part recursively performs bulk retrieval of records. This time, the sort order cannot be specified.

If you can use the @kintone/rest-api-client, most scenarios can be resolved by using getAllRecordsWithRecordId.

Since I understood the meaning, I created the function from scratch.

Sample Code

Based on the information so far, I created a custom function for bulk retrieval of records using record IDs.

TypeScript

const CHUNK_SIZE = 500;

export const getAllRecordsWithId = async <
  T extends { $id: unknown } & Record<string, unknown>
>(props: {
  app: number | string;
  fields?: (keyof T)[];
  onStep?: (current: T[]) => void;
  condition?: string;
}): Promise<T[]> => {
  const { fields: originalFields = [], condition = '' } = props;

  const fields = [...new Set([...originalFields, '$id'])];

  return getRecursive<T>({ ...props, fields, condition });
};

const getRecursive = async <T>(props: {
  app: number | string;
  fields: (keyof T)[];
  condition: string;
  onStep?: (current: T[]) => void;
  id?: string;
  stored?: T[];
}): Promise<T[]> => {
  const { app, fields, condition, id } = props;

  const newCondition = id ? `${condition ? `${condition} and ` : ''} $id < ${id}` : condition;

  const query = `${newCondition} order by $id desc limit ${CHUNK_SIZE}`;

  const { records } = await kintone.api(kintone.api.url('/k/v1/records', true), 'GET', {
    app,
    fields,
    query,
  });

  const stored = [...(props.stored ?? []), ...records];

  if (props.onStep) {
    props.onStep(stored);
  }

  const lastRecord = stored[stored.length - 1];
  const lastId = lastRecord.$id.value;

  return records.length === CHUNK_SIZE ? getRecursive({ ...props, id: lastId, stored }) : stored;
};

JavaScript

const CHUNK_SIZE = 500;

/**
 * @template T
 * @param props {{ app: number | string; fields?: (keyof T)[]; onStep?: (current: T[]) => void; condition?: string; }}
 * @returns { Promise<T[]> }
 */
export const getAllRecordsWithId = async (props) => {
  const { fields: originalFields = [], condition = '' } = props;

  const fields = [...new Set([...originalFields, '$id'])];

  return getRecursive({ ...props, fields, condition });
};

/**
 * @template T
 * @param props {{ app: number | string; fields: (keyof T)[]; condition: string; onStep?: (current: T[]) => void; id?: string; stored?: T[];}}
 * @returns  { Promise<T[]> }
 */
const getRecursive = async (props) => {
  const { app, fields, condition, id } = props;

  const newCondition = id ? `${condition ? `${condition} and ` : ''} $id < ${id}` : condition;

  const query = `${newCondition} order by $id desc limit ${CHUNK_SIZE}`;

  const { records } = await kintone.api(kintone.api.url('/k/v1/records', true), 'GET', {
    app,
    fields,
    query,
  });

  const stored = [...(props.stored ?? []), ...records];

  if (props.onStep) {
    props.onStep(stored);
  }

  const lastRecord = stored[stored.length - 1];
  const lastId = lastRecord.$id.value;

  return records.length === CHUNK_SIZE ? getRecursive({ ...props, id: lastId, stored }) : stored;
};

Points

I divided the function into two parts, following the SDK, but either way is fine.

Arguments

Regarding the arguments, I made the app ID mandatory to ensure portability.

It is possible to set it to execute kintone.getId() if not specified, but this can lead to unexpected errors, so I made it mandatory this time.

Also, what I wanted to do this time was to add a callback function called onStep to the arguments.

This allows you to process the latest record information at any time before all data is retrieved.

Retrieval Order

In the sample, data was retrieved in ascending order of record ID, but I thought it would be more useful to retrieve newer data first, so I retrieved it in descending order of record ID.

According to the article on the seek method, ascending order is also fine, but I thought descending order would be more convenient.

Summary

This time, I introduced a method to bulk retrieve app records using record IDs.

It is a simple method and easier to understand than using the cursor API.

Also, since the official SDK provides a method for retrieving records using record IDs, I created a function based on that.

By using this function, you can bulk retrieve app records using record IDs.

Please give it a try.