Using JSON subarray in subdataset to show list in detail band

0

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"
        }
      ]
    }
  ]
}

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?

dejanr's picture
5
Joined: Oct 17 2019 - 7:20am
Last seen: 1 month 4 weeks ago

What exactly does your desired output look like?

narcism - 1 month 4 weeks ago

deleted

dejanr - 1 month 4 weeks ago

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

dejanr - 1 month 4 weeks ago

1 Answer:

0

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
123-321-102 jane.smith@example.com
123-321-102 jane.smith@example.com

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's picture
5
Joined: Oct 17 2019 - 7:20am
Last seen: 1 month 4 weeks ago
Feedback
randomness