How do I access a string array and then convert to 2 separate numbers?

[questuk]

Hi,

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 ?

Thanks Gaz

[zenofmud]

Can you provide your database schema please

[E1cid]

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[0].data

Or use msg.payload[0]["json_extract(Database.........);"] filling in the bits i have left as .......

[questuk]

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

AND

Thanks Gaz

[E1cid]

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.

[questuk]

Hi,

I think I have solved it with your help and some more research on the internet

I had to change my query to this ...

select
json_extract (Database.data,'$.humidity')as HUMIDITY,
json_extract (Database.data,'$.timestamp') as TIME
from Database;

Now they appear as 2 separate numbers ...

It's always obvious when you find the answer

Thanks Gaz

[craigcurtin]

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)

Craig

[questuk]

Hi,

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.

Storing and Retrieving JSON Data In SQLite

Thanks Gaz

[jbudd]

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)
and not
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!