Skip to main content

Data types

Data types for the JSON DB

Abstract

Data types for the JSON DB

A type is the data type of a field in a table. In a FairCom server, data is stored in fields, records, tables, and databases. A record contains one or more fields. A table contains zero or more records. Each field has a predefined field type.

A FairCom server provides strongly typed fields and flexibly typed fields, which can be freely mixed into any table. Strongly typed fields make queries predictable and fast.

The most common strongly typed fields are NUMERIC, INTEGER, VARCHAR, LVARBINARY, BIT, DATE, TIME, and TIMESTAMP. The remaining strongly typed fields have specialty purposes.

A FairCom server also supports JSON fields, which may flexibly contain any JSON type.

A FairCom table may have up to 2500 fields and zero or more of those fields may have a JSON type.  Each field (or group of fields) may be indexed by one or more indexes with a maximum of 500 indexed fields. One or more individual properties inside a JSON field may also be indexed.

The data types listed in Table 1, “Field types are available in JSON DB API.  All FairCom’s SQL types are supported. Each field in a table and each property in JSON must be one of the listed field types. Each field type may be defined as nullable or not-nullable. The JSON DB API provides response options that return numeric and binary field types as JSON strings.

Note

JSON field types are case-insensitive strings like they are in SQL. In the Table 1, “Field types table, they are shown in uppercase for emphasis and style.

Table 1. Field types

SQL field type

JSON DB API field type

Description

ISAM type number

ISAM type name

JSON type

BIT

"bit"

BIT is a Boolean value of 0 or 1.

8

CT_BOOL

false for 0 and true for 1

TINYINT

"tinyint"

TINYINT is a 1-byte, signed, integer number.

16

CT_CHAR

integer or string

SMALLINT

"smallint"

SMALLINT is a 2-byte, signed, integer number.

33

CT_INT2

integer or string

INTEGER

"integer"

INTEGER is a 4-byte, signed, integer number.

51

CT_INT4

integer or string

BIGINT

"bigint"

BIGINT is an 8-byte, signed, integer number.

231

CT_INT8

integer or string

REAL

"real"

REAL is a 4-byte, IEEE 754, base-two floating point number.

91

CT_SFLOAT

number or string

DOUBLE

FLOAT

"float"

DOUBLE is an 8-byte, IEEE 754, base-two floating point number.

103

CT_DFLOAT

number or string

NUMBER

NUMERIC

DECIMAL

"number"

NUMBER is a 19-byte, signed, fixed-point, base-ten number with 32 digits of precision and user-defined length and scale. Its fixed number of digits and scale are specified when the field is created.

105

CT_NUMBER

number or string

MONEY

"money"

MONEY is a 19-byte, signed, base-ten number with 32 digits of precision and a scale specified as 2 or 4. Its fixed number of digits and scale are specified when the field is created.

105

CT_NUMBER

number or string

DATE

"date"

DATE is a specific day in a specific month and year stored as a 4-byte structure.

75

CT_DATE

string

TIME

"time"

TIME is a specific millisecond in a day stored as a 4-byte structure.

258

CT_TIME_MS

string

TIMESTAMP

"timestamp"

TIMESTAMP is a DATE and TIME combined stored as an 8-byte structure.

257

CT_TIMES_MS

string

CHAR

"char"

CHAR is a fixed length UTF-8 string with padding. Its fixed length and padding are specified when the field is created.

144

CT_FSTRING

string

VARCHAR

"varchar"

VARCHAR is a variable length UTF-8 string. Its maximum length is specified when the field is created.

162

CT_2STRING

string

LVARCHAR

"lvarchar"

LVARCHAR is a variable length UTF-8 string with no specified maximum length. It has a maximum physical maximum length of 2 GB.

170

CT_4STRING

string

BINARY

"binary"

BINARY is a fixed length series of bytes with 0x00 padding. Its length is specified when the field is created.

128

CT_ARRAY

string containing Base64 or Hex

VARBINARY

"varbinary"

VARBINARY is a variable length series of byes. Its maximum length is specified when the field is created.

162

CT_2STRING

string containing Base64 or Hex

LVARBINARY

"lvarbinary"

LVARBINARY is a variable length series of bytes with no specified maximum length. It has a maximum physical maximum length of 2 GB.

170

CT_4STRING

string containing Base64 or Hex

JSON

"json"

JSON is a variable length UTF-8 string that can contain any valid JSON value. Specify a maximum length to store the JSON as a VARCHAR or omit the length to store it as an LVARCHAR, which is up to 2 GB in size.

259

CT_JSON

Any JSON value



Things to know

  • FairCom’s NUMBER and MONEY types allow values that can exceed the numeric limits of JavaScript and some JSON parsers. Thus, the JSON DB API provides the option to embed the numeric values of number types, such as INTEGER, NUMBER, and MONEY in a JSON string. This ensures JavaScript and JSON parsers have no problems parsing JSON containing extra large numbers.

  • The JSON DB API automatically converts CHAR fields to and from JSON's variable-length strings. This makes CHAR fields behave like variable-length strings. You can include spaces when you want to pad its value.

  • SQL pads fixed-length strings with the space character. If the number of characters in a JSON string are less than the fixed length, the database adds the space character to the end of the string until it fills the fixed length. The following SQL query demonstrates this padding by returning quotes surrounding the value of a fixed-length field named "mychar".

    SELECT '"' || mychar || '"' from mytable;
  • A maximum length must be assigned to all variable-length fields. This length is used by FairCom's indexes and by SQL.

    • If a value exceeds the length, the operation returns an error.

    • You can set a VARCHAR string to its maximum possible length of 65,500 bytes as long as you do not index the field. Notice that the maximum length is bytes – not characters – because a UTF-8 character may contain multiple bytes.

  • FairCom indexes the maximum length of strings; thus, do not make a string larger than necessary.

    • The index copies a variable-length string into a fixed-length buffer that is the maximum length of the string. The buffer is initialized with spaces. Then the index uses run length encoding to compress repeated characters.

    • This can cause side effects when a string contains trailing spaces:

      • Keys are identical when the only difference is the number of trailing spaces.

      • The number of trailing spaces has no impact on key comparisons.

      • A key being added to a unique index will fail if the only difference between it and an existing key is the number of trailing spaces.

  • In the JSON DB API, all binary values are represented as a sting containing an encoded as base64 or hexadecimal value.

    • When receiving a property containing a binary value, the JSON DB API converts the base64 or hex encoded value into a binary value and stores it in the field as binary data.

    • When returning data, it takes the binary value out of a field, encodes it as a base64 or hex string and puts it in the JSON property.

      Note

      Encoding a binary value into a variable-length JSON string creates a string that is typically 1.3 to 2 times larger than the field's binary length. This is expected and is not an issue for JSON or the JSON DB API.