Jump to content
Changes to the Jaspersoft community edition download ×

Traversing JSON output from REST API datasource


Recommended Posts

I have used the Web Service Data Adapter to connect to a REST API as a datasource for my report.  The output is in JSON format.  My question is what syntax do I use to be able to traverse through the nodes in the JSON file?  Am I able to skip a node using a JSONPATH expression?

I've followed the tutorials on https://community.jaspersoft.com/project/web-service-data-source and have successfully managed to get the api to run, but not able to read the input fields as they are not returned in an array.

I am trying to read the tgaCode (highlighted below):


Link to comment
Share on other sites

  • Replies 17
  • Created
  • Last Reply

Top Posters In This Topic

I am not sure about the Web Service Data Source's case since it is not officially supported by TIBCO, as stated in the link you provided. The reason I asked about you being tied to a specific version is because starting with Jaspersoft Studio v6.4.0 you should be able to achieve similar functionality with the built-in mechanisms that reside inside the JasperReports Library on which Studio relies. It is about the JSON Data Adapter(that supports URL parameters, custom headers, HTTP POST/GET/PUT) which used in conjuntion with JSONQL could easily solve your problem. I could only try and guide you towards that solution if that is fine with you.

Link to comment
Share on other sites

Starting with TIBCO Jaspersoft® Studio v6.4.0 you could safely use the JSON Data Adapter to achieve similar functionality that you get with Web Service Data Adapter.

The main steps are:

  1. Create the JSON Data Adapter:

    • Either create one,

      • in the Repository Explorer view(Clik on the first icon in the top-right corner),

      • or, directly in one of your project's folder(Right Click on the folder -> New -> Data Adapter).

    • In the Data Adapter Wizard, you choose the JSON File Data Adapter.

    • Give it a name and paste your JSON API URL in the File/URL input field.

    • Once you have pasted/written the URL the File button will turn into an Options button where you could refine your API call(authentication, HTTP verbs and headers, URL parameters).

    • Check the "Use the report JSON expression when filling the report" to have more flexibility when writing the JSONQL query.

  2. Create the Report without specifying the newly created adapter.

  3. Open the Dataset and Query Dialog(Click the first icon in the top-right corner of your Design tab):

    •  Pick your JSON Data Adapter in the top-left corner of the Dialog.

    •  Choose jsonql as the language as the JSON one is too limited in functionality.

    •  Write a jsqonql query as explained in the next step.

  4. Write a query in the dedicated input field. There are more options when doing this and some depend on the structure of your JSON response from the URL call:

    •  If the "tgaCode" is unique across the whole JSON, and you want only that as a field,  you could simply write this query: "..tgaCode" (without quotes). This means select the tgaCode key from everywhere. Then click the Read Fields button and notice only one field with the "[0]" expression. You could rename the field name at this point to be more appropriate.

    • If the "tgaCode" is not unique across the entire JSON you could write: "qualificationDtoMap..tgaCode" to give it a context to start from.

    •  If you want other keys besides tgaCode you could write: "..[id, tgaCode, tgaTitle]" or "qualificationDtoMap..[id, tgaCode, tgaTitle]".

These are just some of the options to write a query for this specific case. If you want to read more about JSONQL you could check out http://jasperreports.sourceforge.net/sample.reference/jsonqldatasource/index.html#jsonql


All the options in the Data Adapter can be dinamically set through the means of properties set to report parameters.

For instance, if you need to customize the URL endpoint to have it resemble https://jsonplaceholder.typicode.com/todos/{id} you could:

  • leave it empty in the adapter

  • add these report-level parameters:

<parameter name="todoId" class="java.lang.Integer">    <defaultValueExpression><![CDATA[7]]></defaultValueExpression></parameter><parameter name="theUrl" class="java.lang.String">    <property name="net.sf.jasperreports.http.data.url"/>    <defaultValueExpression><![CDATA["https://jsonplaceholder.typicode.com/todos/" + $P{todoId}]]></defaultValueExpression></parameter>[/code]

The other parameters that could be set this way could be found here:


Link to comment
Share on other sites

