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

Reply via email to