nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Flatten Array Field Trouble...

ArmstKP

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"}}

Find more posts tagged with

Accepted answers

PeterNasuti

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!

All comments

ArmstKP

@PeterNasuti Thank you for looking into it. I guess I will have to look into using Arcade and extracting the values when using the Map Fields tool...

PeterNasuti

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.

ArmstKP

@PeterNasuti I did try to just flatten and to use "last_values" as the root node, but that did not work either.

Here is my full JSON:

{"result":"success","server_time":"2023-07-25T12:57:51Z","channels":[{"channel_id":"66666","field1":"Temperature","field2":"Humidity","field3":"Light","field4":"Voltage","field5":"WIFI RSSI","field6":"","field7":"","field8":"External Temperature Probe","field9":"","field10":"","field11":null,"field12":null,"field13":null,"field14":null,"field15":null,"latitude":"44.93380593209362","longitude":"-93.29240328337887","name":"South Side","public_flag":"false","tags":null,"url":null,"metadata":"{\"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}","description":null,"traffic_out":"365892","traffic_in":"684874","status":"{\"ssid\":\"MPRB-Secured\",\"status\":\"mac=ec:94:cb:7f:a1:b4,usb=1,ota_status=0,ota_errcode=0,ota_sec=0\",\"usb\":\"1\",\"mac\":\"ec:94:cb:7f:a1:b4\"}","timezone":null,"created_at":"2023-06-20T18:36:36Z","updated_at":"2023-07-05T18:33:58Z","usage":"4246875","last_entry_id":"22425","last_entry_date":"2023-07-25T12:55:41Z","product_id":"ubibot-ws1a","device_id":"0f006003a36a04a35c41ab64cc9f40148382e1bb","channel_icon":null,"last_ip":"73.228.129.155","attached_at":"2023-06-20T18:36:36Z","firmware":"ws1a_v2.2.3","full_dump":"0","activated_at":"1687294250","serial":"K775***WS1A","mac_address":"ec:94:cb:7f:a1:b4","full_dump_limit":"3","cali":null,"size_out":"1073741824","size_storage":"209715200","plan_code":"ubibot_free","plan_start":"2023-06-20T18:36:36Z","plan_end":null,"bill_start":"2023-07-20T18:36:36Z","bill_end":"2023-08-19T18:36:36Z","last_values":"{\"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\"}}","vconfig":"{\"field1\":{\"h\":\"0\",\"u\":\"1\"},\"field2\":{\"h\":\"0\",\"u\":\"3\"},\"field3\":{\"h\":\"0\",\"u\":\"4\"},\"field4\":{\"h\":\"0\",\"u\":\"5\"},\"field5\":{\"h\":\"0\",\"u\":\"6\"},\"field8\":{\"h\":\"0\",\"u\":\"14\"}}","vpref":"{\"fields\":[{\"key\":\"field1\",\"index\":0,\"visible\":true},{\"key\":\"field2\",\"index\":1,\"visible\":true},{\"key\":\"field3\",\"index\":2,\"visible\":true},{\"key\":\"field4\",\"index\":3,\"visible\":false},{\"key\":\"field5\",\"index\":4,\"visible\":false},{\"key\":\"field8\",\"index\":5}],\"v\":3}","sensors":null,"sensors_mapping":"","battery":null,"vpref_from":"owner","net":"1","c_icon_base":null,"full_serial":"K7750M8WS1A","triggering_rules":null}],"virtual_fields":[]}

PeterNasuti

@ArmstKP For this case you would be clicking "Flatten" instead of "Flatten Arrays". Flatten arrays is only to flatten items contained in square brackets [/] as an array that actually contains data values. Flatten is to flatten {/} objects.

Additionally, you may choose to specify an element such as "last_values" as a root node if you want to "drill down" into a JSON set to get a deeper element.

If you have further questions feel free to log a support case or reach out to me over Esri Community direct message or on this thread. It would also help to be able to provide a full sample of your JSON - you could of course replace values with fake values of the same type.

JeffSilberberg

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 .....

JeffSilberberg

Correct, you do not have an array to flatten there - -

Where as this is an array of iBeacon reports --

{ "beacons": [

{

"type": "iBeacon",

"major": 3838,

"minor": 4949,

"time": 2037,

"txpwr": -59,

"rssi": -71

{

"type": "iBeacon",

"major": 3838,

"minor": 4949,

"time": 2037,

"txpwr": -59,

"rssi": -71

}

]

}

ArmstKP

@JeffSilberberg This is what I receive back from jsoncrack.

JeffSilberberg

I pasted the data as a picture not as text so I can't try it for you -- But go to JSON crack and paste your JSON structure into the left panel after clearing out the sample JSON already in there. This will let you look at what you are asking Velocity to parse at a more detailed level.

But yes, an array is defined as between two square brackets. []

https://jsoncrack.com/editor

-------------------

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.