JasperReports - JSONQL Data Source Sample

JSONQL Data Source
The JSONQL query language

JSONQL Data Source

Description / Goal
How to fill a report with data from a JSON file using the JSONQL query language.

Since: 6.3.1

JSONQL Data Source Overview

JSONQL stands for JSON Query Language and it is meant to replace the existing language(simply called JSON) for querying JSON data, but in a separate implementation as they are not compatible in terms of syntax and complexity.

It is recommended that you choose JSONQL over JSON due to its extended capabilities of traversing and filtering JSON structures.

The table below gives an overview of some of the features found in the new JSONQL language in comparison to the existing ones in the JSON language.
The examples are based on the restructured data/northwind.json source file.

The JSONQL Query Executer

It is triggered when the jsonql language is specified:

• at report level:

<query language="jsonql"><![CDATA[ ..Orders(@size > 1).* ]] ></query>

• or in the data adapter:

<jsonDataAdapter class="net.sf.jasperreports.data.json.JsonDataAdapterImpl">
  ...
  <language>jsonql</language>
  ...
</jsonDataAdapter>

The JsonQLQueryExecuter uses the query string to produce a JsonQLDataSource instance, based on the same built-in parameters (or equivalent report properties) as for the existing JsonDataSource. This query executer is registered via JsonQLQueryExecuterFactory factory class.

As in the case of the existing JSON query executer, in order to prepare the data source, the JSONQL query executer looks for the JSON_INPUT_STREAM parameter that contains the JSON source objects in the form of an java.io.InputStream. If no JSON_INPUT_STREAM parameter is provided, then the query executer looks for the alternate net.sf.jasperreports.json.source String parameter or report property that stores the path to the location of the JSON source file.

JsonQLQueryExecuter runs the query over the input source and stores the result in an in-memory JsonQLDataSource object.

During the JsonQLDataSource instantiation, the query executer also looks for the following additional parameters or report properties, containing the required localization settings:

• net.sf.jasperreports.json.date.pattern
• net.sf.jasperreports.json.number.pattern
• JSON_LOCALE (parameter only) of type java.util.Locale
• net.sf.jasperreports.json.locale.code of type java.lang.String; this can be used if no java.util.Locale parameter is available
• JSON_TIME_ZONE (parameter only) of type java.util.TimeZone
• net.sf.jasperreports.json.timezone.id of type java.lang.String; this can be used if no java.util.TimeZone parameter is available

In the next section you can see how these additional parameters are provided in the /src/JsonQLDataSourceApp.java class.

The JSONQL Data Source Sample

In our example, the data is stored as a hierarchy of Northwind.Customers.Orders objects in the data/northwind.json file.

The JSONQL configuration is done in the data/northwindDataAdapter.jrdax data adapter file that is provided in the reports/NorthwindOrdersReport.jrxml via the report property:

<property name="net.sf.jasperreports.data.adapter" value="data/northwindDataAdapter.jrdax"/>

In the NorthwindOrdersReport we run a JSONQL query in order to retrieve orders from anywhere:

<query language="jsonql"><![CDATA[ ..Orders(@size > 1).* ]] ></query>

In reversed order of application, the query simply translates to: "Select the children(.*) of Orders - with size greater than 1 - from anywhere"

This query is possible since the Orders property of each customer is an array. Therefore we can apply an array specific filtering function to each value for the Orders property. In this case we are interested in the Orders that have more than one element.

The "get children" expression .* is necessary here because without it we would be looking at an array of arrays(an array with the arrays that the Orders properties point to) and not an array of objects that we are interested in.

The properties (fields) that we are interested in are:

• properties of the order itself: OrderID, OrderDate and Freight:

<field name="Id" class="java.lang.String">
  <property name="net.sf.jasperreports.jsonql.field.expression" value="OrderID"/>
</field>
<field name="OrderDate" class="java.util.Date">
  <property name="net.sf.jasperreports.jsonql.field.expression" value="OrderDate"/>
