Search code examples
jasper-reports

Jasper Json nested lists

I'm new in Jasper reports, so I'll write my whole task, if it's allowed..

I'm trying to parse json data to print in it PDF. Data is coming from oracle DB, written in CLOB column, so I have list of json-clob records.

I have nested lists json (and clob) data, so I can't build 3 stair nested report with my jasper experience. I have json structure like this:

{
  title: 'Main Title',
  blocks: [{
    title: 'Inner Title',
    items: [{
      key: '1',
      value: 'some text'
    }, {
      key: '2',
      value: 'some other text'
    }]
  }, {
    text: 'here may be other fields (text, not title)'
  }]
}

For this json data, I want to get this result in PDF (just simple output):

Main Title
Inner Title
1 some text
2 some other text
here may be other fields (text, not title)

Firstly, I use CLOB converting in json source ( script:

< dataSourceExpression >
  < ![CDATA[new net.sf.jasperreports.engine.data.JsonQLDataSource(new ByteArrayInputStream($F{JSON_CLOB_RECORD}.getBytes("UTF-8")))]] >
< /dataSourceExpression >

source: nested jasper subreports with json datasource ).

After that, I use jr:list and textfield to print main titles. And I'm done, don't know how to continue.

I searched nested lists (jr:list) but nothing, can't fit to my task.

P.S. I have subDataset for parsing data from CLOB to json with fields title (java.lang.String) and blocks (I tried here java.util.Collection and ArrayList but errors occurred, than I wrote java.lang.Object. Is it necessary to write blocks field in this subDataset?). I also added subDataset for blocks list, is it needed? And if yes, so I have to add items subDataset too, right? Also, I tried to add subReport after main title textfield, for blocks list and added dataSourceExpression but can't continue.

Need some help.

Solution

  • I would suggest using a subreport, instead of a list or nested lists, because you gain more flexibility in this case.

    In the main report you should discard the list element and the dataset and use a subreport, something like:

    <subreport>
    <reportElement x="0" y="0" width="550" height="50" uuid="37861aca-d444-4aec-ad03-baa310540327"/>
    <subreportParameter name="JSON_INPUT_STREAM">
        <subreportParameterExpression><![CDATA[new ByteArrayInputStream($F{JSON_CLOB_RECORD}.getBytes("UTF-8"))]]></subreportParameterExpression>
    </subreportParameter>
    <subreportExpression><![CDATA["Subreport.jasper"]]></subreportExpression>
</subreport>

    Here we only need the JSON_INPUT_STREAM so that the subreport will construct a dataSource based on the query and its language.

    Case 1. If you are mostly interested in the items key, then the subreport(Subreport.jrxml) could be pretty basic like so:

    <?xml version="1.0" encoding="UTF-8"?>
<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="Subreport" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="a3dd10f8-b28c-4c11-b582-9b169e8aa417">
    <queryString language="jsonql">
        <![CDATA[..items.*]]>
    </queryString>
    <field name="mainTitle" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="$.title"/>
    </field>
    <field name="key" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="key"/>
    </field>
    <field name="value" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="value"/>
    </field>
    <field name="blockTitle" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="^^.title"/>
    </field>
    <background>
        <band splitType="Stretch"/>
    </background>
    <detail>
        <band height="90" splitType="Stretch">
            <textField>
                <reportElement isPrintRepeatedValues="false" x="0" y="30" width="100" height="30" isRemoveLineWhenBlank="true" uuid="172de051-3d2c-4458-b01e-ab06e4e1bb3b"/>
                <textFieldExpression><![CDATA[$F{blockTitle}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement isPrintRepeatedValues="false" x="0" y="0" width="100" height="30" isRemoveLineWhenBlank="true" uuid="94f57756-a5a8-402f-ad23-7cdbe2a00e59"/>
                <textFieldExpression><![CDATA[$F{mainTitle}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="60" width="100" height="30" uuid="7dbbdfd8-5a0e-4288-ae39-21f13cd4d921"/>
                <textFieldExpression><![CDATA[$F{key} + " " + $F{value}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

    Notice the isPrintRepeatedValues="false" and the isRemoveLineWhenBlank="true" properties that remove the duplicates and the lines so that you get the desired output.

    I've simulated your case with a serialized JSON and got the following: sample output

    Case 2. If you are interested in all the blocks object data you would have to land on the blocks item in your query and create a list/subreport for the items key, something like:

    <?xml version="1.0" encoding="UTF-8"?>
<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="a3dd10f8-b28c-4c11-b582-9b169e8aa417">
    <subDataset name="BlockItemsDataset" uuid="83c1b8ed-e880-4474-a809-39d95277341f">
        <field name="key" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="key"/>
        </field>
        <field name="value" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="value"/>
        </field>
    </subDataset>
    <queryString language="jsonql">
        <![CDATA[blocks]]>
    </queryString>
    <field name="mainTitle" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="$.title"/>
    </field>
    <field name="blockTitle" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="title"/>
    </field>
    <background>
        <band splitType="Stretch"/>
    </background>
    <detail>
        <band height="91" splitType="Stretch">
            <textField>
                <reportElement isPrintRepeatedValues="false" x="0" y="0" width="100" height="30" isRemoveLineWhenBlank="true" uuid="94f57756-a5a8-402f-ad23-7cdbe2a00e59"/>
                <textFieldExpression><![CDATA[$F{mainTitle}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement isPrintRepeatedValues="false" x="0" y="30" width="100" height="30" isRemoveLineWhenBlank="true" uuid="172de051-3d2c-4458-b01e-ab06e4e1bb3b"/>
                <textFieldExpression><![CDATA[$F{blockTitle}]]></textFieldExpression>
            </textField>
            <componentElement>
                <reportElement x="0" y="61" width="200" height="30" uuid="7a94d84d-d58e-4508-b143-3704fa7f5cd3"/>
                <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="BlockItemsDataset" uuid="a866f955-7594-4fb9-9bb2-936282963dea">
                        <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("items")]]></dataSourceExpression>
                    </datasetRun>
                    <jr:listContents height="30" width="200">
                        <textField>
                            <reportElement x="0" y="0" width="200" height="30" uuid="36a771fa-f949-4c69-a62c-1df0c610a2d3"/>
                            <textFieldExpression><![CDATA[$F{key} + " " + $F{value}]]></textFieldExpression>
                        </textField>
                    </jr:listContents>
                </jr:list>
            </componentElement>
        </band>
    </detail>
</jasperReport>