A recipe (and a question) on using JSONQL and Javascript

[srhaque]

I thought it might be helpful to write up how to use JSONQL and Javascript to "walk" a JSON datasource since I at least found this rather tricky.

First, the key documentation seems to be The JSONQL query language and also the rather minimal Using JavaScript as a Language for Expressions. There are of course a number of helpful other articles but I did not find anything that made it clear how to do the kind of data walks/joins I needed to do. A small extract of my data illustrates the issue:

{    "employee": [{"title": 132, "name": "Fred", ...}, {}, {}, {}...],    "title": [{"id": 132, "text": "Mr.", ...}, {}, {}, {}...],}[/code]

and the requirement is that while walking "employee", I need to look up the "Mr." corresponding to "title" 132 so I can emit "Mr. Fred". Most documentation might tend to suggest the main dataset might use a query string of "employee" (or maybe "employee.*"...I'm not 100% clear on the difference!). The problem I had understadning how to do the walk was solved by using am *empty* main data set query string along with a single field definition corresponding to the top of the report:

   <queryString language="jsonql">        <![CDATA[]]>    </queryString>    <field name="top" class="java.lang.Object">        <propertyExpression name="net.sf.jasperreports.jsonql.field.expression"><![CDATA["$"]]></propertyExpression>        <fieldDescription><![CDATA[Top level object]]></fieldDescription>    </field>[/code]

Note how the type is java.lang.Object, not a more common simple type (more anon). Then, for each subDataSet, I'd specify the fields of interest, plus an identical $F{top}:

    <subDataset name="Employees" uuid="114e4562-7ddd-4e31-85d2-eb8177c7fbd1">        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JSON data adapter"/>        <queryString language="jsonql">            <![CDATA[employee]]>        </queryString>        <field name="name" class="java.lang.String">            <property name="net.sf.jasperreports.json.field.expression" value="name"/>            <fieldDescription><![CDATA[name]]></fieldDescription>        </field>...        <field name="title" class="java.lang.Integer">            <property name="net.sf.jasperreports.json.field.expression" value="title"/>            <fieldDescription><![CDATA[title]]></fieldDescription>        </field>        <field name="top" class="java.lang.Object">            <propertyExpression name="net.sf.jasperreports.jsonql.field.expression"><![CDATA["$"]]></propertyExpression>            <fieldDescription><![CDATA[Top level object]]></fieldDescription>        </field>    </subDataset>[/code]

This approach means that $F{top} can be used anywhere one likes to do things like this:

      <datasetRun subDataset="Employees" uuid="8771c862-35e8-4835-8e44-89acf7016e02">          <dataSourceExpression><![CDATA[$P{REPORT_DATA_SOURCE}.subDataSource("employee.*")]]></dataSourceExpression>      </datasetRun>      <jr:listContents height="46" width="380">          <textField>              <reportElement x="0" y="0" width="70" height="30" uuid="34f5ebdd-08b6-478d-b281-e1fc47276717"/>              //              // Normal stuff              //              <textFieldExpression><![CDATA[$F{name}]]></textFieldExpression>          </textField>          <textField>              <reportElement x="70" y="0" width="100" height="30" uuid="7a2569f5-7487-4f8e-8e53-70c104159290"/>              //              // Combine $F{title} with $F{top} to "join" the "title" array.              //              <textFieldExpression><![CDATA[$F{top}.get("title").get(java.util.Arrays.binarySearch($F{top}.get("title").findValuesAsText("id").toArray(), "" + $F{title})).get("title").asText() + " " + $F{name}]]></textFieldExpression>          </textField>      </jr:listContents>[/code]

This works because the result of the java.lang.Object lookup is in fact a com.fasterxml.jackson.databind.node.ObjectNode, which has the predictable friends TextNode, and ArrayNode etc. Now that look pretty horrid (but it is in fact quite simple in its parts), so the question is could this have been expressed more cleary using ES6's Array.from() and .filter() etc? Or put another way: what version of JS does Jaspersoft support? That's my question too...

Also, any suggestions on better ways to do the above are most welcome.

Thanks, Shaheed

P.S. I also tried ES5's indexOf, and got confusing results. Then again, I've forgotten what little I ever knew of JS and Java, let alone Jaspersoft.

 

 

 

[srhaque]

Saving as rich text seems broken...I had to type the whole thing twice already. So all the bolding and the URLs are gone...

[narcism]

Regarding *:

The wildcard operator is described in the documentation link that you posted. It can be applied to both array and object keys and always produces array structures.

 

Regarding the JavaScript version:

Jasperreports relies on Mozilla's Rhino JavaScript engine written in Java, so whatever version they implement that's what we use.

 

Another way to link to the corresponding "title" would imply traveling up the JSON tree and, in a pretty basic usage, could be done like this :

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.6.0  --><jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Report" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="1ba5dc3f-d678-4af6-9d1f-e1c2734b802e">    <subDataset name="TitleDS" uuid="9cece070-b115-4ec6-b614-5f193cbfaced">        <field name="text" class="java.lang.String">            <property name="net.sf.jasperreports.json.field.expression" value="text"/>        </field>    </subDataset>    <queryString language="jsonql">        <![CDATA[employee]]>    </queryString>    <field name="titleId" class="java.lang.Integer">        <propertyExpression name="net.sf.jasperreports.jsonql.field.expression"><![CDATA["title"]]></propertyExpression>    </field>    <field name="name" class="java.lang.String">        <property name="net.sf.jasperreports.jsonql.field.expression" value="name"/>    </field>    <background>        <band splitType="Stretch"/>    </background>    <title>        <band height="79" splitType="Stretch">            <staticText>                <reportElement x="180" y="20" width="200" height="30" uuid="d41e59d6-4e42-4acb-bbcf-0a53dc7317bd"/>                <textElement textAlignment="Center" verticalAlignment="Middle">                    <font size="16"/>                </textElement>                <text><![CDATA[JSONQL Filter]]></text>            </staticText>        </band>    </title>    <pageHeader>        <band height="35" splitType="Stretch"/>    </pageHeader>    <columnHeader>        <band height="30" splitType="Stretch">            <staticText>                <reportElement mode="Opaque" x="0" y="0" width="80" height="30" forecolor="#000000" backcolor="#ACFCF6" uuid="92e4f28e-99e1-41c8-ab50-ceb655d4ae69">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="6e722783-0c23-4671-9044-56acf1c015e6"/>                </reportElement>                <text><![CDATA[Title]]></text>            </staticText>            <staticText>                <reportElement mode="Opaque" x="80" y="0" width="130" height="30" forecolor="#000000" backcolor="#ACFCF6" uuid="db4e5576-b19c-447a-9a82-de9cf5f9c392">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="aa44f7ad-f18b-4174-8d13-3be39660f86e"/>                </reportElement>                <text><![CDATA[Name]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="30" splitType="Stretch">            <textField>                <reportElement x="80" y="0" width="130" height="30" uuid="ce964b5e-9330-4799-b0d1-2367e9a7b1d4">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="aa44f7ad-f18b-4174-8d13-3be39660f86e"/>                </reportElement>                <textFieldExpression><![CDATA[$F{name}]]></textFieldExpression>            </textField>            <componentElement>                <reportElement x="0" y="0" width="80" height="30" uuid="c54586b0-ff78-4a95-be32-bee9d7228c25"/>                <jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical">                    <datasetRun subDataset="TitleDS" uuid="2e473887-cab2-46cd-bd68-4862539a1ec7">                        <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("^{2}.title.*(id == " + $F{titleId} + " )")]]></dataSourceExpression>                    </datasetRun>                    <jr:listContents height="30" width="80">                        <textField>                            <reportElement x="0" y="0" width="80" height="30" uuid="92827326-d0e8-4cb7-82ac-76e91fdde255"/>                            <textFieldExpression><![CDATA[$F{text}]]></textFieldExpression>                        </textField>                    </jr:listContents>                </jr:list>            </componentElement>        </band>    </detail></jasperReport>[/code]

 

[srhaque]

Thanks for the (quick!) answers...

On the "*", thanks.

On the JS version, yes, I worked out that you have Rhino. I'm not totally clear on what that means, and anyway, it is old. I guess the obvious ask would be if a more modern version might be a possibility (e.g. I tried ES6 syntax, and it was a no-go).

On the "title", the problem with the "standard" solution you propose is that I could not work out how to combine $F{text} with $F{name} to allow a textfield containing '$F{text} + " " + $F{name}', since in this model, these two are associated with distinct datasets. The idea of my $F{top} is to sidestep this problem by allowing for '$F{text} + " " + $F{top}.get(...).....asText()'. The rather horrendous expression required to do the array lookup is the prime motivation for a newer JS.

Note that I actually rearrange my JSON to use dicts instead of arrays by transforming

[{"id": 1, ...}, {"id": 444, }, ...][/code]

into dicts keyed by the id:

{"1": {"id": 1, ...}, "444": {"id": 444, }, ...}[/code]

to eliminate the array lookup and replace this:

$F{top}.get("title").get(java.util.Arrays.binarySearch($F{top}.get("title").findValuesAsText("id").toArray(), "" + $F{title})).get("title").asText() + " " + $F{name}[/code]

with this:

$F{top}.get("title").get("" + $F{title}).get("title").asText() + " " + $F{name}[/code]

which is, ahem, a bit nicer.

[narcism]

Working on the same example that I proposed, you could pass the name as a parameter to the list's dataSet. Here's what you should have instead:

The subDataset definition:

<subDataset name="TitleDS" uuid="9cece070-b115-4ec6-b614-5f193cbfaced">    <parameter name="pName" class="java.lang.String"/>    <field name="text" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression" value="text"/>    </field></subDataset>[/code]

The list:

<jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical">    <datasetRun subDataset="TitleDS" uuid="4b9df06f-9058-4a75-a704-207d0b3956ed">        <datasetParameter name="pName">            <datasetParameterExpression><![CDATA[$F{name}]]></datasetParameterExpression>        </datasetParameter>        <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("^{2}.title.*(id == " + $F{titleId} + " )")]]></dataSourceExpression>    </datasetRun>    <jr:listContents height="30" width="80">        <textField>            <reportElement x="0" y="0" width="80" height="30" uuid="92827326-d0e8-4cb7-82ac-76e91fdde255"/>            <textFieldExpression><![CDATA[$F{text} + " " + $P{pName}]]></textFieldExpression>        </textField>    </jr:listContents></jr:list>[/code]

and of course, you need to adjust the column header and the detail band to keep only what is relevant.

[srhaque]

Got it, thanks. So the fundamental tradoff between the two approaches is whether the complexity of the "walk" is in the subDataSet JSONQL setup, or the textField Java/Groovy/Javascript usage. I guess for different uses cases, either might be preferred.

The subDataSet feels more "declarative" in nature, and the textField is more towards the Turing complete end of the spectrum (especially if you guys upgrade the JS version :-)).

[srhaque]

I see from Google that Rhino is effectively dead. Is there a plan to get to a newer version of JS? I see that there is now "standard" support in Oracle's setup, but am not sure of the wider picture.