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

[de8orah]

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.

[SuperDave]

I cannot answer the question yet, but the reference to name should probably be:

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

Since the relative location of dafs.id would only return id (a value) with no children. 

 

[narcism]

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>
 

[SuperDave]

 

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 !! :)

 

[narcism]

Edited my answer with a small sample based on you JSON data

[SuperDave]

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.