I am trying out JSON MATCH functionality using JsonIndexQuic Apache Pinot #pinot-dev

I am trying out JSON_MATCH functionality using Jso...

Amrish Lal

01/31/2021, 5:34 AM

I am trying out JSON_MATCH functionality using JsonIndexQuickStart. First I ran the query

select actor from githubEvents limit 1

which produced the result:

{"id":18542751,"login":"LimeVista","display_login":"LimeVista","gravatar_id":"","url":"<https://api.github.com/users/LimeVista>","avatar_url":"<https://avatars.githubusercontent.com/u/18542751>?"}

then I tried to write a query using JSON_MATCH predicate to match the above row:

select actor from githubEvents WHERE JSON_MATCH(actor, 'login = ''LimeVista''')

and this produces an empty result set. I am wondering if I am missing anything or if the query above is incorrect?

Sidd

01/31/2021, 6:18 AM

Can you try count(*) for the same json match filter.? Does it return 1? Also try using json_extract_scalar (actor, '$.id', 'STRING') from ... json_match ....

Amrish Lal

01/31/2021, 9:00 PM

select count(*) from githubEvents WHERE JSON_MATCH(actor, 'login = ''LimeVista''')

produces empty result. I am wondering if there is an issue with Json index search here? select

json_extract_scalar(actor,'$.id', 'STRING')

from githubEvents limit 10 works and returns results.

select count(*) from githubEvents WHERE json_extract_scalar(actor,'$.id', 'STRING') = '18542751'

also works

Amrish Lal

01/31/2021, 9:20 PM

Seems like there is a small bug in QueryContextConverterUtils.java. I will create a PR with fix and SQL level test cases.

👍 2

Kishore G

02/01/2021, 2:50 AM

can we change the parser such that double escape is not needed?

Amrish Lal

02/01/2021, 2:51 AM

I can take a look at that.

Kishore G

02/01/2021, 2:52 AM

Thanks Amrish

Amrish Lal

02/03/2021, 4:25 AM

Bug fix PR is here: https://github.com/apache/incubator-pinot/pull/6535.

Amrish Lal

02/03/2021, 4:32 AM

Double escape appears to be unavoidable in JSON_MATCH since the function requires passing predicate string as string literal in the second argument. That predicate string again contains literals (and hence the escape). The predicate string is parsed in JsonMatchFilterOperator by invoking Calcite, so needs to be well formed predicate.

2 Views

Open in Slack

Previous Next