[teiid-issues] [JBoss JIRA] (TEIID-5481) Evaluate JSONPATH as expression language for Json payload to table

[Steven Hawkins (Jira)]

    [ https://issues.jboss.org/browse/TEIID-5481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13737805#comment-13737805 ] 

Steven Hawkins commented on TEIID-5481:
---------------------------------------

After some additional consideration jsoncast is probably not initially needed - pg does not offer direct cast of json to non-string types either.  Shown in the pr is a form of json parsing that converts to a multi-dimensional array for processing by arraytable:

{code}
select * from arraytable(rows jsontoarray(jsonValue, '$..book[0:2]', false, '@.author', '@.title', '@.price') columns author string, title string, price decimal) as x
{code}

This side steps the issue of introducing additional parsing constructs and a reflective interface for contributing a RelationalNode.  Alternatively I could introduce a more formal definition of a record/row set table function extension, or just go ahead and introduce some additional parsing logic, which would simplify this example to:

{code}
select * from jsontable(jsonValue, '$..book[0:2]', false columns author string, title string, price decimal) as x
{code}

> Evaluate JSONPATH as expression language for Json payload to table
> ------------------------------------------------------------------
>
>                 Key: TEIID-5481
>                 URL: https://issues.jboss.org/browse/TEIID-5481
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine
>            Reporter: Ramesh Reddy
>            Assignee: Steven Hawkins
>            Priority: Major
>             Fix For: 12.3
>
>
> Currently in Teiid in order to turn a JSON document to a relational structure, JSONTOXML function is used then XPATH used on it to retrieve data using XMLTABLE.
> Investigate the feasibility of using JSONPATH [1]  directly on JSON document to build a JSONTABLE construct.
> [1] https://code.google.com/archive/p/json-path/



--
This message was sent by Atlassian Jira
(v7.12.1#712002)