Node-Red -Store and Retrieve JSON Data from a SQLite Database

hi and welcome to another video in this video we're going to look at uh storing and retrieving uh json data in a sqlite database now i've probably covered storing json data in a sequel live database before but back then i didn't actually realize that you can actually query the data in the an adjacent in a database based on the json parameters what i mean by that is if we look at the example data here and let's just make it a bit bigger so is there uh data in the retrieve from the database which i'll show you how to do in a second but we've basically got two fields in this scheme we've got the device which is the sensor one and we've got data which is the json data now in previous videos i showed you how to store this data but we create fields for the timestamp we create fields for the temperature we create another field for the humidity so the the database schema would look very different to this but what we've just got now is two fields one for the device name and one called data which contains the json data now what i didn't realize is that you can actually query the database for this json data so in other words i can do i can construct a query to find all data where the temp with the humidity is greater than a certain value or less than a certain value etc and i'm going to going to show you how to do that now prior to that what we did is we'd create as i said we create fields for this data and if i look at the this one here this is the way we normally do we create a table sensor data where we'd have the device as a field it's a text field and then we'd have a time as a field which is an integer and then we'd have temperature as a field and it's a floating humidity is a field which again is a float that's the way we normally construct it that's where i used to construct it before i actually realized that we can actually do a query actually on the json data the reason i was obviously storing it this way is because i wanted to query on the humidity and i wanted a query on the temperature and i didn't realize that if it was actually part of a json string i could still do that i can't remember when that was introduced into sql light but there is a tutorial on the site and it it is mentioned there so if you're interested in when it actually came in it's certainly a few years ago okay now this is the way we construct the table for this one here so it's just a simple device field followed by a data field which contains text and remember json is just a string so json data is text data now that is the way you see tables created on most um demo flows and most demo videos on sql lite what i actually did was create a little function here which you don't need to change except for the the name of the table to create and this will basically create a table based on fields you put into this template here so a device is text the data is text if you wanted to put temperature as an integer you just put temperature integer uh etc underneath here it makes it much easier to actually create the table rather than having to create it this way but that's your choice you can either use this type of structure or you can use this type of structure the flow will be available for download so you can see both i actually do believe i covered that in another video as well as part of my demo flows i usually have a node here to to drop the table just makes it easier when you're generating test data now this part of the flow here just generates some sample data so it just creates the data so we have some random temperatures and we have some random humidities here and we insert it into the table at the bottom here and you can see here where i construct the the json payload now the advantage of actually storing it as json rather than actually splitting out the data out into fields is is the fact that it actually makes a very simple create if you've ever created tables in sql light and you've had to say 10 fields you'd have 10 entries here and it can get very complex and the problem with it is what happens if you want to change it and you want to add an 11th or you want it to delete one and go down to nine well you obviously have to edit this which is a bit of a mess but you also have to create that new field in the table or delete that new field from the table and that's not an easy thing to do whereas with with the json format you just add it into the json payload and you don't make don't need to make any changes whatsoever in this com this sql command it stays exactly the same it doesn't care what's in that json payload it's just text and so that's a that's the main reason i like this and as i say i wish i'd have discovered it earlier because it makes it much much easier to actually store the data so you just take the data coming in as a json string and you just pop it straight into the table and if you need to query on those parameters you can so if you just look over here and we have got some fields here time stamp temperature and humidity i'll just delete it cancel that and underneath here i've got a little function here that's going to query the database for the temperature greater than a value 16 there it is there and notice the query now it's not so easy to see in a video but there is a to tool on the site where you you'll find all this you extract the data using the function json extract and we're looking for the temperature key which is here and notice the format of it.temperature this extracts the temperature key from the the database and then we just do a standard comparison is it greater than 16. so if i do that query you can see we got two objects there and if i look at the temperature value here you can see it's 16 and if i look at the temperature value here you can see it's 19. not only can we query based on that we can even update the values and we can update again using the json extract and what we're going to do is we're going to set the humidity which is that value here and we're going to set it with where a timestamp is equal to a certain timestamp now you obviously have to hunt through the data and find the the the data with this timestamp so that's not so easy to demonstrate and i'm not going to demonstrate it here but you can try it out yourself now the reason i i chose the timestamp is because it needs to be a unique field or a unique key and the timestamp is the the thing that is unique in that data okay that brings us the end of video i hope you found it useful and don't forget to leave your comments and if if you want to get notified of new videos then you can always subscribe to the channel and until next time goodbye

Channel: Steve Cope

Views: 8,288

Keywords: node-red, sqlite, JSON, Storing, Retrieve, video, stevesnoderedguid, tutorial, how to

Id: 81qE8wNHO5Q

Length: 8min 27sec (507 seconds)

Published: Fri May 06 2022