Jump to content
Changes to the Jaspersoft community edition download ×

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 ??


ankit.maurya

Recommended Posts

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 1Json Col
-1{"Id": 5, "name": "MNP"}
0

{"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 1MNPABCDefJklJson Col
-1MNP   {"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"/>
</field>

and other fields as in table

Using the json query below

<queryString language="jsonql">
        <![CDATA[$F{json_col}]]>
 </queryString>

 

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

Any help is really appreciated.

Link to comment
Share on other sites

  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...