How to generate JSON sample

ronald_2017

Hi All,

I have table and I want to generate the following JSON sample. However, I used group by, so which functions should I use? All columns in the JSON output exist in the table.

Oralce 19C

{
    "cheque_type":"55",
    "amount":1000,
    "people":[
        {"id":"XXX",
        "age":"30",
        "name":"AAA"},
        {"id":"YYY",
        "age":"35",
        "name":"BBB"},
        {"id":"YYY",
        "age":"38",
        "name":"BBB"}
            ],
            "bank_list":[
                {"bank_name":"A Bank",
                "country":"USA"},
                {"bank_name":"B Bank",
                "country":"UK"},
                {"bank_name":"C Bank",
                "country":"France"}
                ],
                "cheque":
                {"cheque_no": "CCC",
                "cheque_bank": "CCC",
                "cekBankasi_branch": "CCC",
                "cekBankasi_date": "",
                },
            "createuser":"TTTT"
} 

SELECT 
    JSON FUNCTIONS
FROM T_TABLE 
GROUP BY cheque_no, cheque_bank, cekBankasi_branch, cekBankasi_date, cheque_type, amount;

Thanks in advance

Solomon Yakobson

Use JSON_ARRAYAGG.

SY.

Solomon Yakobson

Use JSON_ARRAYAGG.

SY.

ronald_2017

https://community.oracle.com/tech/developers/discussion/comment/16812265#Comment_16812265

I used it, however it gives error.

Paulzip

Show your code.

Also, if any section within the JSON generation exceeds 4000 bytes, put returning clob (12.2c and above) in ...

JSON_ARRAYAGG (
  JSON_OBJECT (
    -- large JSON object
    returning clob
  ),
  ....
  returning clob
)