Handling JSON data in PhixFlow often requires adding escape characters or removing characters that PhixFlow cannot recognise.
| Tip |
|---|
Some databases do not recognise 4-bit characters, such as emojis; see Database for recommended database configurations.
|
Escaping Invalid Characters
If you are outputting stream data into JSON format, the data can include special characters that JSON will not accept. The following macro escapes these invalid characters.
| Code Block | ||
|---|---|---|
| ||
do(
$value = $args[1],
$value = replaceAll( $value , "\b","\\\\b"),
$value = replaceAll( $value , "\f","\\\\f"),
$value = replaceAll( $value , "\t","\\\\t"),
$value = replaceAll( $value , "\r","\\\\r"),
$value = replaceAll( $value , "\n","\\\\n"),
$value = replaceAll( $value , "\\\\","\\\\\\\\"),
$value = replaceAll( $value , '"','\\\\"')
)
|
Validating Data Imported Into PhixFlow
JSON data, like any imported data format, can include characters that cannot be written to a database that is using the UTF-8 character set. For example, 4-bit Emojis are not supported by UTF-8.
...
PhixFlow stores its data in a database. The recommended database configurations, described on the Database page, mean your instance of PhixFlow can store UTF8 characters in its database. However, the different databases handle characters from the extended set differently.
| Database | Behaviour |
|---|---|
| Oracle | Stores extended characters correctly. |
| SQL Server | Discards or converts extended characters. |
| MariaDB | Fails to store extended characters because it only recognises a sub-set of UTF8 . This can cause problems if data contains 4-bit characters, such as emojis. |
If you are loading data from another database, a file or emails, you will need to either escape or remove any invalid characters before writing to PhixFlow running on MariaDB.
For information about how to excape characters, see Text Expressions and Escape Characters.
Removing Characters
The following regex lists the characters expected in a JSON file and removes invalid characters from it. You could adapt this regex to validate an data that you want to load into PhixFlow.
| Code Block | ||
|---|---|---|
| ||
replaceAll(JSON Code,"[^\\p{Space}0-9A-Za-z!:\\\"%&\\[*()\\],-/_\\\\{}\\.]","") |
The backslash character escapes special characters such as [] , . and \
See Also
...