0

Newbie here. Can someone please help me? I'm trying to use "JSON_TABLE" to convert a json document with arrays into a relational table, but I'm getting the following error. I'm using DB2-Linux v11.1. Thank you in advance.

Error:

"ExampleExceptionFormatter: exception message was: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=path '$.tasks[*]' columns (Ale;.TranId', nested;, DRIVER=4.18.60"

WITH ALERT_TABLE (jsondoc) AS   
(  
VALUES   
'   
{  
        "transaction": {  
            "TranId": "123"  
    },  
        "tasks": [  
        {  
            "AlertID": "ABC123",  
            "AlertType": "AML"  
            }  
        ,  
        {  
            "AlertID": "ZZZ999",  
            "AlertType": "Fraud"        
            }  
    ]  
}  
 '  
)   


SELECT U."TranId", u."AlertID", 
u."AlertType"  
FROM ALERT_TABLE a, JSON_TABLE  
(  
    a.jsondoc, '$' columns(   
    TranId INTEGER path '$.TranId',  
    nested path '$.tasks[*]'  
    columns (  
    AlertID VARCHAR(20) path '$.AlertID',  
    AlertType VARCHAR(20)  path 
'$.AlertType'  
))) u;

I'm expecting to see the following results:

TranId AlertID AlertType
123 ABC123 AML
123 ZZZ999 Fraud

EastBayBulldogz
  • 1
  • 1
  • DB2 for LUW and DB2 for IBM i JSON_TABLE do not handle the same syntax, nested does not exist in DB2LUW. See [this link](https://www.ibm.com/docs/fr/db2/11.5?topic=functions-json-table). And see Mark Barinstein accepted answer [here](https://stackoverflow.com/questions/63035346/db2-nested-json/63042808#63042808) to know how to handle arrays – nfgl Jun 08 '23 at 10:23

0 Answers0