13 Replies Latest reply on Jul 30, 2015 8:38 PM by Ramesh Reddy

Teiid - Consume JSON tree from REST web service

I am trying to consume the JSON data returned by REST WS, Essentially this data is a array with each element of array may be having one or more array with few attributes at each level.

A sample data is attached with this post. I would like to create VDB using this web service and write a transformation which will show top level array node as a row (so multiple rows)

I am not able to find any example of this sort or documentation for writing this kind of transformation. I am able to consume JSON data from a REST WS which returns a single node.

It will be really great help if I get pointers around this area.

Thanks,

-Prabh

• 1. Re: Teiid - Consume JSON tree from REST web service

I believe your JSON document needs to start with  "{" then array elements then "}". What is error you are seeing? Note that at column level Teiid will only support arrays of simple types.

• 2. Re: Teiid - Consume JSON tree from REST web service

Hi, there's a tutorial for importing JSON webservices available here: How to Consume a JSON REST Web Service in Teiid Designer

Since your top-level object is an array, you will have to specify the second-level response element as the root path (by right clicking it and selecting Set as root path, see screenshot). You can then double click the desired elements to add to the transformation.

It is not clear what exactly you want to extract from the json, if you provide some sample expected output, I could further assist you in creating the transformation.

BTW, there are some syntax errors in your sample JSON, namely missing commas on lines 11, 70 and 129.

• 3. Re: Teiid - Consume JSON tree from REST web service

Thanks Ramesh. In case of array JSON begin with "[" and end with "]" and in between this we have set of nodes, and each Node begin with "{" and end with "}".

Just would like to understand more on when you say Teiid only supports arrays of simple types, I am looking for recursive array support like a tree structure, where  top level is set of Nodes, each node will have attributes(key,value pair) + array and so on up to 2/3 level down.  Does this kind of parsing supported.

@Andrej,  Thanks for your inputs and sorry for trouble with syntax errors. Well using the mentioned tutorial I tried to consume the JSON output, for simple JSON it works perfectly fine. But when I try to consume data as I mentioned in my Sample Output it throws below error.

The Query I was trying is

SELECT * FROM ( exec "TAS1ViewModel"."TAS1Proc"('generalConfiguration[modelType:"5100u"]') ) AS X_X

The exception I get is

OPTIMIZATION COMPLETE:

PROCESSOR PLAN:

============================================================================

15:20:44,863 ERROR [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue24) kxJ0Y9ATIMQ0 TEIID30019 Unexpected exception for request kxJ0Y9ATIMQ0.0: org.teiid.core.TeiidRuntimeException: nu/xom/NodeFactory

at org.teiid.query.processor.relational.XMLTableNode$1.run(XMLTableNode.java:275) [teiid-engine-8.11.0.Final.jar:8.11.0.Final] at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.11.0.Final.jar:8.11.0.Final] at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.11.0.Final.jar:8.11.0.Final]

at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.11.0.Final.jar:8.11.0.Final] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_31] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_31]

Caused by: java.lang.NoClassDefFoundError: nu/xom/NodeFactory

at org.teiid.query.xquery.saxon.XQueryEvaluator.evaluateXQuery(XQueryEvaluator.java:137) [teiid-engine-8.11.0.Final.jar:8.11.0.Final]

at org.teiid.query.processor.relational.XMLTableNode\$1.run(XMLTableNode.java:269) [teiid-engine-8.11.0.Final.jar:8.11.0.Final]

... 6 more

Caused by: java.lang.ClassNotFoundException: nu.xom.NodeFactory from [Module "nux.nux:main" from local module loader @16f65612 (finder: local module finder @311d617d (roots: E:\Work\Odata\JbossTeiid8\modules,E:\Work\Odata\JbossTeiid8\modules\system\layers\dv,E:\Work\Odata\JbossTeiid8\modules\system\layers\base))]

... 8 more

Thanks,

-Prabh

• 4. Re: Teiid - Consume JSON tree from REST web service

BTW, In first place I am trying to consume a single key value at each level in a tree. If this works then I will extend this to transforming multiple key values at each level.

• 5. Re: Teiid - Consume JSON tree from REST web service

> I am looking for recursive array support like a tree structure, where  top level is set of Nodes, each node will have attributes(key,value pair) + array and so on up to 2/3 level down.  Does this kind of parsing supported.

Teiid does not have support for a struct type.  So you can't parse to tree structure directly.  It would have to be an array of SQL/XML values.

> Thanks for your inputs and sorry for trouble with syntax errors. Well using the mentioned tutorial I tried to consume the JSON output, for simple JSON it works perfectly fine. But when I try to consume data as I mentioned in my Sample Output it throws below error.

What version of EAP are you deployed on?

• 6. Re: Teiid - Consume JSON tree from REST web service

Steve,

If a XPATH expression in the query points to root of XML fragment that we want to construct a STRUCT from, does that represent a SQL/XML object? I am trying to think if there is way to multi-level parse and throw results into global temp tables etc.

Ramesh..

• 7. Re: Teiid - Consume JSON tree from REST web service

> If a XPATH expression in the query points to root of XML fragment that we want to construct a STRUCT from, does that represent a SQL/XML object?

If you point to an element/sequence and the column type is XML, then you will get the whole subtree.

• 8. Re: Teiid - Consume JSON tree from REST web service

Teiid does not have support for a struct type.  So you can't parse to tree structure directly.  It would have to be an array of SQL/XML values.

