Forum Stats

  • 3,768,544 Users
  • 2,252,807 Discussions
  • 7,874,616 Comments

Discussions

How to generate JSON sample

ronald_2017
ronald_2017 Member Posts: 528 Blue Ribbon
edited Oct 15, 2021 4:39PM in SQL & PL/SQL

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

Best Answer

Answers