Map JSON Numbers to SQL Integer and Numeric Columns

Numeric Type Mapping

JSON has a single number type that covers both integers and floating-point values. SQL, however, provides a rich set of numeric types with different ranges, precision, and storage costs.

Integer Detection

The converter inspects each numeric value to determine whether it is an integer:

{
  "id": 42,
  "quantity": 1000,
  "total_cents": 29999,
  "rating": 4.5,
  "latitude": 35.6762
}

CREATE TABLE products (
  id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  total_cents INTEGER NOT NULL,
  rating DECIMAL(10, 2) NOT NULL,
  latitude DOUBLE PRECISION NOT NULL
);

Choosing the Right Integer Type

SQL type	Range	Use when
SMALLINT	-32,768 to 32,767	Status codes, small counters
INTEGER	-2.1B to 2.1B	Most IDs, quantities
BIGINT	-9.2E18 to 9.2E18	Timestamps in ms, large IDs

The converter defaults to INTEGER for whole numbers. If the sample value exceeds the INTEGER range (roughly 2.1 billion), it upgrades to BIGINT. You can also configure the tool to always use BIGINT for ID columns.

Floating-Point Precision

For decimal values, the converter chooses between:

• DECIMAL(p, s) — exact precision, ideal for money and measurements.
• DOUBLE PRECISION — approximate but fast, suitable for scientific data and coordinates.

Money Columns

A common pattern is to store money as integer cents (e.g., 29999 = $299.99) to avoid floating-point rounding errors. The converter detects column names containing price, amount, cost, or cents and may suggest DECIMAL or an integer-cents approach in the comments.

Overflow Protection

Always choose a type with headroom. If your current max ID is 50,000, using INTEGER is safe — but if the table will grow to billions of rows, start with BIGINT to avoid a painful migration later.