Jump to content
Changes to the Jaspersoft community edition download ×

JSONQL: Is there a way to replace the literal reference with the current value of the column I'm iterating over?


de8orah
Go to solution Solved by narcism,

Recommended Posts

First, please forgive me as I'm sure I will sound very ignorant with regards to Jasper Reports Server and JSONQL - these are all new to me!

Here is the basic structure I'm using:

{
"dafs": [
{ "id": "3434343", "name": "TEST" }
],
"details": [
{"dafId": "3434343"}
]
}

From this, I want to get the "name" from the "daf" as I iterate over the details.

Here is an example of what I thought might work:

$.dafs.id(@val == "3434343")[0].name

but I'm not successfully getting it to change to the daf ID I am currently working in.

Link to comment
Share on other sites

  • Replies 5
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

  • Solution

Your initial query, $.dafs.id(@val == "3434343")[0].name, does not yield any results because you are trying to advance the 'name' key into an 'id' value:

$.dafs.id(@val == "3434343")[0] produces "3434343" and "3434343".name does not make any sense.

 

As @SuperDave pointed out, you need to go one level up(to the object holding the id property that you filtered) and get the 'name' property:

$.dafs.id(@val == "3434343")[0]^{1}.name

 

If your IDs are unique you don't need to select the first one with [0] and if you are going up one level you could just use ^ instead of ^{1} and have a simpler query like so:

$.dafs.id(@val == "3434343")^.name

 

If your query is the mainDataset's query, you can skip the $ sign as it has no influence there, but only when used on field expression properties. 

Your mainDataset's query could eventually looks like so: dafs.id(@val == "3434343")^.name

 

Other queries that might produce the same result could be:

dafs.*(id == "3434343").name, meaning: get all the 'dafs' children(.*) with an id of "3434343", then get the name

..name(^.id == "3434343"), meaning: get the name from everywhere(..name), whose parent has an id of "3434343"

..*(id == "3434343").name, meaning: get everything with an id of "3434343" then select its name

 

Now, for a sample JSON file like this one:

{
    "dafs": [{
            "id": "3434343",
            "name": "TEST"
        }, {
            "id": "7777777",
            "name": "TEST2"
        }
    ],
    "details": [{
            "dafId": "3434343",
            "value": "foo"
        }, {
            "dafId": "3434343",
            "value": "bar"
        }, {
            "dafId": "7777777",
            "value": "foo2"
        }
    ]
}

you could achieve what you want like so:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.4.3.final using JasperReports Library version 6.4.3  -->
<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_v2" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="e3b05a29-880e-40b6-b6e5-57262d70dafe">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="JsonqlDataAdapter.xml"/>
    <subDataset name="Dataset1" uuid="ad6f5a71-46b2-4e2c-afc4-057a0c0fcd01">
        <field name="name" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="[0]"/>
        </field>
    </subDataset>
    <queryString language="jsonql">
        <![CDATA[details]]>
    </queryString>
    <field name="dafId" class="java.lang.String"/>
    <field name="value" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="80" splitType="Stretch">
            <staticText>
                <reportElement x="230" y="20" width="100" height="30" uuid="d8d035c2-98de-4dfe-9b6f-2d614c6eb84c"/>
                <text><![CDATA[JSONQL Master-Detail Test]]></text>
            </staticText>
        </band>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="30" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="100" height="30" uuid="d44d7116-5f0a-4825-b95d-3352bed51408">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="478a975d-5f7d-4562-a05c-bb7deb5a87de"/>
                </reportElement>
                <text><![CDATA[DafId]]></text>
            </staticText>
            <staticText>
                <reportElement x="100" y="0" width="100" height="30" uuid="34889355-45e9-4d08-b3fa-a395eff34fa4">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="22926328-265c-4c55-a355-0c16a8674fc5"/>
                </reportElement>
                <text><![CDATA[Value]]></text>
            </staticText>
            <staticText>
                <reportElement x="200" y="0" width="100" height="30" uuid="94622f69-794f-48cd-945b-41198e37e9b8">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="22926328-265c-4c55-a355-0c16a8674fc5"/>
                </reportElement>
                <text><![CDATA[Name]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="30" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="100" height="30" uuid="8f21d894-1820-439e-8548-ea499caeae76">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="478a975d-5f7d-4562-a05c-bb7deb5a87de"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{dafId}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="100" y="0" width="100" height="30" uuid="6d869a88-514a-48b9-9bc7-c96f691e5ab3">
                    <property name="com.jaspersoft.studio.spreadsheet.connectionID" value="22926328-265c-4c55-a355-0c16a8674fc5"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{value}]]></textFieldExpression>
            </textField>
            <componentElement>
                <reportElement x="200" y="0" width="100" height="30" uuid="624dbec7-4b67-43dd-a061-57caf6076af2"/>
                <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="Dataset1" uuid="64f4dd3c-b18c-460b-bc96-198f8eb8a948">
                        <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JsonQLDataSource)$P{REPORT_DATA_SOURCE}).subDataSource("^{2}.dafs.id(@val == "" + $F{dafId} + "")^.name")]]></dataSourceExpression>
                    </datasetRun>
                    <jr:listContents height="30" width="100">
                        <textField>
                            <reportElement x="0" y="0" width="100" height="30" uuid="7d57d947-68b4-4653-94b8-ff93b30924e2"/>
                            <textFieldExpression><![CDATA[$F{name}]]></textFieldExpression>
                        </textField>
                    </jr:listContents>
                </jr:list>
            </componentElement>
        </band>
    </detail>
</jasperReport>
 

Link to comment
Share on other sites

 

Great response narcism, that should be included with the JSONQL examples.

I'm interested in the OPs other question about how to reference the current value from the main dataset instead of using the literal "3434343" in the query. I tried to answer her question but have not found any resources that explain it.  I've tried a few tests, just to see what would happen, nothing works thus far.  I believe the gist of she's asking for is:

{
"dafs": [
{ "id": "3434343", "name": "TEST" },
{ "id": "7777777", "name": "TEST2" }
],
"details": [
{"dafId": "3434343","value":"foo"},
{"dafId": "3434343","value":"bar"},
{"dafId": "7777777","value":"foo2"}
]
}

$.dafs.id(@val == $F{dafId})^.name

where $F{dafId} refers to the refId column in the main dataset that's being interated over, resulting in detail rows:

3434343, foo, TEST
3434343, bar, TEST
7777777, foo2, TEST2

I can see a lot of value in being able to do this, it would allow datasets from webservices to be smaller and use references instead of repeated details in the results.

Again, great explaination of the JSONQL query and examples !! :)

 

Link to comment
Share on other sites

That's a great answer - I was trying to do everything in the initial JSONQL query but having a second datasource in the report that with a dynamic filter is perfect.

 

Thanks for taking the time to look at this, I think I can use this in a lot of my reports.

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...