1. Make sure you have a Data Adapter XML file in your workspace. You can export the one from Repository Explorer if you chose that path. Let's assume you named it JSON_DA.xml and placed it in the same folder with your report.


2. Link it to the report either:

- by manually setting this property:

- or by setting the Default Data Adapter in the Report Properties tab to point to your XML file


3. JasperSoft Studio should pick it up automatically this way when publishing to server


Link to comment
Share on other sites

I am getting a NullPointerException when trying to preview the report after setting the "net.sf.jasperreport.data.adapter" value to my xml file. Could it be because I have blanked out the url in the data adapter and am using the "net.sf.jasperreports.http.data.url" property to dynamically set the url?



net.sf.jasperreports.engine.JRException: java.lang.NullPointerException

at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:536)

at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$17(ReportController.java:511)

at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:429)

at org.eclipse.core.internal.jobs.Worker.run(Worker.java:56)

Caused by: java.lang.NullPointerException

at net.sf.jasperreports.eclipse.util.HttpUtils$1.determineRoute(HttpUtils.java:57)

at org.apache.http.impl.client.InternalHttpClient.determineRoute(InternalHttpClient.java:124)

at org.apache.http.impl.client.InternalHttpClient.doExecute(InternalHttpClient.java:183)

at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:82)

at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:107)

at net.sf.jasperreports.data.http.HttpDataConnection.getInputStream(HttpDataConnection.java:67)

at net.sf.jasperreports.data.DataFileStream.(DataFileStream.java:47)

at net.sf.jasperreports.data.DataFileUtils.getDataStream(DataFileUtils.java:79)

at net.sf.jasperreports.data.json.JsonDataAdapterService.contributeParameters(JsonDataAdapterService.java:75)

at net.sf.jasperreports.engine.fill.JRFillDataset.contributeParameters(JRFillDataset.java:1153)

at net.sf.jasperreports.engine.fill.JRFillDataset.setParameterValues(JRFillDataset.java:665)

at net.sf.jasperreports.components.list.FillDatasetRun.start(FillDatasetRun.java:164)

at net.sf.jasperreports.components.list.VerticalFillList.prepare(VerticalFillList.java:102)

at net.sf.jasperreports.engine.fill.JRFillComponentElement.prepare(JRFillComponentElement.java:152)

at net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:542)

at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:453)

at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:428)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillTitle(JRVerticalFiller.java:325)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:256)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:110)

at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:615)

at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135)

at java.lang.Thread.run(Thread.java:748)


Also the published .xml file was renamed to .xml__6378735146977274512. Is this what it should do? Should the report also be pointing to this xml when I look at the Data Source for this report on the server?


Link to comment
Share on other sites

Yes, that NPE is caused by that. Setting the Evaluation Time to "Early" for each parameter involved in URL creation should fix this.

Regarding the XML file name, I'm not sure why there's no control over the naming, but the report will be linked to it if. You could check that by opening it from the Repository Explorer view instead of the workspace.

Link to comment
Share on other sites

Thanks for your help so far @narcism. I've set the net.sf.jasperreport.data.adapter to my xml file and all parameters involved in the URL creation to "Early" but am still getting the NPE when previewing the report in jasperstudio. I'm running version 6.6.0 community edition. The report previews successfully when I have not set "net.sf.jasperreport.data.adapter" to my xml file, but use "com.jaspersoft.studio.data.defaultdataadapter" to access the same xml file in my workspace.
Link to comment
Share on other sites

  • 1 year later...
  • 8 months later...

I added new parameters within report e.g

<parameter name="ENDPOINT_URL" class="java.lang.String" isForPrompting="false" evaluationTime="Early">
        <property name="net.sf.jasperreports.http.data.url"/>

On XML Adapter, I removed url element.

And everything works perfect.

Link to comment
Share on other sites

  • 1 year later...
  • 7 months later...

How to override the header value dynamically.


As I can only see "net.sf.jasperreports.http.data.header" in the constants. I am assuming this property will set the name of the header. If I want to set the value of the header, what do I need to do?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...