Using JSON subarray in subdataset to show list in detail band

[dejanr]

Hi all,

I am having subreport with JSON data source. I am using jsonql language for query as it has more options than normal json.
Here is the example structure of JSON:

{  "users": [    {      "firstName": "John",      "lastName": "Doe",      "contact": [        {          "phone": "111-222-333",          "email": "john.doe@example.com",          "type": "business"        },        {          "phone": "121-212-654",          "email": "john.doe.private@example.com",          "type": "private"        }      ]    },    {      "firstName": "Jane",      "lastName": "Smith",      "contact": [        {          "phone": "123-321-102",          "email": "jane.smith@example.com",          "type": "business"        },        {          "phone": "123-321-102",          "email": "jane.smith@example.com",          "type": "private"        }      ]    }  ]}[/code]

My main dataset query is "users.*". and fields are "firstName" and "lastName".
I would like to show contact details (array of contacts) in a list element as third column in the report.
I am having issues rendering contact data in list that way.
I create subdataset "ContactDataset", in "Dataset and Query Dialog" I get fields "phone", "email" and "type". When I go to data preview I can actually see those data.
When I add list to report I can set it's dataset "ContactDataset" and in editor add fields to list itself.
But, when I try to run preview I don't get anything.
I tried setting list's Dataset "Use a JRDatasource expression" property to "((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("users.contact.*")" but it does not work.
Does anyone have any idea how to use JSON object subarray ("contact") to show list element in detail band?
 

[narcism]

What exactly does your desired output look like?

[dejanr]

Hmmm...I've added tables here in response to make it look good, but it is just rubbish now...

[dejanr]

Two rows:

First name	Last name	Contact
John	Doe	111-222-333 john.doe@example.com 121-212-654 john.doe.private@example.com
Jane	Smith

Also, I've solved it...I am pretty sure I've tried it on Friday, but all I've needed to set is  list's Dataset "Use a JRDatasource expression" property to

((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("contact")

[dejanr]

deleted

[rajil]

I am using API call to get JSON object from server.  Noticed that subDataSource calls API 2nd time to get details.  How can I use existing data set instead of calling API twice?