# CSV node access to deeper JSON object properties

**URL:** <https://discourse.nodered.org/t/csv-node-access-to-deeper-json-object-properties/18690>\
**Category:** Feature Requests\
**Created:** [2 December 2019 13:03 UTC](https://discourse.nodered.org/t/csv-node-access-to-deeper-json-object-properties/18690 "2019-12-02T13:03:55Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![johngouk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/johngouk/32/14790_2.png) [@johngouk](https://discourse.nodered.org/u/johngouk)\
**Post date:** [2 December 2019 13:03 UTC](https://discourse.nodered.org/t/csv-node-access-to-deeper-json-object-properties/18690/1 "2019-12-02T13:03:55Z")

</div>

I have JSON data with sub-structures which I'd like to convert to CSV e.g.

{"Time":"2019-12-01T18:12:55"  
,"SHT3X\_0x44”:{“Temperature":20.6,"Humidity":50.8}  
,"SHT3X\_0x45":{"Temperature":20.6,"Humidity":50.8}  
,"TempUnit":"C"}

As far as I can see, the existing CSV\<-\>JSON node doesn't let me access e.g. the SHT3X\_0x44.Temperature property - I've tried it and I get undefined. I checked the current code and it assumes the properties specified in the template string are all top-level properties. Of course, there'd be some discussion about the appropriate headings for such substructures, and what would happen for CSV-\>JSON conversion... :-). I have some thoughts on an implementation.

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [2 December 2019 13:05 UTC](https://discourse.nodered.org/t/csv-node-access-to-deeper-json-object-properties/18690/2 "2019-12-02T13:05:26Z")

</div>

What is the CSV representation of that example you are trying to achieve?

---

<div class="post-metadata">

**Author:** ![johngouk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/johngouk/32/14790_2.png) [@johngouk](https://discourse.nodered.org/u/johngouk)\
**Post date:** [2 December 2019 17:03 UTC](https://discourse.nodered.org/t/csv-node-access-to-deeper-json-object-properties/18690/3 "2019-12-02T17:03:04Z")

</div>

Haha, good question! I basically want to flatten it, so

2019-12-01T18:12:55,20.6,50.8,20.6,50.8

would be fine. Then it's easy to import and do graphs etc. in one's favourite spreadsheet tool.

And yes, I could do that for this example pretty trivially with a JS function, but hey... it seems like a useful extension to be able to "see into" deeper JSON structures.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [2 December 2019 18:01 UTC](https://discourse.nodered.org/t/csv-node-access-to-deeper-json-object-properties/18690/4 "2019-12-02T18:01:18Z")

</div>

[quote="johngouk, post:1, topic:18690"]  
Note: you have some smart quotes in your JSON data

I would use a change node with JSONata so for the first two items you would use this  
`Time&","&SHT3X_0x44.Temperature`  
which gives you  
`"2019-12-01T18:12:55,20.6"`  
and you should be able to deduce how to complete the JSONata expression.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [2 December 2019 22:33 UTC](https://discourse.nodered.org/t/csv-node-access-to-deeper-json-object-properties/18690/5 "2019-12-02T22:33:53Z")

</div>

So right now we do accept a "template" that can pick properties out of the object to arrange the string in the correct order. But as pointed out it currently only accepts depth one...

technically this is because we use `RED.util.getMessageProperty(msg,"payload['"+node.template[t]+"']")` which then fails as (in this case) could be `msg.payload["SHT3X_0x44.Temperature"]` which an incorrect way to reference a property...

However we could detect the . in the template and then instead use `msg.payload.SHT3X_0x44.Temperature` which would then be correct - but then you couldn't have a column name with a . in it.

Thoughts anyone ?

---

<div class="post-metadata">

**Author:** ![johngouk](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/johngouk/32/14790_2.png) [@johngouk](https://discourse.nodered.org/u/johngouk)\
**Post date:** [4 December 2019 16:22 UTC](https://discourse.nodered.org/t/csv-node-access-to-deeper-json-object-properties/18690/6 "2019-12-04T16:22:48Z")

</div>

Mmm, yes, indeed.  
I wasn't sure about the acceptability of "." in JSON property names, but it is indeed acceptable. However, to access properties with names containing "." only the `object["propertyWith.Embedded"].subLevelProperty` form works, otherwise JS gets confused.

IFF this is worth doing, then I guess the template format could be extended to accept property specifications similarly to JS, examples as follows:

- `level1` - as before
- `level1.level2.level3` - specifies value at level3, of 3 level hierarchy
- `["level1.level2"].level3` - specifies value of level3, of 2 level hierarchy
- `level1["level2.level3"]` - specified value of "level2.level3" of 2 level hierarchy
- `firstItem,["embedded.item"].level2,level1.level2` - 3 values: a 1st level item, and two 2nd level items

That doesn't break existing working templates, allows the specification of both JSON-\>CSV and CSV-\>JSON operations using the same syntax, and follows JS syntax more or less.

Of course, there is then the issue of what about "[" and "]" in property names, so

`Time":"2019-12-01T19:04:35","SHT3X].0x44":{"Temperature":21.6,"Humidity":49.1},"TempUnit":"C","topic":"tele/sonoff/0371/SENSOR"}`

works, provided the accessing code is of the form

`temperature = msg.payload["SHT3X].0x44"].Temperature;`

which presumably works because the offending ] is in a string, which the parser accepts.

But I'm not sure this isn't getting too complicated...

Thoughts, anyone? What did I miss?