Json SQLite extract value returns Blank ?

(1) By Questuk on 2022-08-30 11:28:04 [link] [source]

Hi, I am still learning this,

I have a database with the Table Energy I have a column inside of it called IP22 I want to extract the value 2.125

I may not have the object correct that I entered into the database IP22 column ?

{ "meter_reading": 2.125 }

my JSON query is wrong ?

SELECT json_extract ('{"IP22":,"$.meter_reading"}') AS Test FROM Energy;

The query does run, but gives a blank cell on DB Browser for SQLIte.

Any ideas ... thanks Gaz

(2.1) By ddevienne on 2022-08-30 11:34:05 edited from 2.0 in reply to 1 [link] [source]

Looks like you're passing a single arg to json_extract no?
You misplaced your single-quote and closing-curly.

sqlite> SELECT json_extract ('{"meter_reading": 2.125}', '$.meter_reading') AS Test;
2.125

(3.2) By ddevienne on 2022-08-30 11:58:57 edited from 3.1 in reply to 2.1 [source]

Maybe this will make more sense to you:

sqlite> create table Energy ( IP22 text );
sqlite> insert into Energy values ('{"meter_reading": 2.125}');
sqlite> SELECT json_extract (IP22, '$.meter_reading') AS Test from Energy;
2.125

(4) By Questuk on 2022-08-30 13:32:57 in reply to 3.2 [link] [source]

Hi ddevienne,

Thanks for that info , i did try it and got a result of 2.125. I kept the original question very simple.

My actual database is setup for months of recording temperatures and meter_reading etc from various sensors
with fields set to the last part of IP address of ... IP15 .. IP22 .. IP92

This data will then be extracted for a graph.

This my database ...

Table ... Energy

| IP15 | IP22 | IP92 |

Json objects under each correct column

Json object data will be put into the correct IP field.

I think that your query looks at all of my database, how do i just extract all data from IP22 for example ?

Thanks for your valuable help

Gaz

(5) By Questuk on 2022-08-30 13:51:14 in reply to 4 [link] [source]

Hi again,

Sorry i forgot to try the second of your suggestions as I was distracted, yes it does give me all the information i just asked for in my last question above.

Sorry , once again thank you

Gaz