How to join text and JSON in BIRT
Keywords：join text and JSON BIRT data source
BIRT provides a joint dataset solution for the combination of two data sources. It has a graphical operation interface and can complete simple internal and external join, but its function is very limited (almost no secondary calculation ability, calculation after join is only limited to simple query and fixed query, and it is difficult to realize free combination query and variable query, not to mention multi-step calculation such as filter after group and aggregation).
BIRT supports CSV / TXT and other files as data sources, but it cannot build JSON data source. Some open source communities provide plug-ins for parsing JSON data source. Almost all plug-ins are very low-level and inconvenient to use.
In terms of capability, only the user-defined data source can completely solve this problem, but the hard coding method of BIRT java bean data source is complex and the workload is huge.
For example, to deal with such a scenario: sales.txt is a structured text divided by tab, city.json is an unstructured JSON string, the second column of sales.txt and part of the text of city.json have a foreign key relationship, and the two files need to be joined into a two-dimensional table. The schematic diagram is as follows:
It is recommended to use esProc, which is an independent data computing engine with computing power independent of the database. It can obtain data from a variety of files and do joins and other calculations. In fact, it can be regarded as a BIRT java bean data source with simpler syntax. For example, to implement the above problem, the script of esProc only needs 5 lines:
A |
|
1 |
=json(file("/workspace/city.json").read()) |
2 |
=A1.new(name,#1.(#1):desc,(firstblank=pos(desc," "),left(desc,firstblank-1)):key,right(desc,len(desc)-firstblank):value) |
3 |
=file("/workspace/sales.txt").import@t() |
4 |
=join(A3:sales,#2;A2:city,key) |
5 |
=A4.new(sales.OrderID,sales.Client,sales.Amount,sales.OrderDate,city.name,city.value) |
It is more convenient to implement calculation after join, such as: count the sales of each city; only add one line on this basis: =A5.groups(name;sum(Amount):amount)
In fact, there are many similar problems that are not convenient to solve, such as: calculating text, Excel, even join calculation, importing into database and other requirements, but using esProc SPL is very simple, please refer to Structured Text File Processing in SPL (II) , JSON data calculation and importing into database
esProc provides JDBC driver, and can be easily integrated with BIRT and other reporting tools. Please refer to How to Call an SPL Script in BIRT
Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
