How To Use PostgreSQL JSON data in Ad Hoc View

Issue Description

Customer has a requirement to create an Ad Hoc View based on PostgreSQL datasource which included JSON data type.

When he adds the JSON field into Ad Hoc View Table as a Column, an error appears:

Exception executing database query. Please contact your administrator.  See application log for additional details. 42883 ERROR: could not identify an equality operator for type json[/code]

Is there a way to use PostgreSQL JSON data in Ad Hoc View?

Resolution

The error caused by ORDER BY clause added to generated SQL like

select "id","info"from "public"."orders_json"order by "info" nulls firstlimit 1000[/code]

which can not be applied to JSON object directly.

There is an enhancement request to implement support for JSON nested fields in Ad Hoc Views and Domains, internal #JS-33110

Until the request implemented, a possible workaround is to create a Domain derived table to 'convert' JSON type into Text data like:

select "id", "info"::Text from "public"."orders_json"[/code]

and then use derived table field to create an Ad Hoc View. The data will be displayed like

{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}[/code]

Solution tested with TIBCO JasperReports^® Server v.7.2.0

AS-20200501, case 01852384