Use of JSONOPBJECT and JSONARRAY_AGG (TEIID30345 The command of this scalar subquery returned more than one value)

[cagreek]

I have a request to show Parent / Child information that could have multiple rows in the Parent tables and multiple rows in the child tables within a subquery.

I attempted the following use of JSONOPBJECT and JSONARRAY_AGG, but I am getting an error in the subquery.

TEIID30345 The command of this scalar subquery returned more than one value

can you please see what needs to be changed to allow a JSON response the allows for main customer data and drill down customer information with multiple rows in a subquery

BEGIN

SELECT JSONOBJECT( WO.main_cust_data.key_id_data,

WO.main_cust_data.CName,

WO.main_cust_data.CDate,

WO.main_cust_data.CseqNum,

JSONARRAY_AGG((SELECT JSONOBJECT(WO.cust_drill_down.COutDate,

WO.cust_drill_down.amount,

WO.cust_drill_down.CDate,

WO.cust_drill_down.key_id_data,

WO.cust_drill_down.CseqNum)

FROM WO.cust_drill_down

WHERE (WO.cust_drill_down.key_id_data = WO.main_cust_data.key_id_data)

AND (WO.cust_drill_down.CDate = WO.main_cust_data.CDate)

AND (WO.cust_drill_down.CseqNum = WO.main_cust_data.CseqNum))) AS resultDataSet) AS json_out

FROM WO.main_cust_data

WHERE WO.main_cust_data.mid = (WO.getwriteoffinfo.p_key_id_data)

GROUP BY WO.main_cust_data.key_id_data, WO.main_cust_data.CName,WO.main_cust_data.CDate, WO.main_cust_data.CseqNum;

END

[shawkins]

> I have a request to show Parent / Child information that could have multiple rows in the Parent tables and multiple rows in the child tables within a subquery.

The assumption being that each row of the result set will be one parent json object, which may have multiple children, correct?

You could either try to include the children as a correlated subquery or via a join with grouping.  You have elements of both approaches above.

As a subquery it would look like:

BEGIN

SELECT JSONOBJECT( WO.main_cust_data.key_id_data,

WO.main_cust_data.CName,

WO.main_cust_data.CDate,

WO.main_cust_data.CseqNum,

(SELECT JSONARRAY_AGG(JSONOBJECT(WO.cust_drill_down.COutDate,

WO.cust_drill_down.amount,

WO.cust_drill_down.CDate,

WO.cust_drill_down.key_id_data,

WO.cust_drill_down.CseqNum))

FROM WO.cust_drill_down

WHERE (WO.cust_drill_down.key_id_data = WO.main_cust_data.key_id_data)

AND (WO.cust_drill_down.CDate = WO.main_cust_data.CDate)

AND (WO.cust_drill_down.CseqNum = WO.main_cust_data.CseqNum)) AS resultDataSet) AS json_out

FROM WO.main_cust_data

WHERE WO.main_cust_data.mid = (WO.getwriteoffinfo.p_key_id_data);

END

Note that subquery produces the scalar array result that is then included with the parent.

As a join with grouping it would look like:

BEGIN

SELECT JSONOBJECT( WO.main_cust_data.key_id_data,

WO.main_cust_data.CName,

WO.main_cust_data.CDate,

WO.main_cust_data.CseqNum,

JSONARRAY_AGG(JSONOBJECT(WO.cust_drill_down.COutDate,

WO.cust_drill_down.amount,

WO.cust_drill_down.CDate,

WO.cust_drill_down.key_id_data,

WO.cust_drill_down.CseqNum)) AS resultDataSet) AS json_out

FROM WO.main_cust_data LEFT OUTER JOIN WO.cust_drill_down

ON (WO.cust_drill_down.key_id_data = WO.main_cust_data.key_id_data AND WO.cust_drill_down.CDate = WO.main_cust_data.CDate AND WO.cust_drill_down.CseqNum = WO.main_cust_data.CseqNum)

WHERE WO.main_cust_data.mid = (WO.getwriteoffinfo.p_key_id_data)

