CSV node access to deeper JSON object properties

Core Development Feature Requests
1

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.

2

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

3

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.

4

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

5

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 ?

6

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?

1 Like