[
https://issues.apache.org/jira/browse/FINERACT-1996?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17793782#comment-17793782
]
Peter Santa commented on FINERACT-1996:
---------------------------------------
Márta Jankovics
h3. October 16, 2023 at 11:49 PM
Edited
Test report_sql (${datatable} param I registered beforehand):
select savings_transaction_id, {{{}structured_transaction_details{}}}::json ->
'EntryDetails' -> 0 -> 'TransactionDetails'-> 0
->'RelatedParties'->'DebtorAccount'->'Identification' → 'IBAN' as iban
from ${datatable} dstd
where savings_transaction_id = ${savingsTransactionId}
* Run requested report with non-generic resultset
GET
\{{url}}/runreports/:reportName?{*}genericResultSet=false{*}&R_savingsTransactionId=\{{savingsTransId}}&R_datatable=\{{dtableName}}
This report failed before, but after the fix it returns:
{{}}{{[}}{{ {}}{{ "savings_transaction_id": 185,}}{{ "iban":
null}}{{ },}}{{ {}}{{ "savings_transaction_id": 185,}}{{
"iban": "\"HU73304000010000000064708610\""}}{{ }}}
{{]}}
Please note the “iban” value is in quotation mark, because it’s json type
* Run requested report with generic resultset
GET
\{{url}}/runreports/:reportName?{*}genericResultSet=true{*}&R_savingsTransactionId=\{{savingsTransId}}&R_datatable=\{{dtableName}}
This report failed before, but after the fix it returns:
{{}}{{{}}{{ "columnHeaders": [}}{{ {}}{{ "columnName":
"savings_transaction_id",}}{{ "columnType": "BIGINT",}}{{
"columnDisplayType": "INTEGER",}}{{ "isColumnNullable": false,}}{{
"isColumnPrimaryKey": false,}}{{ "isColumnUnique":
false,}}{{ "isColumnIndexed": false,}}{{ "columnValues":
[]}}{{ },}}{{ {}}{{ "columnName": "iban",}}{{
"columnType": "JSON",}}{{ "columnDisplayType": "TEXT",}}{{
"isColumnNullable": false,}}{{ "isColumnPrimaryKey": false,}}{{
"isColumnUnique": false,}}{{ "isColumnIndexed": false,}}{{
"columnValues": []}}{{ }}}{{ ],}}{{ "data": [}}{{
{}}{{ "row": [}}{{ 185,}}{{ null}}{{
]}}{{ },}}{{ {}}{{ "row": [}}{{
185,}}{{ {}}{{ "type": "json",}}{{
"value": "\"HU73304000010000000064708610\""}}{{ }}}{{
]}}{{ }}}{{ ]}}
{{}}}
“iban” response type is json and the value is quoted
* Run *corrected report* (please note the operator *->>* to get the iban
value, instead of the json part itself) same API
select savings_transaction_id, {{{}structured_transaction_details{}}}::json ->
'EntryDetails' -> 0 -> 'TransactionDetails'-> 0
->'RelatedParties'->'DebtorAccount'->'Identification' *->>* 'IBAN' as iban
{{}}{{{}}{{ "columnHeaders": [}}{{ {}}{{ "columnName":
"savings_transaction_id",}}{{ "columnType": "BIGINT",}}{{
"columnDisplayType": "INTEGER",}}{{ "isColumnNullable": false,}}{{
"isColumnPrimaryKey": false,}}{{ "isColumnUnique":
false,}}{{ "isColumnIndexed": false,}}{{ "columnValues":
[]}}{{ },}}{{ {}}{{ "columnName": "iban",}}{{
"columnType": "TEXT",}}{{ "columnDisplayType": "TEXT",}}{{
"isColumnNullable": false,}}{{ "isColumnPrimaryKey": false,}}{{
"isColumnUnique": false,}}{{ "isColumnIndexed": false,}}{{
"columnValues": []}}{{ }}}{{ ],}}{{ "data": [}}{{
{}}{{ "row": [}}{{ 185,}}{{ null}}{{
]}}{{ },}}{{ {}}{{ "row": [}}{{
185,}}{{ "HU73304000010000000064708610"}}{{ ]}}{{
}}}{{ ]}}
{{}}}
“iban” response type is text and there is no unnecessary quotation marks
* Json path expression is working also in the where condition, tested with
this report (for ${asOn} I linked an existing parameter):
{{}}{{select savings_transaction_id,}}{{(structured_transaction_details::json
-> 'EntryDetails' -> 0 -> 'TransactionDetails'-> 0
->'RelatedParties'->'DebtorAccount'->'Identification' ->> 'IBAN') as
iban,}}{{TO_DATE(structured_transaction_details::json -> 'ValueDate' ->>
'Date', 'yyyy-MM-dd') as asOn}}{{from ${datatable} dstd }}
*
{{where savings_transaction_id = ${savingsTransactionId} and
(structured_transaction_details::json -> 'ValueDate' ->> 'Date') = '${asOn}'}}
Response:
{{}}{{{}}{{ "columnHeaders": [}}{{ {}}{{ "columnName":
"savings_transaction_id",}}{{ "columnType": "BIGINT",}}{{
"columnDisplayType": "INTEGER",}}{{ "isColumnNullable": false,}}{{
"isColumnPrimaryKey": false,}}{{ "isColumnUnique":
false,}}{{ "isColumnIndexed": false,}}{{ "columnValues":
[]}}{{ },}}{{ {}}{{ "columnName": "iban",}}{{
"columnType": "TEXT",}}{{ "columnDisplayType": "TEXT",}}{{
"isColumnNullable": false,}}{{ "isColumnPrimaryKey": false,}}{{
"isColumnUnique": false,}}{{ "isColumnIndexed": false,}}{{
"columnValues": []}}{{ },}}{{ {}}{{
"columnName": "ason",}}{{ "columnType": "DATE",}}{{
"columnDisplayType": "DATE",}}{{ "isColumnNullable": false,}}{{
"isColumnPrimaryKey": false,}}{{ "isColumnUnique": false,}}{{
"isColumnIndexed": false,}}{{ "columnValues": []}}{{
}}}{{ ],}}{{ "data": [}}{{ {}}{{ "row": [}}{{
185,}}{{ "HU73304000010000000064708610",}}{{
[}}{{ 2023,}}{{ 10,}}{{
13}}{{ ]}}{{ ]}}{{ }}}{{ ]}}
{{}}}
Please note the type of the “{{{}asOn{}}}“ property is date. You can refine the
report to return specific type column values.
*Summary:*
* The original report is working now because I’ve {*}introduced new datatable
column type: json{*}. Supported both by mysql and postgres databases, but mysql
stores it as long text. PostgreSQL supports native JSON data type since
{*}version 9.2{*}.
It is now possible to add json columns even when the datatable is created.
”Such data can also be stored as {{{}text{}}}, but the JSON data types have the
advantage of enforcing that each stored value is valid according to the JSON
rules.”
{{}}{{{}}{{ "datatableName": "dt_savings_transaction_\{{dtableCount}}",}}{{
"apptableName": "m_savings_account_transaction",}}{{ "multiRow": true,}}{{
"columns": [}}{{ {}}{{}}{{…}}{{
"name":"structured_transaction_details",}}{{ "type":"json"
}}{{ },}}
*
{{…}}
* The json path report fails if there is any row with malformed json in the
queried column (if the row matches the preceding filters)
* The report itself is better to be modified to use ->> operator
* json path expression with dynamic parameters can be used in both the SELECT
and WHERE sections of the report sql
* It might be useful to request specific return types
> Reporting should support query that includes json path for DB field in
> Postgres
> -------------------------------------------------------------------------------
>
> Key: FINERACT-1996
> URL: https://issues.apache.org/jira/browse/FINERACT-1996
> Project: Apache Fineract
> Issue Type: Improvement
> Components: Data Tables
> Reporter: Peter Santa
> Priority: Major
> Labels: BeanSalad
>
> h1. Goal
> It should be supported to insert a reporting query via API, that utilises the
> benefits of a Postgres DB, to query elements in a JSON that is stored in a
> field of a record.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
