I have tried for many hours to try and do this myself but it has eluded me
I am accessing my Sqlite database that is configured to store data as JSON.
I have managed to get the data from that for Humidity & Timestamp.
But I cannot access the returned array numbers ?
How do I do it ?
Can you provide your database schema please
Try using AS in your sql query to get a clear name.
SELECT json_extract (.....) AS data FROM ......
then you would use something like
msg.payload["json_extract(Database.........);"] filling in the bits i have left as .......
Hi to you both
As you suggested I changed this ...
select json_extract (Database.data,'$.humidity' , '$.timestamp') AS new_data from Database;
and now get ...
But it still is a string and not separate numbers ?
Here is my database for zenofmud
Your sql query is returning a json string you should be able to add JSON_QUERY (Transact-SQL) - SQL Server | Microsoft Docs to return the required data
But would be simpler if you stored you data in individual columns and not in json form.
I think I have solved it with your help and some more research on the internet
I had to change my query to this ...
json_extract (Database.data,'$.humidity')as HUMIDITY,
json_extract (Database.data,'$.timestamp') as TIME
Now they appear as 2 separate numbers ...
It's always obvious when you find the answer
It would much a lot more sense though (as E1Cid said) at the time of storing the data to break the string out and store the values in two seperate fields (temperature and humidity)
Yes that is what I have been doing for the last 4 years, then I read this page and it's video and I could see how it would be much more flexible and easier for a human being too read.
It has taken me a while to understand it all and get it working, but now I can see how easy it is ( once I have had help on here for the last part ).
If you are interested in learning more this is the page and video I watched and convinced me this was the way I wanted to go.
The reason Steve gives for storing non-atomic data as json in a single field is
What happens if I need suddenly to record the atmospheric pressure?
We need to add another field to the table which is possible but not simple.
[but if] Our data is simply a JSON object with time stamp,temperature and humidity.
We can easily expand it without having to modify the table.
But it really isn't difficult to add a new field to a table (though I'm not familiar with SQLite).
And if it's such a good idea to have everything in one field, why define the table as
CREATE TABLE sensor_data2(DEVICE TEXT NOT NULL,DATA TEXT NOT NULL)
CREATE TABLE sensor_data2(DATA TEXT NOT NULL)
In fact why have a sensor_data2 table at all? Just put everything as json in a single table
CREATE TABLE the_world(DATA TEXT NOT NULL)
Nope, I just can't see it. Sorry!
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.