https://pinot.apache.org/ logo
#pinot-dev
Title
# pinot-dev
a

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?
s

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 ....
a

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
Seems like there is a small bug in QueryContextConverterUtils.java. I will create a PR with fix and SQL level test cases.
👍 2
k

Kishore G

02/01/2021, 2:50 AM
can we change the parser such that double escape is not needed?
a

Amrish Lal

02/01/2021, 2:51 AM
I can take a look at that.
k

Kishore G

02/01/2021, 2:52 AM
Thanks Amrish
a

Amrish Lal

02/03/2021, 4:25 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.