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
BIGINT	"bigint"	`BIGINT` is an 8-byte, signed, integer number.	231	CT_INT8	integer or 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
BIT	"bit"	`BIT` is a Boolean value of `0` or `1`.	8	CT_BOOL	`false` for 0 and `true` for 1
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
DATE	"date"	`DATE` is a specific day in a specific month and year stored as a 4-byte structure.	75	CT_DATE	string
DOUBLE `FLOAT`	"float"	`DOUBLE` is an 8-byte, IEEE 754, base-two floating point number.	103	CT_DFLOAT	number or string
INTEGER	"integer"	`INTEGER` is a 4-byte, signed, integer number.	51	CT_INT4	integer or string
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
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
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
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
NCHAR	"nchar"	`NCHAR` is a fixed length UTF-16 string with padding. Its fixed length is specified when the field is created.	177	CT_FUNICODE	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
NVARCHAR	"nvarchar"	`NVARCHAR` is a variable length UTF-16 string. Its maximum length is specified when the field is created.	193	CT_2UNICODE	string
REAL	"real"	`REAL` is a 4-byte, IEEE 754, base-two floating point number.	91	CT_SFLOAT	number or string
SMALLINT	"smallint"	`SMALLINT` is a 2-byte, signed, integer number.	33	CT_INT2	integer or 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
TINYINT	"tinyint"	`TINYINT` is a 1-byte, signed, integer number.	16	CT_CHAR	integer or string
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
VARCHAR	"varchar"	`VARCHAR` is a variable length UTF-8 string. Its maximum length is specified when the field is created.	162	CT_2STRING	string
VARIANT	"variant"

Essential information

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.

UTF-8UTF-8ODBCODBCODBCbytes