IBM i Global

Hi

I am trying to convert all records in a physical file to json object.  here is what I have in my SQLRPGLE program.

Dear Sarvan

Please try using JSON_ARRAYAGG in place of JSON_ARRAY to see if it addresses your issue? 

This cheat sheet may be useful for you in the future:  DB2 for i SQL JSON_OBJECT Essential Cheat Sheet at https://blog.faq400.com/en/db2-for-i/db2-for-i-sql-json_object-essential-cheat-sheet-2/      

There are some samples in "Generating JSON data" in IBM Docs.

A simple example is shown below.

- SQL

SELECT
    JSON_OBJECT('customer' VALUE
        JSON_ARRAYAGG(
            JSON_OBJECT('custNo' VALUE REGNO,
                        'custInfo' VALUE
                JSON_OBJECT(
                    'custNameKj' VALUE TRIM(KJNAME),
                    'custNameKana' VALUE TRIM(KNNAME),
                    'custAddr1' VALUE TRIM(ADDR1),
                    'custAddr2' VALUE TRIM(ADDR2)
                )
            )
        )
    ) AS JSONOUT
  FROM DEMOLIB.PERSON
  WHERE REGNO < 3
;

- JSON (Formatted)

{
   "customer":[
      {
         "custNo":1,
         "custInfo":{
            "custNameKj":"滝川　厚",
            "custNameKana":"ﾀｷｶﾞﾜ ｱﾂｼ",
            "custAddr1":"賀茂郡南伊豆町",
            "custAddr2":"一色3-16"
         }
      },
      {
         "custNo":2,
         "custInfo":{
            "custNameKj":"川西　節男",
            "custNameKana":"ｶﾜﾆｼ ｾﾂｵ",
            "custAddr1":"甲賀市",
            "custAddr2":"甲南町野尻1-17-4"
         }
      }
   ]
}

Remember that when in a relational tabular system you need to go from N original rows to a reduced form, usually a reduction/aggregate function is used like SUM or in this case JSON_ARRAYAGG  (that supports also GROUP BY).

I did something similar in a my recent query with the following , it returns a row

SELECT JSON_OBJECT('data' VALUE
       JSON_ARRAYAGG(
         JSON_OBJECT('SOFTWARE_VENDOR':  SOFTWARE_VENDOR,
                     'SOFTWARE_VERSION': SOFTWARE_VERSION,
                     'SOFTWARE_NAME':    SOFTWARE_NAME)
         )
       ) 
FROM SOFT00F

Thank you all for the answer.  I have tried all other combination except for JSON_ARRAYAGG. 

It worked.  Thank you all.