Hello,
I see a strange problem in sqlite with json and pehaps a bug.
I've a table with somes json record (when activityType=22) and not json
record (when activityType is not 22)
I need made somes complex request on json value.
And the request below give me an error "malformed JSON".
If we add , after the create table, the index creation below:
create index if not exists idxtype on records(activitytype);
We don't have the error and we have the wanted result.
Here is the sql code, to create and fill a small sample table, then
(complex) request:
drop table if exists records;
create table if not exists records(activityId integer primary key,
activityDate text, activityType integer,message text) ;
delete from records;
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');
update records set message=replace(message,'""','"');
select * ,json_valid(message) from records;
drop index if exists idxtype;
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);
Best regards
Gilles Vollant
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
