Using JSON array in teiid

Question

I am using teiid for calling REST.

If one output response have a JSON array of contacts with different feilds like id,first-name lastname. What should I write in the procedure to get the JSON array.

An example with my details will be very helpful Thankyou

score 0 · Answer 1 · answered Nov 06 '17 at 14:58

For example, you have JSON contents like this

[
   {
      "firstName":"George",
      "lastName":"Micheal"
   },
   {
      "firstName":"Jerry",
      "lastName":"Springer"
   }
]

You can issue a query like

SELECT * 
FROM XMLTABLE('/response/response' passing jsontoxml('response', jsonparse('[{"firstName" : "George", "lastName" : "Micheal" }, { "firstName" : "Jerry", "lastName" : "Springer" }]', TRUE)) COLUMNS firstname string, lastname string) x

That will return tabular results like

firstName  lastName
George     Micheal
Jerry      Springer

Now, if you want you can wrap above into a view like

CREATE VIEW Person (firstName string, lastName String) AS
    SELECT x.firstName, x.lastName 
    FROM XMLTABLE('/response/response' passing jsontoxml('response', jsonparse('[{"firstName" : "George", "lastName" : "Micheal" }, { "firstName" : "Jerry", "lastName" : "Springer" }]', TRUE)) COLUMNS firstname string, lastname string) x

Put the above definition of the view into a VDB, and when you deploy VDB into the Teiid Server, then using the JDBC driver you can simple issue

select * from person

also note above that, since I was providing the JSON document I needed the JSONPARSE function, otherwise it expects blob. The tricky part the double "/response/response" as root, where JSONTOXML wraps each array beginning with 'response' element and then also wraps root in 'response' element.

Using JSON array in teiid

1 Answers1