GROUP BY WO.main_cust_data.key_id_data, WO.main_cust_data.CName,WO.main_cust_data.CDate, WO.main_cust_data.CseqNum;

END

[cagreek]

I will look at your suggestion, but the data for the parent child could like like the following. Row 1 and 2 in the parent might not have any rows in the child, but row 3 in the parent record could have multiple child records as seen below.

Will the sql you provided handle such a use case?

Parent
key_id_data	Cname	Cdate	CseqNum
111	My Name	Jan 1 2019	2
111	My Name	Jan 1 2019	2
111	My Name	feb 2 2019	2
Child
CoutDate	amount	Cdate	key_id_data	CseqNum
jan 31 2019	1.22	Feb 2 2019	111	2
jan 31 2019	3.33	Feb 2 2019	111	2
jan 31 2019	4.44	Feb 2 2019	111	2
jan 31 2019	5.55	Feb 2 2019	111	2
Feb 1 2019	6.66	Feb 2 2019	111	2
Feb 1 2019	7.77	Feb 2 2019	111	2

[shawkins]

> Will the sql you provided handle such a use case?

Yes, a correlated subquery in the select clause is evaluated for each outer row.  An outer row will always be produced.

In the join case a left outer join is being used, which means that row a containing the parent/left side values will be produced regardless if there is a match in the ON clause.

[shawkins]

I should amend the join / group by case though.  It will produce an array with an empty object if the parent has no children, to correct that will make it a little more complicated than the subquery form.

[cagreek]

This was very helpful , I will continue to work on this.

Thank you Steven!!!

[cagreek]

Hi Steven,

In the example JSON query we've been discussing is contained in a "function". How do you expose that function in oData to run the function. If I look at the $metadata, i do not see the function called getwriteoffinfo

http://localhost:8080/odata4/WO/WO/$metadata

Please suggest what i need to do , to access the json function we've constructed below?

http://localhost:8080/odata4/WO/WO/getwriteoffinfo(p_mid='83838383838383')

BEGIN

SELECT JSONOBJECT( WO.main_cust_data.key_id_data,

WO.main_cust_data.CName,

WO.main_cust_data.CDate,

WO.main_cust_data.CseqNum,

JSONARRAY_AGG(JSONOBJECT(WO.cust_drill_down.COutDate,

WO.cust_drill_down.amount,

WO.cust_drill_down.CDate,

WO.cust_drill_down.key_id_data,

WO.cust_drill_down.CseqNum)) AS resultDataSet) AS json_out

FROM WO.main_cust_data LEFT OUTER JOIN WO.cust_drill_down

ON (WO.cust_drill_down.key_id_data = WO.main_cust_data.key_id_data AND WO.cust_drill_down.CDate = WO.main_cust_data.CDate AND WO.cust_drill_down.CseqNum = WO.main_cust_data.CseqNum)

WHERE WO.main_cust_data.mid = (WO.getwriteoffinfo.p_key_id_data)

GROUP BY WO.main_cust_data.key_id_data, WO.main_cust_data.CName,WO.main_cust_data.CDate, WO.main_cust_data.CseqNum;

END

[shawkins]

> How do you expose that function in oData to run the function. If I look at the $metadata, i do not see the function called getwriteoffinfo

See the OData Metadata section of OData Version 4.0 Support · GitBook

A procedure has to meet certain conditions to be an OData function, otherwise it is represented as an OData action.  However currently neither supports a LOB result set column - which I'm guessing is they way you have this represented.  Since it looks like it will only ever return 1 row, you could change that to just a json return value rather than a result set.

create vitrual procedure getwriteoffinfo(p_mid string) returns json as

begin

   return jsonParse((SELECT JSONOBJECT...), true);

end

Note the jsonParse function is not needed after [TEIID-5728] JSON production functions JSONOBJECT and JSONARRAY_AGG producing CLOBs - JBoss Issue Tracker

> Please suggest what i need to do , to access the json function we've constructed below?

An alternative approach would be to create specific views for this and let the odata layer handle the json construction.  What you are doing in the procedure looks roughly like:

