With sqlite.exe from tools zipfile, I reproduce the bug with this script:
If I add, just after the create table the line
create index if not exists idxtype on records(activitytype) ;
I have a correct result
If I don't add the index, I've "malformed json" error.
Can you try reproduce?
Regards
Gilles
Here is the script without double quote
drop table if exists records;
create table if not exists records( activityId integer primary key,
activityDate text, activityType integer,message text) ;
insert into records values (1,'2016-09-01',22,'{"Date":"09/01/2016
02:00:00","CountAnalyzedMails":44} ');
insert into records values (3,'2016-09-02',22,'{"Date":"09/02/2016
02:00:00","CountAnalyzedMails":54} ');
insert into records values (5,'2016-09-02',26,'nojson');
select * ,json_valid(message) from records;
select activityDate,Date, CountEmails from
(
select activityDate, jsonstr, json_valid(jsonstr),
(activityDate) ||'_' || json_extract(jsonstr , '$.Date') as multiinfo,
json_extract(jsonstr , '$.Date') as Date,
json_extract(jsonstr , '$.CountAnalyzedMails') as CountEmails
from (
select *, length(message) as msglen,
message as jsonstr ,
json_valid(message) as is_json_valid
from (select * from records where activitytype=22 ) where
is_json_valid=1)
) as allrs0
where
multiinfo in
(
select max(activityDate) ||'_' || date as concat_sel from
(
select activityDate, jsonstr, json_valid(jsonstr),
(activityDate) ||'_' || json_extract(jsonstr , '$.Date') as multiinfo,
json_extract(jsonstr , '$.Date') as Date,
json_extract(jsonstr , '$.CountAnalyzedMails') as CountEmails
from (
select *, length(message) as msglen,
message as jsonstr ,
json_valid(message) as is_json_valid
from (select * from records where activitytype=22 ) where
is_json_valid=1)
) as allrs1
group by date order by date);
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
