How to pull master and details in a single json file

[pmthirumaran]

I would like to pull the json data using Teiid JDV like the following

{"college":"Department",

"resultList":[{"id":"01","name":"Math"},{"id":"02","name":"Chemistry"}],"errorMsg":"No Error"}

any sample reference in teiid?

Thanks in advance

[shawkins]

You'll want to use json construction functions: JSON Functions · GitBook

You can adapt the JSONARRAY_AGG.  Something like:

SELECT JSONOBJECT('Department' as college, JSONARRAY_AGG(JSONOBJECT(id, name)) as resultList) FROM departments

If it's from a join, then you can group by the master and aggregate the child.

[pmthirumaran]

Thanks for your reply.

My complete senario is

In the EBL layer (xmi), the master data called Merchant (combination of 2 tables) and the detail called Equipment (combination of 4 tables) from here I have to combine both create a new table called customerinfo which is going to combination of  Merchant and Equipment together as a single record (1: many relationships to show the output as a json response)  a. How can I implement this?

[rareddy]

CustomerInfo is what you are after building then why do you need the JSON building stuff. If you want to combine Merchant and Equipment to create CustmerInfo can you not use a JOIN to bring them together?

[shawkins]

The general pattern with a join looks very similar to the example above:

SELECT JSONOBJECT(master.name, JSONARRAY_AGG(JSONOBJECT(detail.id, detail.name)) as resultList) FROM master left outer join detail on master.id = detail.master_id group by master.name

This would produce 1 json document per master row with an array of detail objects.

[luisgaffie]

I have the same problem..

[shawkins]

Luis did you try SQL like was shown in my previous comment?

[pmthirumaran]

This is from my transformer editor

BEGIN

SELECT JSONOBJECT(devep_EBL.MERCH_MID.mid, devep_EBL.MERCH_MID.vid, devep_EBL.MERCH_MID.sourceSystemRef, JSONARRAY_AGG((SELECT JSONOBJECT(devep_EBL.EQUIPMNT_MID.terminalId, devep_EBL.EQUIPMNT_MID.model, devep_EBL.EQUIPMNT_MID.itemCd, devep_EBL.EQUIPMNT_MID.terminalBin) FROM devep_EBL.EQUIPMNT_MID WHERE devep_EBL.EQUIPMNT_MID.mid = devep_EBL.MERCH_MID.mid)) AS resultList) AS json_out FROM devep_EBL.MERCH_MID WHERE devep_EBL.MERCH_MID.mid IN (devep.getcustomerinfo.p_mid) GROUP BY devep_EBL.MERCH_MID.mid, devep_EBL.MERCH_MID.vid, devep_EBL.MERCH_MID.sourceSystemRef;

END

When I run it through Preview Data it is getting the following result

{"mid":"123456","vid":null,"sourceSystemRef":null,"resultList":[{"terminalId":"0008024977533258","model":"CONVERGE NEW GENERATION","itemCd":"CNVNG","terminalBin":"001734"}]}

When I call it from URL

http:localhost:8080/odata4/developmentportal/devep/getcustomerinfo(p_mid=8024977533)

The following error I am getting

Cannot find EntitySet, Singleton, ActionImport or FunctionImport with name 'getcustomerinfo'.

How to resolve this issue?

[shawkins]

This is essentially the same situation as: Use of  JSONOPBJECT and JSONARRAY_AGG (TEIID30345 The command of this scalar subquery returned more than one value)

If you are doing this through odata, you should consider using an entity representation rather than a procedure.  As a procedure there currently is a limitation that it cannot return a result set of lob values (json / clob in this case).  You can instead return a single value as a workaround.