Setting data types in JSON
Using the mapping object for JSON, you can set data types for fields in your data source.
Available data types
The following data types are available in JSON:
Some of these types can be auto-resolved by the component.
Automatic type resolution
If you omit the field’s type, the component automatically assigns the data type based on the field’s first value. The following data types can be auto-resolved:
The results of the automatic resolution may be unexpected when:
- The first field value is
null. In this case, the field type cannot be resolved automatically. - The field values are not formatted correctly. For example, a number was specified in the hexadecimal form instead of decimal.
- The field type cannot be resolved automatically. For example, time values are processed as numbers if the
"time"type is not explicitly specified. - The first field value is formatted differently from other values. For example, the first value is a number while others are strings.
Learn more about each field data type in the sections below.
Number
This field type is used to store numbers.
Field values can contain digits, -, and + characters. Point . must be used as a decimal separator. Numbers in exponential notation are also supported. Examples of valid values: -20, 2.50, 1.0E+2.
To mark the field as a number, set the type property of the mapping object to "number".
Can be auto-resolved: yes (learn more).
Available aggregations: all.
String
This field type is used to store strings.
Field values must be enclosed in double quotes ". Special characters, such as line breaks or double quotes ", must be escaped using the backslash \. Examples of valid values: "Apple", "\"A-Z\" section".
To mark the field as a string, set the type property of the mapping object to "string".
Can be auto-resolved: yes (learn more).
Available aggregations: Count, Distinct Count.
Date
This field type is used to store dates.
Field values must be specified in one of the following formats:
- ISO 8601. Examples:
"2018-01-10"(date),"2018-01-10T08:14:00"(date and time),"2018-01-10T06:14:00Z"(date and time in UTC).
If the values are specified in this format, the field type can be auto-resolved as the"date"type. - Unix timestamps in milliseconds. Example:
1515535200000(timestamp of the"2018-01-10"date).
If the values are specified in this format, the field type must be set explicitly as a date. - JavaScript Date objects. Can be used only with inline JSON data. Example:
new Date(2018, 1, 10)(creates the"2018-01-10"date).
If the values are specified in this format, the field type can be auto-resolved as the"date"type.
To mark the field as a date, set the type property of the mapping object to one of the following:
"date"– dates are divided into 3 subfields:Year,Month,Day.
Can be auto-resolved: yes, if values are specified in ISO 8601 or as JavaScript Date objects (learn more).
Available aggregations:Count,Distinct Count.
See the example."date string"– dates are displayed as strings in the"dd/MM/yyyy"format. The format can be changed using the datePattern option.
Can be auto-resolved: no (learn more).
Available aggregations:Min,Max,Count,Distinct Count."year/month/day"– dates are represented as the multilevel hierarchy:Year>Month>Day.
Can be auto-resolved: no (learn more).
Available aggregations:Count,Distinct Count.
See the example."year/quarter/month/day"– dates are represented as the multilevel hierarchy:Year>Quarter>Month>Day.
Can be auto-resolved: no (learn more).
Available aggregations:Count,Distinct Count."datetime"– dates are displayed as strings in the"dd/MM/yyyy HH:mm:ss"format. The format can be changed using the dateTimePattern option.
Can be auto-resolved: no (learn more).
Available aggregations:Min,Max,Count,Distinct Count.
See the example.
Time
This field type is used to store time intervals, such as duration.
Field values must be specified as a number of seconds. In the component, values are displayed in the "HH:mm:ss" format. Examples of valid values: 5400 (displayed as "01:30:00" in the component).
To mark the field as time, set the type property of the mapping object to "time".
Can be auto-resolved: no (learn more).
Available aggregations: Min, Max, Count, Distinct Count.
Month
This field type is used to store months. Natural sorting is applied to the field members: from January to December.
Field values must start with a capital letter. Full names and three-letter abbreviations of months are supported. Examples of valid values: "October", "Dec", "May".
To mark the field as months, set the type property of the mapping object to "month".
Can be auto-resolved: no (learn more).
Available aggregations: Count, Distinct Count.
Weekday
This field type is used to store the days of the week. Natural sorting is applied to the field members: from Sunday to Monday.
Field values must start with a capital letter. Full names and three-letter abbreviations of the days of the week are supported. Examples of valid values: "Monday", "Sun", "Friday".
To mark the field as a day of the week, set the type property of the mapping object to "weekday".
Can be auto-resolved: no (learn more).
Available aggregations: Count, Distinct Count.
Level
This field type means that a field is a level of a multilevel hierarchy.
To mark the field as a hierarchy level, set the type property of the mapping object to "level".
Can be auto-resolved: no (learn more).
Available aggregations: none.
Hidden
This field type is used to hide fields from the report.
To mark the field as hidden, set the type property of the mapping object to "hidden".
Can be auto-resolved: no (learn more).
Available aggregations: none.
Note. Another way to hide fields from the report is to omit them in the mapping object.
After formatting your JSON data and setting field data types, learn how to connect to the JSON data.