Node-RED

How to store data in JSON file in Node-RED

General
#4

I would actually disagree here. As long as you need the whole thing available to Node-RED, a JSON file loaded to memory is much easier to work with and avoids the additional complexity of a database.

Only if you need to query or aggregate data would a SQL database be a better bet.

As this is "just" a lookup table referenced directly, the JSON is ideal.

Absolutely, this is much simpler than messing with file read/write's. It just works. I'm using it a lot and gradually migrating any older solutions to this. You can back up the resulting files as well.

#5

It is possibly arguable either way in this case. Requirements such as "count the number of light circuits in a room" suggest a database approach to me.
Also for the Scenes, looking up the available scenes for a particular room, adding and deleting scenes, and so on might well be easier with a db.
To a great extent it is down to which approach you are more comfortable with.

#6

As always :wink:

Yes, given the data shown, this might be correct. Of course, the advantage of using JSON is that you can simply adjust to need. So that you could easily just have another global variable that tracks that info too. Or group the existing JSON by type and then you can just count the number of entries.

Personally, I tend to use a slightly different structure for my in-memory variables like this:

{
    "Light 1": {
        "device":"Light 1",
        "zone":"bedroom1",
        "name":"Wall Light"
        "fitting":"wall",
        "colour":"warmwhite"
    },
    "Light 2": {
        "device":"Light 2", 
        "zone":"bedroom1",
        "name":"Ceiling Light",
        "fitting":"ceiling",
        "colour":"RGB"
    }
}

So creating as an object. This makes it easier to relate the data to other objects - I then tend to convert to an array if I need to. Of course, for some processing, the array format is better.

Indeed, I'm currently writing a heating dashboard using uibuilder with VueJS and bootstrap-vue. Bootstrap-vue has a nice table component but it needs its data as an array. As the data is only updated once per minute, I simply convert it before sending. Merging together several variables is much easier as an object.

#7

Missing a comma??? :grin:

#8

Thanks for your help so far.

In @TotallyInformation’s suggestion for a json file, does anyone know how it would be possible to count the number of items (eg with a specific property like fittingtype=wall) in a JSON file?

With a simple loop that reads all values and compares and counts?

#9

if the data was stored as an array of objects like:

{
    "payload": [{
        
            "device": "Light 1",
            "zone": "bedroom1",
            "name": "Wall Light",
            "fitting": "wall",
            "colour": "warmwhite"
        },
         {
            "device": "Light 2",
            "zone": "bedroom1",
            "name": "Ceiling Light",
            "fitting": "ceiling",
            "colour": "RGB"
        }]
}

then you could use a JSONata expression like: $count(payload[fitting="wall"]) which would give the result of 1 while this $count(payload[zone ="bedroom1"]) would give you 2

2 Likes
#10

Or in JavaScript:

msg.payload.filter(function(item){return item.fitting === 'wall'}).length;
or
msg.payload.filter(function(item){return item.zone === 'bedroom1'}).length;

Not as neat as @zenofmud's expression but usable in a function node that might be doing a bunch of other stuff.

#11

in sql one could do a "group by" without knowing any zone/fitting names - is this also possible in jsonata?

#12

Thanks @zenofmud and @drmibell for the practical tips. Most useful.

Out of interest @drmibell, re your earlier post where you suggested a JSON file. That's what I originally envisaged. Sorry if this is a basic question, but would you literally create the file with a text editor to start with? If you wanted to update it programmatically (e.g. one item might be a stored lighting scene, and there may be an option on a control panel to overwrite an existing scene with new settings) - I'm guessing there's a good way to do that?

Or do we get into possible problems with concurrency? Perhaps it may be better to store all of the json in persistent storage? Hopefully I didn't misunderstand your point about using JSON file for static information about devices and a global variable for state information...

#13

Yes.

Because scenes typically involve more than one device, I generally use a different format and a separate JSON file for them.

Easy, I think, although I haven't actually done it. Something like this:

  • At startup, read the JSON file(s), and convert to object(s), and save in context (presumably persistent).
  • Use the objects as needed for sending commands, switching scenes, etc., and keep the current state of the system in a persistent context.
  • Modify the devices or scenes objects as you like. If you want to make the changes permanent, convert the objects to JSON and write them back out to their appropriate files.

This blurs my distinction between "static" and "dynamic," since you can change your definitions of the scenes or device configuration on the fly, but that's a perfectly reasonable thing to do.

#14

"Filtering" in JSONata is what Paul is demonstrating with this part of the expression -- payload[fitting="wall"]

but since he wrapped it inside the $count(...array...) function, the result is the number "1".
If you remove that you will see the output is just an array of the matching object(s) themselves. If by grouping you also mean "aggregating" or reducing (turning N objects into 1), then yes that is also possible using functions like these.

The best way to see this in action is to copy your data and paste it into the left-hand window on the try.jsonata.org site... then, as you change the expression in the upper-right window, you can see the results (or errors) change in the lower-right.

If you've not read the docs on jsonata.org I would hightly recommend it -- it will take you only an afternoon to get proficient with the syntax, and can save hours of JS coding, if you do much restructuring of data.