</field>
<field name="Freight" class="java.lang.Float">
  <property name="net.sf.jasperreports.jsonql.field.expression" value="Freight"/>
</field>
properties of the parent customer object: City, CustomerID and Company Name
that are obtained by going up 2 levels
<field name="ShipCity" class="java.lang.String">
  <description>Go up by two levels then select City</description>
  <property name="net.sf.jasperreports.jsonql.field.expression" value="^{2}.City"/>
</field>
<field name="CustomerID" class="java.lang.String">
  <description>Go up by two levels then select CustomerID</description>
  <property name="net.sf.jasperreports.jsonql.field.expression" value="^{2}.CustomerID"/>
</field>
<field name="CompanyName" class="java.lang.String">
  <description>Go up twice by 1 level then select CompanyName</description>
  <propertyExpression name="net.sf.jasperreports.jsonql.field.expression"><![CDATA["^^[\"Company Name\"]"]] ></propertyExpression>
</field>

The rest of the configuration necessary for this sample to work is done in the data adapter file: data/northwindDataAdapter.jrdax

<?xml version="1.0" encoding="UTF-8" ?>
<jsonDataAdapter class="net.sf.jasperreports.data.json.JsonDataAdapterImpl">
  <name>NorthWind JsonQL DA</name>
  <fileName>/data/northwind.json</fileName>
  <useConnection>true</useConnection>
  <language>jsonql</language>
  <datePattern>yyyy-MM-dd</datePattern>
  <numberPattern>#,##0.##</numberPattern>
</jsonDataAdapter>

Here we specify:

• the source file with the JSON data: /data/northwind.json
• the fact that we want to use the existing connection and the report's query by setting the useConnection flag to true
• the language of our query: jsonql; it is optional in this case since we are using the report's query that already specifies the language
• the date pattern used to parse the date fields
• the numeric pattern used to parse the numeric fields

Running the Sample

Running the sample requires the Apache Maven library. Make sure that maven is already installed on your system (version 3.6 or later). In a command prompt/terminal window set the current folder to demo/samples/jsonqldatasource within the JasperReports source project and run the following command:

> mvn clean compile exec:exec@all

It will generate all supported document types containing the sample report in the demo/samples/jsonqldatasource/target/reports directory.

The JSONQL query language

Description / Goal
Detailed description of the JSONQL query language.

Since: 6.3.1

Overview

JSONQL is a language used for querying JSON data structures. It appeared from the need to perform more complex JSON traversals and filtering. JSONQL is not compatible with the existing JSON language due to its possibly complicated syntax.

We typically write JSONQL queries in query expressions marked with the appropriate jsonql language:

<query language="jsonql"><![CDATA[ ..Orders(@size > 1).* ]] ></query>

Syntax

A JSONQL query is an expression containing one or more chained members:

[member1][member2]...[memberN]

For example, in the following expression:

Northwind.Customers.Orders.*(Freight > 200 && OrderDate *= "1997")

we distinguish the following members:

• [Northwind]
• [.Customers]
• [.Orders]
• [.*(Freight > 200 && OrderDate *= "1997")]

Each member is applied on the result(s) produced by the previous member. If a member produces no result, the rest of the members, if any, are skipped and no results are returned.

When successful, the final result of the JSONQL expression is a collection of JSON nodes, where each node may be an Array, an Object or a Value(boolean, string, number or null).

Absolute expressions

Absolute expressions start with the $ symbol placed in front of the first member expression:

$.Northwind.Customers[0]["Company Name"]

or

$["Northwind"].Customers[0]["Company Name"]

etc.

When used in the report's query, it makes no difference if an expression is absolute or not. The result is always calculated from the root of the JSON data.

The only place where the absolute expression matters is the field's property expression. For example:

<field name="FirstCustomerCompanyName" class="java.lang.String">
  <description><![CDATA[ The company name of the first customer]] ></description>
  <propertyExpression name="net.sf.jasperreports.jsonql.field.expression"><![CDATA[ "$.Northwind.Customers[0][\"Company Name\"]" ]] ></propertyExpression>
