|
|
|
FairCom Edge can automatically extract data from a JSON document sent over MQTT. This section shows how to extract data using propertyPath and propertyFormat.
propertyPath
Use the propertyPath feature to automatically extract the value of any JSON property located at any location in a JSON document and put it into a field in a database table.
Examples
"grandparent.parent.child" extracts the value "me" from the JSON document below:
{
"grandparent":
{
"parent":
{
"child": "me"
}
}
}
"person.names[0].firstName" extracts "John" from the JSON document below:
{
"person":
{
"names":
[
{ "firstName": "John", "lastName": "Taormina" },
{ "firstName": "Mike", "lastName": "Bowers" }
]
}
}
propertyFormat
Use the propertyFormat feature to automatically convert the data type in a specific JSON property into a field in a database table.
This table shows the default way that FairCom Edge converts JSON values into field values.
You can also set the default way FairCom Edge should convert a JSON value into a field value.
Examples
Automatically convert date values embedded in JSON strings into native database data types.
Convert a date embedded in a JSON string property into a database date type in a field, such as converting { "myDate": "12/01/2002" } into 2002-12-01.
You can convert a variety of date and time formats.
Automatically convert binary values embedded in JSON into a single binary value in a field.
Convert a JSON property containing an array of integers into a binary field value, such as converting { "myImageAsArrayOfByteValues": [0,1,99,100,255] } into 0x00016364FF.
Convert a JSON string property containing an embedded HEX-encoded binary value into a binary field value, such as converting { "myImageAsHexValue": "0xFF003D" } into 0xFF003D.
Convert a JSON string property containing an embedded Base64 encoded binary value into a binary field value, such as converting { "myImageAsBase64Value": "SGVsbG8gV29ybGQ=" } into 0xFF003D.
Automatically truncate floating point numbers in a JSON number property into an integer field value, such as converting { "myNumber": 50.0021 } into 50.
If the database field type is a NUMBER, MONEY, REAL, or DOUBLE, it preserves JSON floating point values without truncating them.
fieldFormat
Use the fieldFormat feature to transform nested JSON data into native database string values.
Use "stringify" to convert all or some of a JSON document into a string stored in a database field.
This is the default behavior when you don't map any JSON properties to database fields: FairCom Edge converts the entire JSON document into a string and stores it in a JSON field in the database. This is the easiest and most flexible approach, but downstream solutions may or may not be able to handle JSON in strings. For example, ThingWorx works best with simple field types, such as number, date, and boolean.
Use propertyPath to extract a sub-document out of the JSON document and use fieldFormat: "stringify" to store that sub-document into a database field.
