0

I have an example data set like below

id|accountid|attributes|created|type

1|10|{'base:instances': '{}', 'cont:contact': 'CLOSED', 'cont:contactchanged': '1468516440931', 'devconn:lastchange': '1462387904432', 'devconn:signal': '100', 'devconn:state': 'ONLINE', 'devpow:backupbatterycapable': 'false', 'devpow:battery': '66', 'devpow:linecapable': 'false', 'devpow:source': 'BATTERY', 'devpow:sourcechanged': '1462387904403', 'temp:temperature': '25.75'}|2016-05-04 18:51:44+0000|Test

2|20|{'base:instances': '{}', 'cont:contact': 'CLOSED', 'cont:contactchanged': '1468516440931', 'devconn:lastchange': '1462387904432', 'devconn:signal': '100', 'devconn:state': 'ONLINE', 'devpow:backupbatterycapable': 'false', 'devpow:battery': '66', 'devpow:linecapable': 'false', 'devpow:source': 'BATTERY', 'devpow:sourcechanged': '1462387904403', 'temp:temperature': '25.75'}|2016-05-04 18:51:44+0000|Prod

3|30|{'base:instances': '{}', 'cont:contact': 'CLOSED', 'cont:contactchanged': '1468516440931', 'devconn:lastchange': '1462387904432', 'devconn:signal': '100', 'devconn:state': 'ONLINE', 'devpow:backupbatterycapable': 'false', 'devpow:battery': '66', 'devpow:linecapable': 'false', 'devpow:source': 'BATTERY', 'devpow:sourcechanged': '1462387904403', 'temp:temperature': '25.75'}|2016-05-04 18:51:44+0000|Prod

4|40|{'base:instances': '{}', 'cont:contact': 'CLOSED', 'cont:contactchanged': '1468516440931', 'devconn:lastchange': '1462387904432', 'devconn:signal': '100', 'devconn:state': 'ONLINE', 'devpow:backupbatterycapable': 'false', 'devpow:battery': '66', 'devpow:linecapable': 'false', 'devpow:source': 'BATTERY', 'devpow:sourcechanged': '1462387904403', 'temp:temperature': '25.75'}|2016-05-04 18:51:44+0000|Test

I import this to sqlite3 3.13 to do some analysis (.mode csv, .headers on, .separator '|', .import file.csv dev)

As you can see the second field is json formatted data the keys all have : in the names and I think part of my issue.

I would like to and select all rows with column type matching Test and print out the devpow:battery value from the json in attributes column

I have tried all the below and I can't get this to work

select json_extract(dev.attributes, '$.devpower:battery') from dev where type=="Test";

select attributes.[devpower:battery] from dev where type=="Test";

select 'attributes.devpower:battery' from dev where type=="Test";

And quite a few permeations of the above. Any help is greatly appreciated.

oz123
  • 27,559
  • 27
  • 125
  • 187
Type11
  • 1
  • 4

1 Answers1

0

devpow:battery is a perfectly valid object label, and it works if you are actually using valid JSON (the values in your example are not), and if you spell the label correctly (which you did not):

> SELECT attributes FROM dev;
{"devpow:battery": "66"}
> SELECT json_extract(dev.attributes, '$.devpow:battery') FROM dev WHERE ...;
66
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Ok then I think I am misunderstanding how json_extract is used. I will not know the power that is what I want to grab from the query for a row that has a certain column value (In this case type=="Test") So for any row where type column == "Test" I want to get from the attributes column the value for the jason pair in that field named devpow:battery or 66 in your example. Which is why I thought it might be something like the above `select attributes.[devpower:battery] from dev where type=="Test";` `select 'attributes.devpower:battery' from dev where type=="Test";` – Type11 Jul 18 '16 at 19:56
  • But none of those work which is what lead me to json_extract. Hope I am explaining that better, thanks. – Type11 Jul 18 '16 at 19:56