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.
2 Answers:
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>
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 !! :)
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.