</field>

defines a field whose value is calculated directly from the root Northwind object and not relative to the field in the dataSet produced by the query. ` You may want to use an absolute expression like this when the value you are interested in is not part of any of the results produced by the main dataSet's query.

Comments inside expressions

Comments are supported inside JSONQL expressions. A JSONQL expression can be a single or multi-line expression, therefore we have single line comments and multi-line comments:

• Single line comments start with the // symbol:

Northwind
  .Customers
  .Orders
  .*
  //(Freight > 200 && OrderDate *= "1997")

Here we comment out the entire filtering expression.

• Multi-line comments start with /* and end with */ symbols:

Northwind
  .Customers
  /*.Orders
  .*
  (Freight > 200 && OrderDate *= "1997")*/

Here we comment out everything that follows the Customers member.

We can also use the multi-line comment inside a single line JSONQL expression:

Northwind.Customers.Orders.*(Freight > 200 /*&& OrderDate *= "1997"*/)

Here we comment out only a part of the filtering expression.

JSONQL Members

A member is an expression that tells us how to navigate a single level (up or down or anywhere down) into the JSON tree. The generic member expression has this form:

[direction][key(s)][filterExpression]

The tables below presents all the supported member types within a JSONQL expression. Only the [direction][key(s)] part is shown here as the [filterExpression] is applicable to all member types and will be described separately.

{
"Products": {
"Product_1": {
"Id": 123,
"Category": "books"
},
"Product_2": {
"Id": 234,
"Category": "dvds"
}
}
}

[{
"Id": 123,
"Category": "books"
}, {
"Id": 234,
"Category": "dvds"
}]

[{
"OrderID": 10643,
"OrderDate": "1997-08-25"
},
...
{
"OrderID": 11011,
"OrderDate": "1998-04-09"
}]

[{
"Company Name": "Alfreds Futterkiste",
"Address": "Obere Str. 57"
},
...
{
"Company Name": "Wolski  Zajazd",
"Address": "ul. Filtrowa 68"
}]

[{
"OrderID": 10643,
"OrderDate": "1997-08-25"
},
...
{
"OrderID": 11011,
"OrderDate": "1998-04-09"
}]

⁽¹⁾ A Legal JavaScript Identifier is a character sequence that:

• must start with one of: letter(lowercase or uppercase), $ or _
• and can be followed by any combination of letter, $, _ or digit(0-9).

⁽²⁾ A String Literal is an escaped and double quoted JavaScript String.

The Filter Expression

Each member expression described above can be accompanied by a filter expression.
Let's consider the following JSONQL expression:

 Northwind.Customers.Orders.*(Freight > 200 && OrderDate *= "1997")

It contains the following members:

• [Northwind]
• [.Customers]
• [.Orders]
• [.*(Freight > 200 && OrderDate *= "1997")]

Members #1, #2 and #3 contain no filter expression, only #4 does:

 (Freight > 200 && OrderDate *= "1997")

In this case we have a compound filter expression made out of two basic filter expressions joined by the logical and(&&) operator.

The filter expression is evaluated as member results are added to the whole expression's result. The result of a filter expression is a boolean true or false.

The Basic Filter Expression

The form of a basic filter expression is one of:

( [filterMember1][filterMember2]...[filterMemberN]@Function^(*) Operator Value )
( [filterMember1][filterMember2]...[filterMemberN]@Function)

^(*) The @Function element is optional

The filterMember is similar to the JSONQL Member except it lacks the filter expression. It contains only the: [direction][key(s)] part.

This means that the above member type definitions also apply in this case.

The type and presence of the @Function element dictates the form of the basic filter expression as follows:

Operator to Operand type application

There is an enforcement at the JSONQL language level that requires the operators to be applied to the right kind of operands. The query expression is not valid unless the matching is correctly done.

Logical Operators for Basic Filter Expressions

Basic Filter Expressions can be negated and/or combined in order to allow more complex filtering capabilities.