I think you're probably right, the functionality for reading JSON must exist in the File adapter already Then I'd just need to figure out how to read from "HTTP_GET" UDF as the JSON contents
Ty, will go look at the source for the adapter On Tue, May 31, 2022 at 6:51 PM Julian Hyde <jhyde.apa...@gmail.com> wrote: > Is there any overlap with the file adapter? The file adapter can read > using various transports (file, http, optional compression) and various > formats (csv, json, html tables). > > > On May 31, 2022, at 12:47 PM, Gavin Ray <ray.gavi...@gmail.com> wrote: > > > > I don't think it's SQL Standard, but essentially it takes a text value > > containing JSON data (one record or an array) and converts it into a > > relational table: > > > > https://docs.oracle.com/database/121/SQLRF/functions092.htm > > > > I've managed to get most of this working, now I seem to be failing to put > > the syntax together correctly for the query > > I currently have: > > - A UDF ("HTTP_GET") which can make HTTP calls and return the data as a > > String > > - A table type for Collection<Map<String, Object>> ("MapCollectionTable") > > - A TableFunctionImpl ("JsonTableFunction"), which takes a JSON string, > > reads it with Jackson, and converts it into a "MapCollectionTable" > > > > The following work: > > > > SELECT HTTP_GET('https://jsonplaceholder.typicode.com/posts') > > > > SELECT * FROM TABLE( > > JSON_TABLE('[{ "id": "1", "name": "foo" }, { "id": "2", "name": "bar" > > }]')) > > > > But trying to combine them fails =/ > > > > SELECT * FROM TABLE( > > JSON_TABLE(HTTP_GET('https://jsonplaceholder.typicode.com/posts'))) > > > > The TableFunctionImpl is receiving "null" for the JSON value > > I guess it's maybe expecting static data? > > > > Calcite JSON_TABLE wip 2 (github.com) > > < > https://gist.github.com/GavinRay97/fbd16dd2b893cb59c720e6d514c5e39a#file-main-java > > > > > > On Tue, May 31, 2022 at 2:44 PM Julian Hyde <jh...@apache.org> wrote: > > > >> What is JSON_TABLE? Is it in the SQL standard? Can you give a simple > >> example? > >> > >> On Tue, May 31, 2022 at 10:03 AM Gavin Ray <ray.gavi...@gmail.com> > wrote: > >>> > >>> I dug up some examples from Github of using the "TableFunctionImpl" > >> class: > >>> > >>> mat-calcite-plugin/TableFunctions.java at > >>> a57a5ba80768066714c22bd9e8a9b529d4cb9a6b · vlsi/mat-calcite-plugin > >>> (github.com) > >>> < > >> > https://github.com/vlsi/mat-calcite-plugin/blob/a57a5ba80768066714c22bd9e8a9b529d4cb9a6b/MatCalcitePlugin/src/com/github/vlsi/mat/calcite/functions/TableFunctions.java > >>> > >>> Calcite_sql_driver/MazeTable.java at > >>> 6819088123e67631367ca927b112b58e4eb90829 · dream001/Calcite_sql_driver > >>> (github.com) > >>> < > >> > https://github.com/dream001/Calcite_sql_driver/blob/6819088123e67631367ca927b112b58e4eb90829/src/test/java/com/yonyou/calcite/MazeTable.java > >>> > >>> > >>> Currently trying to get a TableFunction that just returns a Map<String, > >>> Object> as a relation to work > >>> Have the below -- calling this shows that the function IS invoked, but > >> the > >>> data it gives back is garbage: > >>> > >>> Calcite JSON_TABLE WIP (github.com) > >>> <https://gist.github.com/GavinRay97/b42c69992a0d0c3a01f1c7e1b9ab1999> > >>> > >>> This is the output of main() > >>> > >>> json = {"id":1, "name":"John"} > >>> json = {"id":1, "name":"John"} > >>> json = {"id":1, "name":"John"} > >>> EXPR$0: id > >>> EXPR$0: id > >>> > >>> I'm doing something wrong here for sure, lol. > >>> > >>> > >>> On Tue, May 31, 2022 at 11:35 AM Gavin Ray <ray.gavi...@gmail.com> > >> wrote: > >>> > >>>> I'm interested in implementing JSON_TABLE functionality for Calcite > >>>> > >>>> This opens up some neat usecases, like adding HTTP request UDF's > >>>> then using JSON_TABLE to convert the result into a table: > >>>> > >>>> SELECT JSON_TABLE( > >>>> HTTP_GET('http://localhost:8080/api/v1/users/1')) > >>>> > >>>> Adding support for all of the functionality seems difficult, > >>>> but I'm wondering whether this could be done as a "SqlTableFunction" > >> UDF? > >>>> > >>>> I'm thinking it might be possible if the JSON_TABLE udf > >>>> expects Map<String, Object> and does inference based on that? > >>>> > >>>> Something like: > >>>> > >>>> WITH users AS ( > >>>> SELECT HTTP_GET('http://localhost:8080/api/v1/users') > >>>> ) SELECT > >>>> id, > >>>> name > >>>> FROM > >>>> JSON_TABLE(JSON_PATH(users, "$.0")) > >>>> > >>>> Does anyone have ideas or see issues with this approach? > >>>> Thank you =) > >>>> > >>>> > >>>> > >>>> > >> > >