Presto: No support for wildcard in JSON Path

[FlxJaron]

The documentation at https://prestodb.io/docs/current/functions/json.html refers to http://goessner.net/articles/JsonPath/ which has a wildcard example.

select json_extract('{"store": {"book": [{"author": "author0"},{"author": "author1"}]}}', '$.store.book[*].author');
Invalid JSON path: '$.store.book[*].author'

When I change this to a specific index, it works.

select json_extract('{"store": {"book": [{"author": "author0"},{"author": "author1"}]}}', '$.store.book[0].author');
   _col0   
-----------
 "author0" 

[FlxJaron]

@martint I see that you responded to similar tickets, do you have time to look into this?

[electrum]

Wildcards are not supported. We should update the documentation.

[FlxJaron]

@electrum Thank you for your reply. What would it take to support it? We might want to implement it ourself.

[ArturGajowy]

I looked into offloading path resolving to https://github.com/jayway/JsonPath, but there seems to be a 1.5x performance penalty. If we're willing to accept that for the added benefit of broader JsonPath support and less maintainance burden, I'll gladly take this one.

Also, we could introduce a feature toggle for the JsonPath engine used - then the user gets to choose: performance or completeness.

@martint would you like me to start implementation of any of the above proposals?

[ArturGajowy]

@electrum what's your opinion?

[RobinUS2]

I like that idea @ArturGajowy , feature toggle is okay, even nicer would be to make it dynamic (e.g. if path contains * use JsonPath else take current implementation). So we don't degrade performance but do have the benefits.

[nezihyigitbasi]

I put up a PR with the jayway library some time ago (#5882) and we have been using it in prod for a while.

[electrum]

I think we should implement it ourselves using Jackson. JSON functions are used heavily and are already very expensive, so we shouldn't make it worse.

[dain]

Another issue is we need to assure the function is backwards compatible with the current one.

[polvoazul]

Any news on that?

[rajivchodisetti]

Hi, Is anyone working on this ?

[sslyxhz]

Any other solutions on that?

[cjie]

Any other solutions on this?

[romad17]

Also very needed for us, please add this functionality.
In addition a tilda syntax will be much appreciated to get the keys. like $.*~

Thank you!

[rongrong]

It seems that there are two concerns here:
1) some semantic differences between jayway and jackson.
2) jayway might introduce a performance penalty.

So this is what I will propose:
1) We should benchmark the two libraries and document the performance differences.
2) We can introduce jayway JSON functions and control which library to use via a session property (this might need a little hack in function resolution)

That way users can make informed decisions on whether they'd be willing to pay for the performance penalty for the enriched functionalities.

Any takers?