create view writeoff_main (

mid string primary key,

key_id_data long,

CName string,

CDate date,

CseqNum long,

unique (key_id_data, CDate, CseqNum),

accesspattern(mid)

) as select mid, key_id_data, CName, CDate, CseqNum from WO.main_cust_data;

create view writeoff_drill_down (

id long primary key,

COutDate date,

amount decmial,

CDate date,

key_id_data long,

CseqNum long,

foreign key (key_id_data, CDate, CseqNum) references writeoff_main (key_id_data, CData, CseqNum)) as select id, COutDate, amount, CDate, key_id_data, CseqNum from WO.cust_drill_down;

These will actually be entities in your odata model, and could have both the parent and the child included in a result using OData expand.  Since they are entities they could be more generally queried, so this isn't an option for you if you want to restrict things to just querying on a single parent id.

[cagreek]

Getting closer,

Thank you for all your help so far. I like the idea of using oData to produce the Parent/Child data in a JSON response. I tried adjusting my current table views to include the primary key/ foreign key relationship but can't seem to get the http://localhost:8080/odata4/WO/WO/$metadata  to show the "Navigation Property" as described from this documentation here:

OData Version 4.0 Support · GitBook

Can you please suggest what i need to do to get the $expand feature to work with the oData URL i'm trying to construct.

If you have any example or sample project on git that has this concept working, that would greatly help guide me.

Thank you

Andy

[shawkins]

> I tried adjusting my current table views to include the primary key/ foreign key relationship but can't seem to get the http://localhost:8080/odata4/WO/WO/$metadata  to show the "Navigation Property" as described from this documentation here

Can you provide the Teiid ddl and/or the OData metadata you are seeing as a result?

> If you have any example or sample project on git that has this concept working, that would greatly help guide me.

If you already have your entities represented correctly, you should only need to add a foreign key.  You can see several examples in our integration tests, like teiid/TestODataIntegration.java at df5b2d04133fe7fa1c87294fd896df15af340b43 · teiid/teiid · GitHub - Note the DDL is fairly straight-forward, then you can see some of the variety of OData queries that can be issued against that.  Other test cases look at more complex expand scenarios.

If you are familiar with Spring Boot, or you could also have a look teiid-spring-boot/samples/odata at master · teiid/teiid-spring-boot · GitHub that uses an entity model with several relationships.

[cagreek]

This is a project for work so i can only show limited information, but what i found interesting is that the JBOSS Data Virtualization Teiid Designer shows the Foreign key but when i attempt to export the DDL, i do not see the foreign key.  I only see the Access pattern?   

CREATE VIEW procSiaDrillDown (

outclearingDate date,

amount bigdecimal,

methodOfPayment string(15),

seqNum bigdecimal,

caseDate date,

mid string(16),

CONSTRAINT mid_AP ACCESSPATTERN(mid)

) OPTIONS(UPDATABLE 'TRUE')

AS

Thanks Andy

[rareddy]

ACCESSPATTERN is not Foreign Key constraint.  You have to define the relationship between those two tables then you will see it.

[cagreek]

I finally got this to work.

For other developers that might have the same issue, make sure your Primary Key and Foreign Keys are setup correctly. Also insure, primary key's are "unique, not null-able".

I attempted to use oData $expand for multiple foreign key links and i am getting the following error.

{"error":{"code":null,"message":"TEIID16042 Multiple $expand nodes are not supported, for ex: /customer?$expand=orders,accounts"}}

Any plans from JBOSS/TEIID community to included multiple navigation properties as described below?

Using $select, $expand, and $value in ASP.NET Web API 2 OData - ASP.NET 4.x | Microsoft Docs

[shawkins]

> Any plans from JBOSS/TEIID community to included multiple navigation properties as described below?

What version of teiid are you using?  Support was added a while ago: [TEIID-4100] Full odata expand support - JBoss Issue Tracker

[cagreek]

Wow, Steven fast response!!!

I am using EAP 6.1, TEIID 8.12.11. Is that the right place to look using the JBOSS designer?