REST API Server Index SCAN API

The REST API Server supports index scan operations in addition to primary key read operations. An index scan allows retrieving multiple rows matching filter conditions, optionally using an ordered index to limit the scan range and control the ordering of results.

Example table definition

Assume we have the following table.

CREATE TABLE `tbl` (
  `pk` int NOT NULL,
  `val_1` int DEFAULT NULL,
  `val_2` int DEFAULT NULL,
  `val_3` double DEFAULT NULL,
  `content` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `idx_val` (`val_1`,`val_2`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

SCAN API request

POST /{api-version}/DB/TABLE/scan

Body:

{
    "limit": 10,
    "readColumns": [
        {
            "column": "pk"
        },
        {
            "column": "val_1"
        },
        {
            "column": "val_2"
        },
        {
            "column": "content"
        }
    ],
    "filters": {
        "op": "AND",
        "args": [
            {
                "op": "AND",
                "args": [
                    {
                        "op": "ISNOTNULL",
                        "column": "content"
                    },
                    {
                        "op": "CMP",
                        "column": "pk",
                        "cond": "GT",
                        "value": 2
                    }
                ]
            },
            {
                "op": "OR",
                "args": [
                    {
                        "op": "CMP",
                        "column": "val_1",
                        "cond": "LE",
                        "value": 30
                    },
                    {
                        "op": "CMP",
                        "column": "val_2",
                        "cond": "GT",
                        "value": 500
                    }
                ]
            }
        ]
    },
    "index": {
        "name": "idx_val",
        "key_columns": [
            "val_1",
            "val_2"
        ],
        "ranges": [
            {
                "lower": {
                    "values": [
                        10,
                        100
                    ],
                    "inclusive": true
                },
                "upper": {
                    "values": [
                        70,
                        700
                    ],
                    "inclusive": false
                }
            }
        ],
        "order": "asc"
    }
}

SCAN API explanation

The request body consists of 4 parts:

limit (required)

Used to specify the maximum number of rows to return.

readColumn (optional)

Used to specify the columns to be returned.

filters (optional)

Used to define filter conditions.

For example, the filter shown above is equivalent to:

(content IS NOT NULL AND pk > 2) AND (val_1 <= 30 OR val_2 > 500)

Filters are defined using a logical-expression format, which allows applying AND / OR / NAND / NOR operations to two or more sub-filters. Sub-filters are specified in the args field:

"filters": {"op": "AND | OR | NAND | NOR", "args": []}

Each entry in the args array represents a sub-filter. A sub-filter can either be a comparison filter or another nested logical filter.

A comparison filter is defined as follows:

{"op": "CMP", "column": "val_1", "cond": "LE", "value": 30}

index (optional)

Used to define an index scan.

The index object consists of the following fields:

name

Specifies the name of the index to be used.

column

Must match the index columns of the specified index. This ensures that the user selects the correct index and provides range values properly. It acts as an additional validation step.

ranges

Specifies the lower and upper bounds for the index scan.

Typically, values for all index columns should be provided. However, partial (prefix) values are supported. For example:

• Providing val_1 = 10 without specifying val_2 is valid, since val_1 is the prefix column of the index.

• Providing val_2 without val_1 is invalid.

order

Indicates whether the returned rows should be ordered by the index, and specifies the order (ASC / DESC).

Example of the response

{
  "data": [
    {
      "pk": 3,
      "val_1": 30,
      "val_2": 300,
      "content": "C5xB5iaZhS1B4LDZtRc1Lp9mN0reX3JpXO5XGCOhh15vsr0q9OnJkZ3FXZs6W9XD"
    },
    {
      "pk": 6,
      "val_1": 60,
      "val_2": 600,
      "content": "NGsifxOOlA70sgoh7n3KJwRve9EKGXbdUwayvsAfZy6rbYlHBqpcxEdJu4rl5Dxn"
    }
  ],
  "rows": 2
}