|
|
|
JSON natively supports six data types: string, number, Boolean, null, object, and array. A JSON number is a Double Binary Floating Point Number. JSON does not natively support date, time, timestamp (date and time combined), binary data, etc. In JSON documents, it is common to use native data types to represent non-native types. This requires external metadata to interpret the non-native type.
Example 1: A date and/or time can be embedded in a JSON string, such as "12/01/2002" and "8:15:30 PM". A date and/or time can also be represented as a JSON number. This number typically represents a date and/or time based on days or seconds that are offset from a starting point in time, such as 18256.33.
Example 2: A floating point number in a JSON property may be assigned to an Integer field and c-tree needs to know how to truncate or round the number.
Example 3: There are multiple ways to represent binary values in strings, such as embedding base64 or hexadecimal encoded values in a string, such as "SGVsbG8gV29ybGQ=" and "FF003D". A binary value can also be represented as a JSON array of numbers, such as [0,1,99,100,255].
Format Properties for Non-Native Data Types
c-tree provides “format” properties to specify how it should interpret non-native data types stored in JSON. You can specify “format” properties in both the CreatePersistenceTopic and AlterPersistenceTopic commands. You can define them using the "default___" properties, which apply a default format to all fields of the specified type in that Persistence Topic. You can also change the format for each individual field using the "propertyFormat" property, which gives you precise control over how c-tree interprets and processes each field of a data message.
The table below contains a list of data types in c-tree along with the “format” strings you can use to tell c-tree how to transform a JSON data type into a native c-tree data type.
FairCom Database Engine Conversion Format Reference
Target |
JSON Source Type |
JSON |
JSON |
Notes |
|---|---|---|---|---|
SQL BIT |
JSON String |
0 Value 0 -0 0.0 -0.0 false False fAlse FALSE "" (empty string) null [] {} All other values are interpreted as 1. |
"BOOLEAN" |
Default Interprets JSON values as a 0 or a 1 value in a SQL BIT data type. All values not interpreted as 0 are interpreted as 1. Logically 0 represents False and 1 represents True.
|
SQL BINARY VARBINARY LVARBINARY |
JSON Array of Numbers |
The bytes for “Hello”: [ 72, 101, 108, 108, 111 ]
Nullable Values: null unknown [null] [unknown]
Empty Binary Value: [] |
"BYTE_ARRAY" |
Default Each number in the array must be between 0 and 255 because it represents 1 byte. If SQL NULL is allowed, then the list of nullable values are interpreted as null; otherwise they are interpreted as an empty binary value.
|
SQL BINARY VARBINARY LVARBINARY |
JSON String |
The bytes for “Hello”: "SGVsbG8="
Nullable Values: null unknown "null" "unknown"
Empty Binary Value: "" |
"BASE64" |
Interprets characters embedded in a string as Base64 encoded bytes. If SQL NULL is allowed, then the list of nullable values are interpreted as null; otherwise they are interpreted as an empty binary value. |
SQL BINARY VARBINARY LVARBINARY
|
JSON String |
The bytes for “Hello”: "48656C6C6F” "x48656C6C6F” "0x48656C6C6F” "�”
Nullable Values: null unknown "null" "unknown"
Empty Binary Value: "" |
"HEX" |
Interprets characters embedded in a string as HEX numbers. All leading and trailing non hex digits are ignored. The leading characters “x” or “0x” or “&#x” are an optional indicator that the value is hexadecimal. If SQL NULL is allowed, then the list of nullable values are interpreted as null; otherwise they are interpreted as an empty binary value. |
SQL TINYINT SMALLINT INTEGER BIGINT
|
JSON Number or String or Boolean or null or unknown |
3.14 "3.14" "3,14" "3 14" "+3.14" "-3.14"
Nullable Values: null "null" unknown "unknown" NaN "NaN" infinity "Infinity" -infinity "-infinity" "" [] {} [{}]
Zero Value: 0 0.0 +0 "+0" -0 "-0" false |
"TRUNCATE" |
"TRUNCATE" is the default conversion where 3.14 becomes 3 and -3.14 becomes -3
|
SQL DATE |
JSON String or Boolean or Number or null or unknown |
"12/31/1999" "12-31-1999" "12.31.1999" Nullable Values: true false null "null" unknown "unknown" "" [] {} [{}] NaN "NaN" infinity "Infinity" -infinity "-infinity" "0" "+0" "-0" Any Number Any String that cannot be interpreted as a date. |
"MM.DD.YY" |
If SQL NULL is allowed, then the list of nullable values are interpreted as null; otherwise they are interpreted as the highest possible DATE value, which is 4,294,967,296 days past Feb 28, 1700 or the value 0xFFFFFFFF in an unsigned long. This rule applies to all SQL DATE types where the format expects a string. |
SQL Date |
JSON String |
"12/01/2002" "12-01-2002" |
"MM.DD.CCYY" |
This is the default date format. |
SQL DATE |
JSON String |
"1/12/2002" "01_12+2002" |
"DD.MM.CCYY" |
|
SQL DATE |
JSON String |
"01/12/02" "01 12 02" |
"DD.MM.YY" |
|
SQL DATE |
JSON String |
"20021201" |
"CCYYMMDD" |
|
SQL DATE |
JSON String |
"021201" |
"YYMMDD" |
|
SQL DATE |
JSON String |
"011202" |
"DDMMYY" |
|
SQL DATE |
JSON String |
"01122002" |
"DDMMYYCC" |
|
SQL DATE |
JSON String |
"120102" |
"MMDDYY" |
|
SQL DATE |
JSON String |
"12012002" |
"MMDDYYCC" |
|
SQL TIME |
JSON String |
"8:15 pm" "08 15 P" Nullable Values: true false null "null" unknown "unknown" "" [] {} [{}] NaN "NaN" infinity "Infinity" -infinity "-infinity" "0" "+0" "-0" Any Number Malformed String |
"hh.mm.am/pm" |
Default If SQL NULL is allowed, then the list of nullable values are interpreted as null; otherwise they are interpreted as the highest possible TIME value, which is 4,294,967,296 milliseconds past midnight or the value 0xFFFFFFFF in an unsigned long (i.e. 49 days past midnight). This rule applies to all SQL TIME types where the format expects a string. |
SQL Time |
JSON String |
"8:15:30 PM" "08.15.30 pm" |
"hh.mm.ss.am/pm" |
Default |
SQL TIMESTAMP |
JSON String |
"2002-12-01 08:15:30-05:00" "2002-12-01|08:15:30Z" "2002-12-01_08:15:30Z" "2002-12-01T08:15:30Z" Nullable Values: true false null "null" unknown "unknown" "" [] {} [{}] NaN "NaN" infinity "Infinity" -infinity "-infinity" "0" "+0" "-0" Any Number Malformed String |
"YMD | HMS" |
Any date and time format can be combined together to form a timestamp format. They are separated by the pipe character. If SQL NULL is allowed, then the list of nullable values are interpreted as null; otherwise they are interpreted as the highest possible TIMESTAMP value, which is the max safe integer in a double (9,007,199,254,740,991). Another possibility could be NaN. This rule applies to all SQL TIMESTAMP types where the format expects a string. |
SQL TIMESTAMP |
JSON String |
"12/01/2002 2:15:45 pm" |
"MM.DD.CCYY | hh.mm.ss.am/pm" |
Another of many possible date time combinations. |
SQL TIMESTAMP |
JSON String |
"200212011315" |
"CCYYMMDD|hhmm" |
Another of many possibilities. |
