Hi Alberto, I think this should help. SELECT id, json.filter(json_data, '$.data.time') AS time, json.filter(json_data, '$.data.others') AS others, FROM tbljson; Regards, Brian Hood On Mon, May 2, 2016 at 12:44 PM, Alberto Ferrari < aferrari@starconnecting.com> wrote:

Thanks Martin: Can you (or anyone) give me an example of that query?

regards.

Alberto Ferrari Email/Hangout: aferrari@starconnecting.com Skype: support-starconnecting

2016-05-02 2:24 GMT-03:00 Martin Kersten :

...

On 02/05/16 01:03, Alberto Ferrari wrote:

...

Can anyone help with json.filters? I have this example table ...

CREATE TABLE tblJson ( "id" INT, "json_data" JSON, );

... with these values...

INSERT INTO tblJson (id, json_data) VALUES (1, '{"data":{"time":"2015-10-09 10:36:35","others":"xxx"}}'), (2, '{"data":{"time":"2015-10-09 12:18:20","others":"yyy"}}'), (3, '{"data":{"time":"2015-12-30 16:31:22","others":"zzz"}}'); The data is ok, I can select the rows, it's ok. Also I can select only the "time" field, like this... SELECT json.filter(json_data, 'data.time') FROM tblJson;

But now I want to filter the content in "json_data" specific field, i.e. SELECT all the rows where "time like '2015-10-09%'" , or maybe "time = '2015-10-09 12:18:20'"

of course not, SQL does not know anything about the Json internal structure. You first have to extract the time component and then apply the like operation.

...

I tried but can't do it. Any help? Thanks!

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Hi Alberto, Yes you can take a look at my JSON_SQUASH DROP FUNCTION JSON_SQUASH; CREATE FUNCTION JSON_SQUASH(name string) RETURNS string BEGIN DECLARE res STRING; SET res = REPLACE(REPLACE(REPLACE(name, '[\"', ''), '\"]', ''), '"', ''); IF (res = '[]') THEN SET res = REPLACE(res, '[]', '<no data>'); END IF; RETURN res; END; SELECT recv_date, recv_time, JSON_SQUASH(hostname) AS hostname FROM (SELECT a.recv_date AS recv_date, a.recv_time AS recv_time, json.filter(json_data, '$.http.host') AS hostname FROM http_traffic_json a JOIN http_traffic_ua b ON (a.guid = b.guid)) AS origin WHERE recv_time BETWEEN CURTIME() - 300 AND CURTIME(); On Mon, May 2, 2016 at 11:29 PM, Alberto Ferrari < aferrari@starconnecting.com> wrote:

Hi Brian, thanks for your response.

Is there a way to filter what I need inside the first SELECT? Something like this? ...

SELECT id, json.filter(json_data, '$.data.[?(time=2015-10-09*)]') AS time, /* I know this does not work, but it's in an example */ (...other fields...) FROM tbljson;

2016-05-02 18:51 GMT-03:00 Brian Hood :

...

Hi Alberto,

I think this should help.

SELECT id, json.filter(json_data, '$.data.time') AS time, json.filter(json_data, '$.data.others') AS others, FROM tbljson;

Regards,

Brian Hood

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list