JSON array as table

[marc.roth]

Hi there,

I need to load some detail data from a JSON array to a table. All tips I've already tried failed, so I hope someone out there can help me.
Here's the JSON
 

{    "Results": [{            "Part_name": "PART1",            "BOM_level": 2,            "quantity": 0.025,            "status": "ok",            "uom": "KG",            "proofs": [{                    "proofType": "TYPE1",                    "proofName": "PROOF1",                    "proofFile": null                },{                    "proofType": "TYPE1",                    "proofName": "PROOF2",                    "proofFile": null                }            ],            "Part_ID": 56031        }, {            "Part_name": "PART2",            "BOM_level": 4,            "quantity": 0.55,            "status": "ok",            "uom": "KG",            "proofs": [{                    "proofType": "TYPE1",                    "proofName": "PROOF3",                    "proofFile": null                }            ],            "Part_ID": "11011_82974"        }, {            "Part_name": "PART3",            "BOM_level": 5,            "quantity": 1.0,            "status": "ok",            "uom": "STK",            "proofs": [{                    "proofType": "TYPE1",                    "proofName": "PROOF4",                    "proofFile": null                }            ],            "Part_ID": "11011_82974"        }, {            "Part_name": "PART4",            "BOM_level": 2,            "quantity": 1.0,            "status": "ok",            "uom": "STK",            "proofs": [{                    "proofType": "TYPE1",                    "proofName": "PROOF5",                    "proofFile": null                }            ],            "Part_ID": 4570785        }, {            "Part_name": "PART5",            "BOM_level": 2,            "quantity": 0.1,            "status": "ok",            "uom": "STK",            "proofs": [{                    "proofType": "TYPE1",                    "proofName": "PROOF6",                    "proofFile": null                }            ],            "Part_ID": 99110        }, {            "Part_name": "PART6",            "BOM_level": 2,            "quantity": 0.075,            "status": "ok",            "uom": "STK",            "proofs": [{                    "proofType": "TYPE1",                    "proofName": "PROOF7",                    "proofFile": null                }            ],            "Part_ID": 99987        }, {            "Part_name": "PART7",            "BOM_level": 4,            "quantity": 6.0,            "status": "ok",            "uom": "STK",            "proofs": [{                    "proofType": "TYPE1",                    "proofName": "PROOF8",                    "proofFile": null                }            ],            "Part_ID": "1620051_72934"        }, {            "Part_name": "PART8",            "BOM_level": 5,            "quantity": 1.0,            "status": "ok",            "uom": "STK",            "proofs": [{                    "proofType": "TYPE1",                    "proofName": "PROOF9",                    "proofFile": null                }            ],            "Part_ID": "1620051_72934"        }, {            "Part_name": "PART9",            "BOM_level": 4,            "quantity": null,            "status": "ok",            "uom": null,            "proofs": [{                    "proofType": "TYPE2",                    "proofName": "PROOF10",                    "proofFile": null                }            ],            "Part_ID": "m100084"        }    ]}[/code]

