SQL table is having a column as json and I want to extract a particular value from that and add it in a new column in table, is there any way ??

The problem is as described below:

I have a SQL(Postgresql) table as given below and I am using spring-hibernate to use it as my data source for the report.

Col 1 Json Col
-1 {"Id": 5, "name": "MNP"}

{"Id": 6, "name": "Abc",

"Id": 12, "name": "JKL"}

-1 {"Id": 7, "name": "Def"}


I have to make a table in the report with the same data but in below-given way:

Col 1 MNP ABC Def Jkl Json Col
-1 MNP       {"Id": 5, "name": "MNP"}
0   Abc   Jkl {"Id": 6, "name": "Abc","Id": 12, "name": "Jkl"}
-1     Def   {"Id": 7, "name": "Def"}

for this, there are two fields for the table as col_1 and json_col

and everything works fine and the report gets generated when it is just this.

But when I add a third field "MNP"

<field name="MNP" class="java.lang.String">
            <property name="net.sf.jasperreports.jsonql.field.expression" value="name"/>

and other fields as in table

Using the json query below

<queryString language="jsonql">


Both the previous column still displays data but the new columns are blank.

Any help is really appreciated.

ankit.maurya's picture
Joined: Jan 31 2019 - 5:03am
Last seen: 4 years 3 months ago

Is there a reason why you do not use postgresql to extract the value in a new column and use it in jaspersoft?

mtn - 4 years 3 months ago

Because here in some cases we have multiple values in json_col and that is not fixed, some json_col has one key and value whereas some have more than one.
So the actual case is that there will be a new column for each new value. name1, name2, name3 and so on.

ankit.maurya - 4 years 3 months ago

0 Answers:

No answers yet