Asking for help on correct way to us SQL with CTE to create JSON_OBJECT

The requested JSON needs to be in this form:

{
    "header": {
        "InstanceName": "US"
    },
    "erpReferenceData": {
        "erpReferences": [
            {
                "ServiceID": "fb16e421-792b-4e9c-935b-3cea04a84507",
                "ERPReferenceID": "J0000755"
            },
            {
                "ServiceID": "7d13d907-0932-44c0-ad81-600c9b97b6e5",
                "ERPReferenceID": "J0000756"
            }
        ]
    }
}

The program that I created looks like this:

dcl-s OutFile sqltype(dbclob_file);  

exec sql                                                 
 With x as (                                             
 select  json_object(                                    
 'InstanceName' : trim(Cntry)   ) objHeader                     
 from xmlhdr                                   
 where cntry = 'US'),                                    
                                                         
 y as (                                                  
 select  json_object(                                    
      'ServiceID' VALUE S.ServiceID,                     
      'ERPReferenceID' VALUE I.RefCod) oOjRef      
 FROM IMH I                                           
   INNER JOIN GUIDS G ON G.REFCOD = I.REFCOD 
   INNER JOIN SERV S ON S.GUID = G.GUID               
  WHERE G.XMLTYPE = 'Service')                           
                                                         
   VALUES   (                                            
  select json_object('header'  : objHeader Format json , 
    'erpReferenceData' : json_object(                    
                     'erpReferences' VALUE               
      JSON_ARRAYAGG(               
          ObjRef Format json)))    
       from x                      
   LEFT OUTER JOIN  y ON 1=1       
  Group by objHeader)              
     INTO  :OutFile; 

This is the compile error I get:

SQL0122: Position 41 Column OBJHEADER or expression in SELECT list not valid.

I am asking if this is the correct way to create this SQL statement, is there a better easier way? Any idea how to rewrite the SQL statement to make it work correctly?

Answer

The key with generating JSON or XML for that matter is to start from the inside and work your way out.

(I’ve simplified the raw data into just a test table…)

with elm as(select json_object 
                   ('ServiceID' VALUE ServiceID,                     
                   'ERPReferenceID' VALUE RefCod) as erpRef
            from jsontst)
select * from elm; 

Now add the next layer as a CTE the builds on the first CTE..

with elm as(select json_object 
                   ('ServiceID' VALUE ServiceID,                     
                   'ERPReferenceID' VALUE RefCod) as erpRef
            from jsontst)
 , arr (arrDta) as (values json_array (select erpRef from elm)) 
select * from arr; 

And the next layer…

with elm as(select json_object 
                   ('ServiceID' VALUE ServiceID,                     
                   'ERPReferenceID' VALUE RefCod) as erpRef
            from jsontst)
 , arr (arrDta) as (values json_array (select erpRef from elm)) 
 , erpReferences (refs) as ( select json_object 
                                 ('erpReferences' value arrDta )
                            from arr)
select * 
from erpReferences; 

Nice thing about building with CTE’s is at each step, you can see the results so far…

You can actually always go back and stick a Select * from CTE; in the middle to see what you have at some point.

Note that I’m building this in Run SQL Scripts. Once you have the statement complete, you can embed it in your RPG program.