And my report definition:

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.5.1.final using JasperReports Library version 6.5.1  --><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="Approvals" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="ba1c377f-05cb-4b47-970f-8fb3eecd3293">    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Approval"/>    <property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver/"/>    <property name="ireport.jasperserver.user" value="jasperadmin"/>    <property name="ireport.jasperserver.reportUnit" value="/reports/Approvals"/>    <style name="Table_TH" mode="Opaque" backcolor="#F0F8FF">        <box>            <pen lineWidth="0.5" lineColor="#000000"/>            <topPen lineWidth="0.5" lineColor="#000000"/>            <leftPen lineWidth="0.5" lineColor="#000000"/>            <bottomPen lineWidth="0.5" lineColor="#000000"/>            <rightPen lineWidth="0.5" lineColor="#000000"/>        </box>    </style>    <style name="Table_CH" mode="Opaque" backcolor="#BFE1FF">        <box>            <pen lineWidth="0.5" lineColor="#000000"/>            <topPen lineWidth="0.5" lineColor="#000000"/>            <leftPen lineWidth="0.5" lineColor="#000000"/>            <bottomPen lineWidth="0.5" lineColor="#000000"/>            <rightPen lineWidth="0.5" lineColor="#000000"/>        </box>    </style>    <style name="Table_TD" mode="Opaque" backcolor="#FFFFFF">        <box>            <pen lineWidth="0.5" lineColor="#000000"/>            <topPen lineWidth="0.5" lineColor="#000000"/>            <leftPen lineWidth="0.5" lineColor="#000000"/>            <bottomPen lineWidth="0.5" lineColor="#000000"/>            <rightPen lineWidth="0.5" lineColor="#000000"/>        </box>    </style>    <subDataset name="proofs" whenResourceMissingType="Key" uuid="4563e834-a9e5-43b5-9f0a-824948c73c73">        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Approval"/>        <queryString language="jsonql">            <![CDATA[Results.proofs.*]]>        </queryString>        <field name="proofType" class="java.lang.String">            <property name="net.sf.jasperreports.jsonql.field.expression" value="Results.proofType"/>        </field>        <field name="proofName" class="java.lang.String">            <property name="net.sf.jasperreports.jsonql.field.expression" value="Results.proofName"/>        </field>        <field name="proofFile" class="java.lang.String">            <property name="net.sf.jasperreports.jsonql.field.expression" value="Results.proofFile"/>        </field>    </subDataset>    <queryString language="jsonql">        <![CDATA[Results.*]]>    </queryString>    <field name="Part_name" class="java.lang.String">        <property name="net.sf.jasperreports.jsonql.field.expression" value="Part_name"/>        <fieldDescription><![CDATA[Part_name]]></fieldDescription>    </field>    <field name="BOM_level" class="java.lang.String">        <property name="net.sf.jasperreports.jsonql.field.expression" value="BOM_level"/>        <fieldDescription><![CDATA[bOM_level]]></fieldDescription>    </field>    <field name="quantity" class="java.lang.String">        <property name="net.sf.jasperreports.jsonql.field.expression" value="quantity"/>        <fieldDescription><![CDATA[quantity]]></fieldDescription>    </field>    <field name="status" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression" value="status"/>        <fieldDescription><![CDATA[status]]></fieldDescription>    </field>    <field name="uom" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression" value="uom"/>        <fieldDescription><![CDATA[uom]]></fieldDescription>    </field>    <field name="proofs" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression" value="proofs"/>        <fieldDescription><![CDATA[proofs]]></fieldDescription>    </field>    <field name="Part_ID" class="java.lang.String">        <property name="net.sf.jasperreports.json.field.expression" value="Part_ID"/>        <fieldDescription><![CDATA[Part_ID]]></fieldDescription>    </field>    <field name="proofType" class="java.lang.String">        <fieldDescription><![CDATA[proofs.proofType]]></fieldDescription>    </field>    <field name="proofName" class="java.lang.String">        <fieldDescription><![CDATA[proofs.proofName]]></fieldDescription>    </field>    <field name="proofFile" class="java.lang.String">        <fieldDescription><![CDATA[proofs.proofFile]]></fieldDescription>    </field>    <background>        <band splitType="Stretch"/>    </background>    <columnHeader>        <band height="21" splitType="Stretch">            <staticText>                <reportElement x="0" y="0" width="70" height="20" uuid="0bb32278-a663-4c3e-9554-43c738319efd">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="1b5d94be-357c-4a7e-af3d-12c7c7abd516"/>                </reportElement>                <text><![CDATA[Part]]></text>            </staticText>            <staticText>                <reportElement x="70" y="0" width="60" height="20" uuid="ae991bf9-1269-48a5-aeca-5fa180af117a">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="9d10b93b-f235-4ddd-8f29-22d5ed937ab5"/>                </reportElement>                <text><![CDATA[Level]]></text>            </staticText>            <staticText>                <reportElement x="187" y="0" width="53" height="20" uuid="c69ae5dd-d253-4ce2-894c-46782dc5e3d0">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="a47f47ec-af0e-41f0-95a0-7b1c0a6f10db"/>                </reportElement>                <text><![CDATA[Quantity]]></text>            </staticText>            <staticText>                <reportElement x="240" y="0" width="100" height="20" uuid="c52a1dce-c849-44df-8587-a495577a376e">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="7b258f22-870d-4d10-8663-6e9296eb4e16"/>                </reportElement>                <text><![CDATA[status]]></text>            </staticText>            <staticText>                <reportElement x="340" y="0" width="80" height="20" uuid="f8d5ebc3-f8df-45e5-b2bc-64dce6345c21">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="2890cc9c-98a2-4a26-8f94-1a6c6751952f"/>                </reportElement>                <text><![CDATA[iD]]></text>            </staticText>            <staticText>                <reportElement x="130" y="0" width="56" height="21" uuid="0f3365ec-a0f4-4cd8-a07d-e8a5fb794cb0">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="bcc4d80f-ba4c-4b97-9cd8-9b1d6e3c560a"/>                </reportElement>                <text><![CDATA[uOM]]></text>            </staticText>            <staticText>                <reportElement x="420" y="0" width="100" height="21" uuid="5edcda5a-382b-4c90-91a0-677f4a5ff336">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="5e7e4213-68b0-4d15-a31f-3ed0a7b90aba"/>                </reportElement>                <text><![CDATA[Proof]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="62" splitType="Stretch">            <textField>                <reportElement x="0" y="0" width="70" height="20" uuid="50b083c3-d16e-4de4-bf2f-979dcfa106cd">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="1b5d94be-357c-4a7e-af3d-12c7c7abd516"/>                </reportElement>                <textFieldExpression><![CDATA[$F{Part_name}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="70" y="0" width="60" height="20" uuid="db293151-3c1b-4492-bc52-014ab297986f">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="1b5d94be-357c-4a7e-af3d-12c7c7abd516"/>                </reportElement>                <textFieldExpression><![CDATA[$F{BOM_level}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="186" y="0" width="54" height="20" uuid="8e80e13e-76ce-48d8-b7a3-9034d6d98345">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="85ea81ee-4a5d-49cd-867c-7b1cf4b8fc0a"/>                </reportElement>                <textFieldExpression><![CDATA[$F{quantity}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="240" y="0" width="100" height="20" uuid="7ea8cc64-46d9-470e-b0a0-908b5ab180e8">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="464b8bc9-4f99-488a-b9c0-d0d3b67abdee"/>                </reportElement>                <textFieldExpression><![CDATA[$F{status}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="130" y="0" width="56" height="20" uuid="43011e16-e0d1-4644-9043-94160c57bf95">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="bcc4d80f-ba4c-4b97-9cd8-9b1d6e3c560a"/>                </reportElement>                <textFieldExpression><![CDATA[$F{uom}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="340" y="0" width="79" height="20" uuid="87fddfb3-b2f4-498c-b6fb-7fc5c931ff1d">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="62a0303a-01c4-44fe-a554-f00d023933c5"/>                </reportElement>                <textFieldExpression><![CDATA[$F{Part_ID}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="420" y="0" width="100" height="20" uuid="dc6307f1-2405-4cf5-a0c6-7c33d0376beb">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="5e7e4213-68b0-4d15-a31f-3ed0a7b90aba"/>                </reportElement>                <textFieldExpression><![CDATA[$F{proofType}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="419" y="20" width="100" height="20" uuid="d0d5639a-6129-403c-ae0c-4f41a8f161e7">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="6c8e4535-1a39-4f5f-8559-db3612ff5b56"/>                </reportElement>                <textFieldExpression><![CDATA[$F{proofName}]]></textFieldExpression>            </textField>            <textField>                <reportElement x="419" y="40" width="100" height="20" uuid="ce8a0250-8e66-466b-8c3f-030e85de61ae">                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="6efe6415-879b-4837-a011-86707f833596"/>                </reportElement>                <textFieldExpression><![CDATA[$F{proofFile}]]></textFieldExpression>            </textField>            <componentElement>                <reportElement x="219" y="20" width="200" height="41" uuid="3b4b1cc5-3022-4467-b490-e9275935c888">                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.FreeLayout"/>                    <property name="com.jaspersoft.studio.table.style.table_header" value="Table_TH"/>                    <property name="com.jaspersoft.studio.table.style.column_header" value="Table_CH"/>                    <property name="com.jaspersoft.studio.table.style.detail" value="Table_TD"/>                </reportElement>                <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" whenNoDataType="AllSectionsNoDetail">                    <datasetRun subDataset="proofs" uuid="75af8bcf-1dd6-4a94-9e96-0d976350d14e">                        <datasetParameter name="JSON_INPUT_STREAM">                            <datasetParameterExpression><![CDATA[$P{JSON_INPUT_STREAM}]]></datasetParameterExpression>                        </datasetParameter>                        <datasetParameter name="net.sf.jasperreports.json.source">                            <datasetParameterExpression><![CDATA[$P{net.sf.jasperreports.json.source}]]></datasetParameterExpression>                        </datasetParameter>                        <datasetParameter name="net.sf.jasperreports.json.sources">                            <datasetParameterExpression><![CDATA[$P{net.sf.jasperreports.json.sources}]]></datasetParameterExpression>                        </datasetParameter>                        <datasetParameter name="net.sf.jasperreports.json.date.pattern">                            <datasetParameterExpression><![CDATA[$P{net.sf.jasperreports.json.date.pattern}]]></datasetParameterExpression>                        </datasetParameter>                        <datasetParameter name="net.sf.jasperreports.json.number.pattern">                            <datasetParameterExpression><![CDATA[$P{net.sf.jasperreports.json.number.pattern}]]></datasetParameterExpression>                        </datasetParameter>                        <datasetParameter name="JSON_LOCALE">                            <datasetParameterExpression><![CDATA[$P{JSON_LOCALE}]]></datasetParameterExpression>                        </datasetParameter>                        <datasetParameter name="net.sf.jasperreports.json.locale.code">                            <datasetParameterExpression><![CDATA[$P{net.sf.jasperreports.json.locale.code}]]></datasetParameterExpression>                        </datasetParameter>                        <datasetParameter name="JSON_TIME_ZONE">                            <datasetParameterExpression><![CDATA[$P{JSON_TIME_ZONE}]]></datasetParameterExpression>                        </datasetParameter>                        <datasetParameter name="net.sf.jasperreports.json.timezone.id">                            <datasetParameterExpression><![CDATA[$P{net.sf.jasperreports.json.timezone.id}]]></datasetParameterExpression>                        </datasetParameter>                        <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>                    </datasetRun>                    <jr:column width="66" uuid="76155ec2-e350-4854-bbbf-83ea5c49dc13">                        <property name="com.jaspersoft.studio.components.table.model.column.name" value="Column1"/>                        <jr:tableHeader style="Table_TH" height="20">                            <staticText>                                <reportElement x="0" y="0" width="66" height="20" uuid="f870bd61-84b1-49ef-bdf5-b28858fe1b25"/>                                <text><![CDATA[Type]]></text>                            </staticText>                        </jr:tableHeader>                        <jr:detailCell style="Table_TD" height="20">                            <textField>                                <reportElement x="0" y="0" width="66" height="20" uuid="65959781-3b2f-4226-9ef3-b56ddba2e9fa"/>                                <textFieldExpression><![CDATA[$F{proofType}]]></textFieldExpression>                            </textField>                        </jr:detailCell>                    </jr:column>                    <jr:column width="66" uuid="17dd3735-0739-4c83-8efe-e2039e0c41bd">                        <property name="com.jaspersoft.studio.components.table.model.column.name" value="Column2"/>                        <jr:tableHeader style="Table_TH" height="20">                            <staticText>                                <reportElement x="0" y="0" width="66" height="20" uuid="e5662edc-7748-4434-b269-3843ad19c7f0"/>                                <text><![CDATA[Proof]]></text>                            </staticText>                        </jr:tableHeader>                        <jr:detailCell style="Table_TD" height="20">                            <textField>                                <reportElement x="0" y="0" width="66" height="20" uuid="c3bdd145-4312-4261-b12c-5c52ee7acdbb"/>                                <textFieldExpression><![CDATA[$F{proofName}]]></textFieldExpression>                            </textField>                        </jr:detailCell>                    </jr:column>                    <jr:column width="66" uuid="b16139ca-fafd-4d24-9400-ce1c9770c0df">                        <property name="com.jaspersoft.studio.components.table.model.column.name" value="Column3"/>                        <jr:tableHeader style="Table_TH" height="20">                            <staticText>                                <reportElement x="0" y="0" width="66" height="20" uuid="94c810b6-caa4-4580-a508-2b3efa22c06e"/>                                <text><![CDATA[File]]></text>                            </staticText>                        </jr:tableHeader>                        <jr:detailCell style="Table_TD" height="20">                            <textField>                                <reportElement x="0" y="0" width="66" height="20" uuid="c6cce0f3-5353-40db-986c-079d9f692366"/>                                <textFieldExpression><![CDATA[$F{proofFile}]]></textFieldExpression>                            </textField>                        </jr:detailCell>                    </jr:column>                </jr:table>            </componentElement>        </band>    </detail></jasperReport>[/code]

 

[narcism]

You only need to subDatasource inside your table's datasetRun. I'm posting only the relevant changes:

1. The subDataset definition:

<subDataset name="proofs" whenResourceMissingType="Key" uuid="4563e834-a9e5-43b5-9f0a-824948c73c73">
    <field name="proofType" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="proofType"/>
    </field>
    <field name="proofName" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="proofName"/>
    </field>
    <field name="proofFile" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="proofFile"/>
    </field>
</subDataset>

2. The table datasetRun definition:

<datasetRun subDataset="proofs" uuid="75af8bcf-1dd6-4a94-9e96-0d976350d14e">
    <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("proofs.*")]]></dataSourceExpression>
</datasetRun>

 

[marc.roth]

Perfect! Saved my day...