I have data coming in from a feed that is structured as below. The field "last_values" won't flatten. When I choose "Flatten Array" it does not flatten the field. Is the reason it doesn't flatten is because there is no "[" at the beginning of the field and no "]" at the end. If so, how would I get this to function?
{"field4":{"value":5,"created_at":"2023-07-24T19:45:15Z"},"log":{"value":507,"created_at":"2023-07-24T17:03:02Z"},"field3":{"value":181.44,"created_at":"2023-07-24T19:49:59Z"},"field1":{"value":21.4191,"created_at":"2023-07-24T19:55:02Z"},"field2":{"value":55.996,"created_at":"2023-07-24T19:55:02Z"},"field5":{"value":-45,"created_at":"2023-07-24T19:50:06Z"},"wifi":{"value":"ee:55:a8:0a:40:ef,-38;fe:55:a8:0a:40:ef,-38;ee:55:a8:0a:40:bf,-57;fe:55:a8:0a:40:bf,-58;02:18:4a:8c:49:62,-61","created_at":"2023-07-24T17:07:49Z"}}
Accepted Solutions
Absolutely! I put together some samples in Arcade (using the Calculate Field tool) to show what it would look like to grab values for specific fields. You would need to ensure to provide sample values for the fields for the Arcade expression builder to successfully evaluate an expression.
Example 1: Acquire "fn_battery" value from "metadata" stringified JSON block
// Sample workflow to acquire the "fn_battery" field from "metadata" escaped JSON object field
// Sample value for metadata field:
// {\"fn_th\":300,\"fn_light\":900,\"fn_mag\":0,\"fn_mag_int\":0,\"fn_acc_tap1\":0,\"fn_acc_tap2\":0,\"fn_acc_act\":0,\"fn_acc_min\":10,\"fn_bt\":0,\"fn_ext_t\":600,\"fn_battery\":7200,\"fn_dp\":300,\"cg_data_led\":1,\"wifi_mode\":1,\"no_net_fn\":0,\"time_zone\":-5}
// Step 1: Remove the backslashes from the stringified JSON
var slashReplace = Replace($feature.metadata,'\\','');
// Step 2: Convert the non-escaped "proper" JSON to a JSON dictionary, and grab the element of interest
FromJSON(slashReplace)['fn_battery'];
Example 2: Acquire "field4 > value" value from "last_values" stringified JSON block
// Sample workflow to acquire the "field4 > value" field from "last_values" escaped JSON object field
// Sample value for last_values field:
// {\"field4\":{\"value\":5,\"created_at\":\"2023-07-25T11:45:15Z\"},\"log\":{\"value\":507,\"created_at\":\"2023-07-24T17:03:02Z\"},\"field3\":{\"value\":226.4,\"created_at\":\"2023-07-25T12:55:25Z\"},\"field1\":{\"value\":21.5606,\"created_at\":\"2023-07-25T12:55:34Z\"},\"field2\":{\"value\":57.0855,\"created_at\":\"2023-07-25T12:55:34Z\"},\"field5\":{\"value\":-36,\"created_at\":\"2023-07-25T12:50:38Z\"},\"wifi\":{\"value\":\"ee:55:a8:0a:40:ef,-38;fe:55:a8:0a:40:ef,-38;ee:55:a8:0a:40:bf,-57;fe:55:a8:0a:40:bf,-58;02:18:4a:8c:49:62,-61\",\"created_at\":\"2023-07-24T17:07:49Z\"}}
// Step 1: Remove the backslashes from the stringified JSON
var slashReplace = Replace($feature.last_values,'\\','');
// Step 2: Convert the non-escaped "proper" JSON to a JSON dictionary, and grab the element of interest
FromJSON(slashReplace)['field4']['value'];
I then confirmed the success of this expression to an output. Let me know if there are any other questions!
Correct, you do not have an array to flatten there - -
Where as this is an array of iBeacon reports --
So put your JSON into the HTTP Feed Receiver - And you will get a schema that you can then reference in the Analytic -- Be sure to click Flattern, but not Flatern Arrays.
Then in the Analtic, for example, you will have $feature.Field3_value or $feature.Field3_created_at .....
Thanks for the reply and for providing the full sample
Taking a closer look, the fields metadata, status, last_values, vconfig, vpref are "stringified/escaped" JSON and not true JSON. This is the reason that the flattening is not able to break that apart as JSON when evaluating the complete element.
