正在删除TEIID中JSON响应中的无效字符

Question

我有一个从invokeHTTP函数生成的json响应，然后将其传递到物化视图。显然，响应中嵌入了控制字符。当转换为XMLTABLE时，我得到以下错误：

at org.teiid.query.xquery.saxon.XQueryEvaluator.evaluateXQuery(XQueryEvaluator.java:147) [teiid-engine-9.1.1.jar:9.1.1]
at org.teiid.query.processor.relational.XMLTableNode$1.run(XMLTableNode.java:269) [teiid-engine-9.1.1.jar:9.1.1]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282) [teiid-engine-9.1.1.jar:9.1.1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-9.1.1.jar:9.1.1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-9.1.1.jar:9.1.1]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_202]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_202]
at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_202]
Caused by: nu.xom.ParsingException: 0x8 is not allowed in XML content

示例响应：

result
[
{
"firstName":"Johnb",
"lastName":"Doe"
}
]

视图定义：

CREATE VIEW Information (firstName string, lastName String) AS
SELECT x.firstName, x.lastName 
FROM (EXEC model.invokeHttp(action => 'GET', endpoint => url ,stream => 'TRUE')) AS f,
XMLTABLE('/result/result' PASSING JSONTOXML('result',f.result) 
COLUMNS 
firstName string PATH 'firstName',
lastName string PATH 'lastName')x

TEIID中是否有任何函数可以从JSON响应中删除不需要的/控制字符，或跳过/排除包含无效字符的记录？

Answer 1

您也可以直接使用JSONTABLE，请参阅http://teiid.github.io/teiid-documents/master/content/reference/r_jsontable.html

另外，请参阅上的其他JSON函数http://teiid.github.io/teiid-documents/master/content/reference/r_json-functions.html