-> Sorry for the confusion, I am not looking for sturct, What I mean here is a a JSON having tree like organization, like one which I attached earlier. For your reference given below

Basically my use case is 1) Consume data (in below format) from web service 2) Join it with RDBMS tables based on few attribute (e.g. Node -> key1 and Node -> ConfigNode->key1  )  3) Expose this vdb using odata interface.

I am using EAP 8.11

[

{

"Node": {

"Key1": "value1",

"Key2": 2,

"Key3": "value3",

"Key4": "value4",

"Key5": "{\"innerKey\":\"innerValue\"}",

"Key6": "Value6",

"Key7": [

"Key7Value1",

"Key7Value2"

],

"Key8": [

"Key8Value1",

"Key8Value2"

]

},

"ConfigNode": [

{

"key1": "value1",

"key2": "value2",

"key3": "{\"internalkey3\":\"internalvalue3\"}",

"key4": [

"key4value4",

"key5value5"

]

},

{

"key1": "value1",

"key2": "value2",

"key3": "{\"internalkey33\":\"internalvalue33\"}",

"key4": [

"key4value4",

"key5value5"

]

}

],

"ConnNode": [

{

"key1": "value1",

"key2": "value2"

},

{

"key1": "value1",

"key2": "value2"

}

],

"NetNode": [

{

"key1": "Value1",

"key2": "value2"

},

{

"key1": "value1111",

"key2": "value222"

}

]

},

{

"Node": {

"Key1": "value1",

"Key2": 2,

"Key3": "value3",

"Key4": "value4",

"Key5": "{\"innerKey\":\"innerValue\"}",

"Key6": "Value6",

"Key7": [

"Key7Value1",

"Key7Value2"

],

"Key8": [

"Key8Value1",

"Key8Value2"

]

},

"ConfigNode": [

{

"key1": "value1",

"key2": "value2",

"key3": "{\"internalkey3\":\"internalvalue3\"}",

"key4": [

"key4value4",

"key5value5"

]

},

{

"key1": "value1",

"key2": "value2",

"key3": "{\"internalkey33\":\"internalvalue33\"}",

"key4": [

"key4value4",

"key5value5"

]

}

],

"ConnNode": [

{

"key1": "value1",

"key2": "value2"

},

{

"key1": "value1",

"key2": "value2"

}

],

"NetNode": [

{

"key1": "Value1",

"key2": "value2"

},

{

"key1": "value1111",

"key2": "value222"

}

]

},

{

"Node": {

"Key1": "value1",

"Key2": 2,

"Key3": "value3",

"Key4": "value4",

"Key5": "{\"innerKey\":\"innerValue\"}",

"Key6": "Value6",

"Key7": [

"Key7Value1",

"Key7Value2"

],

"Key8": [

"Key8Value1",

"Key8Value2"

]

},

"ConfigNode": [

{

"key1": "value1",

"key2": "value2",

"key3": "{\"internalkey3\":\"internalvalue3\"}",

"key4": [

"key4value4",

"key5value5"

]

},

{

"key1": "value1",

"key2": "value2",

"key3": "{\"internalkey33\":\"internalvalue33\"}",

"key4": [

"key4value4",

"key5value5"

]

}

],

"ConnNode": [

{

"key1": "value1",

"key2": "value2"

},

{

"key1": "value1",

"key2": "value2"

}

],

"NetNode": [

{

"key1": "Value1",

"key2": "value2"

},

{

"key1": "value1111",

"key2": "value222"

}

]

}

]

• 9. Re: Teiid - Consume JSON tree from REST web service

> I am using EAP 8.11

8.11 is the Teiid version.  It should be deployed on EAP 6.4 alpha.  If not that could explain the nu/xom/NodeFactory exception.

> 1) Consume data (in below format) from web service

Do you need to pass parameters to invoke the web service?  If that's the case then it would likely be exposed through Teiid OData as a function, rather than as an entity.  Or are you thinking that you need Teiid to expose an entity with associations?

• 10. Re: Teiid - Consume JSON tree from REST web service

1) In version file contains this text "Red Hat JBoss Enterprise Application Platform - Version 6.4.0.Alpha1" so this is EAP 6.4 Alpha.

2) At this point I am trying with Teiid OData function, I assume this is more easier at first. But latter or in final code it would be an entity with association

-Prabh

• 11. Re: Teiid - Consume JSON tree from REST web service

> 1) In version file contains this text "Red Hat JBoss Enterprise Application Platform - Version 6.4.0.Alpha1" so this is EAP 6.4 Alpha.

You should definitely not be seeing an exception then.  nu.xom.NodeFactory is in the xom library.

> 2) At this point I am trying with Teiid OData function, I assume this is more easier at first. But latter or in final code it would be an entity with association

To be an entity with an association, you would need to have this exposed as two views/tables with a foreign key.  The simplest way to do that would be two invocations to the source web service.

• 12. Re: Teiid - Consume JSON tree from REST web service

1) I can find the xom-1.2.7.redhat-4.jar in <Jboss6.4.0Teiid8.11 Install Folder>\modules\system\layers\base\nu\xom\main, But the class loader is not able to find. I am new to Jboss and Teiid, I am not sure where to specify this explicitly. It has to do with setting correct class path.

2) "To be an entity with an association, you would need to have this exposed as two views/tables with a foreign key.  The simplest way to do that would be two invocations to the source web service."

-> To understand this, Do you have any tutorials/examples?