How to store data in JSON file in Node-RED

General
1

Hi, this is quite a "beginner's" question but hopefully someone can help?

I want to build up a "definitions" file that describes the properties for various custom DIY home automation devices.

I'm thinking the best way might be some form of JSON file on disk.

Should I use context, and store to disk?

I have already turned on disk storage in settings.js by adding the following:

        default: {
            module:"localfilesystem"
        },
    },

So for example:

Light 1 (zone = bedroom1, name="Wall Light", fitting=wall, colour=warmwhite, currentlevel=50%)
Light 2 (zone = bedroom1, name="Ceiling Light", fitting=ceiling, colour=RGB, currentlevel=(40,50,0))
PIR 1 (zone = bedroom1, fitting=ceiling, state=idle, etc....)
BedroomScene0 (Light1=0%, Light2=0%, Light3=0%, Light4=0%)
BedroomScene1 (Light1=50%, Light2=25%, Light3=0%, Light4=100%)
BedroomScene2 (Light1=100%, Light2=100%, Light3=100%, Light4=100%)
etc...

The reason I think I'd like to do this, is so that in the future I might:

  • dynamically generate dashboard buttons, sliders, etc.
  • count the number of light circuits in a room (in case I add or remove some later)
  • be able to set up / store light scene definitions, e.g. from the dashboard
  • persist light levels across reboots
  • more?

Does this seem like a good thing to do?

Any tips on how to manage this well, e.g. how to populate the settings initially, such as when I add a new light etc?

Sorry it's a general question, but I'm overwhelmed by the possibilities so far.

2

I think that possibly an sqlite database might be a better solution for this. That is exactly what databases are designed for, storing information and then looking it up later in various ways.

3

@hazymat, I started out using a database for this sort of thing, storing the current state of each device and updating it at every change. Since the introduction of persistent storage, I have gone back to using a JSON file for the static information about my devices and a global variable to store state information. In your case, a JSON file with something like this could store the device description:

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

You can read the file and pass the payload through a JSON node. You get an array of objects that can be saved as a global variable or run through a split node to get a message or variable for each device. It's easy to edit the JSON in a text editor to add a new device or modify an old one.

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.