# How do I access a string array and then convert to 2 separate numbers?
**URL:** https://discourse.nodered.org/t/how-do-i-access-a-string-array-and-then-convert-to-2-separate-numbers/66393
**Category:** General
**Created:** [13 August 2022 13:02 UTC](https://discourse.nodered.org/t/how-do-i-access-a-string-array-and-then-convert-to-2-separate-numbers/66393 "2022-08-13T13:02:01Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@questuk](https://discourse.nodered.org/u/questuk)
#### Post date: [13 August 2022 13:02 UTC](https://discourse.nodered.org/t/how-do-i-access-a-string-array-and-then-convert-to-2-separate-numbers/66393/1 "2022-08-13T13:02:01Z")
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
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [13 August 2022 13:09 UTC](https://discourse.nodered.org/t/how-do-i-access-a-string-array-and-then-convert-to-2-separate-numbers/66393/2 "2022-08-13T13:09:03Z")
Can you provide your database schema please
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [13 August 2022 13:11 UTC](https://discourse.nodered.org/t/how-do-i-access-a-string-array-and-then-convert-to-2-separate-numbers/66393/3 "2022-08-13T13:11:23Z")
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 .......
---
### Author:  [@questuk](https://discourse.nodered.org/u/questuk)
#### Post date: [13 August 2022 13:49 UTC](https://discourse.nodered.org/t/how-do-i-access-a-string-array-and-then-convert-to-2-separate-numbers/66393/4 "2022-08-13T13:49:47Z")
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
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [13 August 2022 14:25 UTC](https://discourse.nodered.org/t/how-do-i-access-a-string-array-and-then-convert-to-2-separate-numbers/66393/5 "2022-08-13T14:25:58Z")
Your sql query is returning a json string you should be able to add [JSON\_QUERY (Transact-SQL) - SQL Server | Microsoft Docs](https://docs.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql?view=sql-server-ver16) to return the required data
But would be simpler if you stored you data in individual columns and not in json form.
---
### Author:  [@questuk](https://discourse.nodered.org/u/questuk)
#### Post date: [13 August 2022 14:52 UTC](https://discourse.nodered.org/t/how-do-i-access-a-string-array-and-then-convert-to-2-separate-numbers/66393/6 "2022-08-13T14:52:54Z")
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
---
### Author:  [@craigcurtin](https://discourse.nodered.org/u/craigcurtin)
#### Post date: [14 August 2022 00:44 UTC](https://discourse.nodered.org/t/how-do-i-access-a-string-array-and-then-convert-to-2-separate-numbers/66393/7 "2022-08-14T00:44:28Z")
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
---
### Author:  [@questuk](https://discourse.nodered.org/u/questuk)
#### Post date: [14 August 2022 06:47 UTC](https://discourse.nodered.org/t/how-do-i-access-a-string-array-and-then-convert-to-2-separate-numbers/66393/8 "2022-08-14T06:47:13Z")
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](https://stevesnoderedguide.com/storing-retrieving-json-data-sqlite)**
>
> In this tutorial we look at storing and retrieving JSON data in SQLite and also at how we can easily store time series data in SQLite.
Thanks Gaz
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [14 August 2022 09:32 UTC](https://discourse.nodered.org/t/how-do-i-access-a-string-array-and-then-convert-to-2-separate-numbers/66393/9 "2022-08-14T09:32:05Z")
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!
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [28 August 2022 09:32 UTC](https://discourse.nodered.org/t/how-do-i-access-a-string-array-and-then-convert-to-2-separate-numbers/66393/10 "2022-08-28T09:32:41Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.