#15

No-one likes a smart-Alec! :slight_smile:
Yes, a typo

1 Like
#16

Getting over-complex with the JSON there folk!!

Object.keys(myObj).length

should do the job, no need for anything complex.

1 Like
#17

Maybe I'm missing something. I would expect Object.keys(myObj).length to give the number of keys in myObj. So I'm not sure how this solves the problem(s) here.

#18

Okay - so to extend this subject, I'm thinking about designing some form of schema that can support different types of light to be included in a "scene".

As I see it, the different types of light you might want to control would be:

  • Single colour (e.g. 0-255) - could also apply to e.g. rollerblinds
  • RGB
  • RGBW
  • RGBWW (I think these are RGB LED strips with TWO additional whites, cool white and warm white)

How about an internal lookup JSON file, which specifies a mapping between colour names and values (e.g. red=255,0,0; salmon=250,128,114... etc)

So the scene definitions might be stored like this. Is this a vaguely sensible idea? Is there a better way?

Should I work towards creating my own node type to handle this stuff? Could it be useful for other people? (I've never created a node before, would be interested in giving it a go...)

[
  {
    "lightingzone":"3F_Bedroom",
    "scene":"0",
    "scenename":"All Off",
    "ceiling":"0",
    "wall":"0,0,0",
    "floor":"0,0,0,0",
    "rollerblind":""
  },
  {
    "lightingzone":"3F_Bedroom",
    "scene":"1",
    "scenename":"Bedtime",
    "ceiling":"5",
    "wall":"10,10,10",
    "floor":"10,10,10,10",
    "rollerblind":"255"
  },
  {
    "lightingzone":"3F_Bedroom",
    "scene":"2",
    "scenename":"Reading",
    "ceiling":"0",
    "wall":"150,150,0",
    "floor":"0,255,0,255",
    "rollerblind":"0"
  },
  {
    "lightingzone":"3F_Bedroom",
    "scene":"3",
    "scenename":"Work",
    "ceiling":"255",
    "wall":"150,150,0",
    "floor":"0,255,0,255",
    "rollerblind":"150"
  },
  {
    "lightingzone":"3F_Bedroom",
    "scene":"4",
    "scenename":"Getting Dressed",
    "ceiling":"255",
    "wall":"150,150,0",
    "floor":"0,255,0,255",
    "rollerblind":"150"
  },
  {
    "lightingzone":"3F_Bedroom",
    "scene":"5",
    "scenename":"Full",
    "ceiling":"255",
    "wall":"255,255,255",
    "floor":"255,255,255,255",
    "rollerblind":"0"
  }
]
#19

If you were using the structure I described, it would tell you how many entries you have at the top level. If you need more complex filters, then there are other ways as mentioned by others.

#20

I think I might put the light/device definitions in an array to make it easier to process. You might also want to think whether you might need a scene to cover more than a single zone - I would certainly want that.

But it all hinges on how you want to actually use the data. As mentioned elsewhere in the thread, it may be a lot more useful to have the array as an array-like object, particularly if you expect it to mainly be used by scene.

{
    "Getting Dressed": [
        {
            "lightingzone": "3F_Bedroom",
            "devices": [
                {
                    "name": "ceiling",
                    "value": "255",
                },
                {
                    "name": "floor",
                    "value": "255,255,255,255",
                },
                ...
            ],
            ...
        },
        ...
    ],
    ...
}

So you might want to map some of that out before committing to a schema.

To be worth putting into a node, you'd need to be sure that it would be easily usable by other people who are likely to have very different sets of data.

#21

Agreed. I was trying to address one of the original questions:

which I think needs one of those more complex filters.

#22

Sorry, I was in a hurry with that original response. I was actually thinking about other possible ways to restructure the data so that you didn't need to process it as much - such as splitting the top-level by type so that all the lights were on one property. Then, once again, you could use Object.keys().length and stick with named properties if you wanted to. But of course, once you get into needing to filter an array or object by a sub-property, you might as well stick with an array so that you can use the filter function.

1 Like
#23

It could help to step back from your specific questions and look at the bigger picture. Over the years I've worked with a bunch of home automation packages, open-source and commercial. Each one had a slightly (or wildly) different approach to mapping the real world onto an abstract data structure. Most of the time, you could see how design choices were influenced by factors such as (human) readability, efficiency (of storage or command execution), modifiability, as well as bias toward certain database tools and programming languages. There is also an element of the developer's notion of "usability" -- does it feel "natural" to organize the world this way and does it correspond to the things that happen or are made to happen there.

Regarding what you have so far, if it works for you, great. If it doesn't, change it. Don't lock yourself into anything until you've used it seriously for a while. In terms of publication, I think if you look at the NR Library, you'll find that lot of home automation projects have been published as flows rather than custom nodes. That's a good way to get something out there quickly and get feedback without putting a lot of effort into something you haven't done before, unless of course you just want to want to develop a custom node for the fun of it.:face_with_raised_eyebrow:

1 Like