JSON schemas for Redshift datatypes

This blog contains JSON schemas for the all the data types supported by Amazon Redshift.

We supply two schemas for each numeric type, since you may want to send in numeric types as JSON strings rather than JSON numbers.

1. SMALLINT
2. INTEGER
3. BIGINT
4. DECIMAL
5. REAL
6. DOUBLE PRECISION
7. BOOLEAN
8. CHAR
9. VARCHAR
10. DATE
11. TIMESTAMP

SMALLINT

The schema for passing the value in as a number:

{ "type": "integer" } 

And the schema for passing the value in as a string. The regex will validate any string consisting only of digits:

{ "type": "string", "pattern": "^[0-9]+$" } 

INTEGER

The schema for passing the value in as a number:

{ "type": "integer" } 

And the schema for passing the value in as a string. The regex will validate any string consisting only of digits:

{ "type": "string", "pattern": "^[0-9]+$" } 

BIGINT

The schema for passing the value in as a number:

{ "type": "integer" } 

And the schema for passing the value in as a string. The regex will validate any string consisting only of digits:

{ "type": "string", "pattern": "^[0-9]+$" } 

DECIMAL

The schema for passing the value in as a number:

{ "type": "number" } 

And the schema for passing the value in as a string. The regex will validate a string of at least one digit, possibly followed by a period and exactly two digits:

{ "type": "string", "pattern": "^[0-9]+(\.[0-9]{2})?$" } 

The schema below is equivalent, except that it also allows empty strings:

{ "type": "string", "pattern": "^$|^[0-9]+(\.[0-9]{2})?$" } 

REAL

The schema for passing the value in as a number:

{ "type": "number" } 

And the schema for passing the value in as a string. The regex will validate any string consisting of at least one digit, possibly followed by a period and at least one digit:

{ "type": "string", "pattern": "^[0-9]+(\.[0-9]+)?$" } 

DOUBLE PRECISION

The schema for passing the value in as a number:

{ "type": "number" } 

And the schema for passing the value in as a string. The regex will validate any string consisting of at least one digit, possibly followed by a period and at least one digit:

{ "type": "string", "pattern": "^[0-9]+(\.[0-9]+)?$" } 

BOOLEAN

{ "type": "boolean" } 

CHAR

This JSON schema is for a char of exactly «LENGTH» characters.

{ "type": "string", "minLength": <<LENGTH>>, "maxLength": <<LENGTH>> } 

VARCHAR

This JSON schema is for a varchar with at most «LENGTH» characters.

{ "type": "string", "maxLength": <<LENGTH>> } 

DATE

This JSON schema uses the regex for Redshift’s default YYYY-MM-DD format.

{ "type": "string", "pattern": "^[0-9]{4}-[0-9]{2}-[0-9]{2}$" } 

TIMESTAMP

This is the recommended way to define a timestsamp field in JSON schema:

{ "type": "string", "format": "date-time" } 

You can instead use the following regex if you wish:

{ "type": "string", "pattern": "^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$" }