JSON String Field into a table

In Jaspersoft Studio, I am using JSONQL to read a JSON file and all is working. However, one of my fields is itself encoded as a JSON String differently from the other fields - see below field employee.state.N.Values. Therefore this field in Jasper is always returned as a string. No matter what I have tried I am unable to get Jasper to interpretemployee.state.N.Values as JSON. I have tried various options in the Dataset & Query as well as trying to use Java on the string itself.

The requirement is to display employee.state.N.Values as a table. Any help appreciated on how I could do this?

{
 "employee": { "352": { "name"  : "Jimmy",
                        "email" : "Jimmy@co",
                        "mobile": "0700 1234569",
                        "state" : { "N": { "Values": "{\"A\": {\"GP\": \"1700.00\", \"Er\": \"137.72\", \"Ee\": \"119.76\", \"GP-YTD\": \"6800.00\"}}"
                                         }
                                  }
                      }
             }
}
innovate's picture
Joined: Oct 6 2018 - 2:29pm
Last seen: 1 year 8 months ago

Excellent- thankyou so much!
This was the part i was missing: 
new net.sf.jasperreports.engine.data.JsonQLDataSource(new ByteArrayInputStream($F{Values}.getBytes("UTF-8")), "A")

Now, my application is a slightly more complex in that the JSON string may have more than one KEY- see below.
Furthermore, I dont know the names of the keys (e.g. one is called "A" and the other "Z".).

I wish to create a table with the first column being the KEY, and the Values in each of the subsequent columns.

KEY GP Er Ee GP-YTD
A 1700 137.72 119.76 6800.00
Z 200.00 11.72 12.76 13.00

I assume there are 2 parts to solve this: (1) identify the KEYs? and (2) return each KEY dict. 
I tried the modify/ remove the SelectionExpression in order to try and return return , but was unsuccessfull.

Any help appreciated on how the report below could be enhanced to achieve this. Many thanks in advance.

{
 "employee": { "352": { "name"  : "Jimmy",
                        "email" : "Jimmy@co",
                        "mobile": "0700 1234569",
                        "state" : { "N": { "Values": "{\"A\": {\"GP\": \"1700.00\", \"Er\": \"137.72\", \"Ee\": \"119.76\", \"GP-YTD\": \"6800.00\"}, 
                 \"Z\": {\"GP\": \"200.00\", \"Er\": \"11.72\", \"Ee\": \"12.76\", \"GP-YTD\": \"13.00\"}}"
                                         }
                                  }
                      }
             }
}
innovate - 4 years 11 months ago

Unfortunately there's no JSONQL operation to pick up an object key(s) right now. A future enhancement may address this. You could get everything else just by modifying the JsonQLDatasource query:

new net.sf.jasperreports.engine.data.JsonQLDataSource(new ByteArrayInputStream($F{Values}.getBytes("UTF-8")), ".*")

narcism - 4 years 11 months ago

Thankyou so much for the pointer to: 

new net.sf.jasperreports.engine.data.JsonQLDataSource(new ByteArrayInputStream($F{Values}.getBytes("UTF-8")), ".*")

That worked perfectly to retreive all the dicts.

Regarding the ability to pick up object keys, that is a shame, but appreciate the clarification.

I was in fact able to work around this by defining a couple of keys in the subdataset:

  • ^{2}.A.GP
  • ^{2}.Z.GP

and then using an expression like this to figure out which dict was in each row:

  • T(IF(EQUALS($F{A_key}, $F{GP}),"A","")) +
        T(IF(EQUALS($F{Z_key},$F{GP}),"Z",""))

Thanks a lot.

innovate - 4 years 11 months ago

1 Answer:

You need to convert the String value to a JsonQLDataSource like so:

<?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="4c53fb8c-7bb8-4faa-a219-05712dd54a30">
    <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="TableDataset" uuid="70d9d3c4-af50-48fc-a8a9-205697b62f0c">
        <field name="GP" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="GP"/>
        </field>
        <field name="Er" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="Er"/>
        </field>
        <field name="Ee" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="Ee"/>
        </field>
        <field name="GP-YTD" class="java.lang.String">
            <propertyExpression name="net.sf.jasperreports.jsonql.field.expression"><![CDATA["[\"GP-YTD\"]"]]></propertyExpression>
        </field>
    </subDataset>
    <queryString language="jsonql">
        <![CDATA[employee..state.N]]>
    </queryString>
    <field name="Values" class="java.lang.String">
        <property name="net.sf.jasperreports.jsonql.field.expression" value="Values"/>
    </field>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch">
            <staticText>
                <reportElement x="130" y="10" width="300" height="30" uuid="bd4b0bab-1996-4952-884b-5e508abc8af3"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="16"/>
                </textElement>
                <text><![CDATA[JSON String to JsonQLDataSource Example]]></text>
            </staticText>
        </band>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="30" splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band height="64" splitType="Stretch">
            <componentElement>
                <reportElement x="0" y="0" width="200" height="64" uuid="d521af5c-22ab-4e28-a8ef-7bbeedd122ef">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                    <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">
                    <datasetRun subDataset="TableDataset" uuid="318415a6-a046-4886-b04a-f13c3e9bf6f0">
                        <dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.data.JsonQLDataSource(new ByteArrayInputStream($F{Values}.getBytes("UTF-8")), "A")]]></dataSourceExpression>
                    </datasetRun>
                    <jr:column width="50" uuid="21fbea93-d2e3-4572-b730-2dfba3eab448">
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="50" height="30" uuid="b54c30af-fb8f-42cc-81d3-a7423ca9b329"/>
                                <text><![CDATA[GP]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="50" height="30" uuid="6646c3f5-5074-4e24-85ae-9b0b39d04a30"/>
                                <textFieldExpression><![CDATA[$F{GP}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="50" uuid="996ba5ec-f669-42b5-9bdb-dae2605e4dee">
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="50" height="30" uuid="e2093fd3-158b-45e2-ac59-44c79b2a2ba2"/>
                                <text><![CDATA[Er]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="50" height="30" uuid="133cdcd6-1b61-4d47-816c-1e68a9aa71a8"/>
                                <textFieldExpression><![CDATA[$F{Er}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="50" uuid="1e059e33-1821-49bc-b4a9-7f08edcbd979">
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="50" height="30" uuid="7338cd27-5d1b-4a79-a64a-eea0878565f1"/>
                                <text><![CDATA[Ee]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="50" height="30" uuid="c9352309-2ded-4e3a-9e4d-f158e2af1b8e"/>
                                <textFieldExpression><![CDATA[$F{Ee}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="50" uuid="e63302fb-4fbb-4685-a0ca-3a6933aa5b3a">
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="50" height="30" uuid="fa704777-82d1-46bd-8dfe-b33b50ed36f6"/>
                                <text><![CDATA[GP-YTD]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="50" height="30" uuid="8ffd553e-3e7b-4d45-a932-8bf5a99d7a28"/>
                                <textFieldExpression><![CDATA[$F{GP-YTD}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                </jr:table>
            </componentElement>
        </band>
    </detail>
</jasperReport>

narcism's picture
6502
Joined: Nov 22 2010 - 12:39am
Last seen: 3 hours 22 min ago
Feedback
randomness