Sample JSON file I want to create from table data
Suppose I have the account details of persons in the following tables
account, transactions and tdsinfo tables. For each account I want to create the json file format of data as per below using PLSQL.
With this information we can build a suitable query to generate our JSON using some common oracle json functions like json_arrayagg and json_object.
{
"Version": {
"Ver": "1.1"
},
"acctdtls": {
"acctname": "John Smith",
"accttype": "S",
"accno": 101,
"transactions": [
{
"transID": 1,
"transDate": "2025-01-01T12:32:37",
"transAmount": 1000
},
{
"transID": 2,
"transDate": "2025-02-01T12:33:13",
"transAmount": 2000
},
{
"transID": 3,
"transDate": "2025-03-01T12:33:35",
"transAmount": 1500
}
]
},
"tdsdtls": {
"accno": 101,
"tds_info": [
{
"tdsdate": "2025-01-01T12:32:37",
"tdsamt": 100
},
{
"tdsdate": "2025-03-01T12:33:35",
"tdsamt": 110
},
{
"tdsdate": "2025-02-01T12:33:13",
"tdsamt": 105
}
]
}
}We first create the tables and dummy data for running our JSON queries.
For a single tag say, acctInfo, below is the query and the output for the same.
SELECT json_object ('acctInfo' IS (json_object ('transactions' IS
(SELECT json_arrayagg (json_object ('transID' IS trans_id, 'transDate' IS trans_dt, 'transAmount' IS trans_amt))
FROM transactions
WHERE 1 =1
AND accounts.accno = transactions.accno
),'accountName' IS accname, 'accountType' IS acctype, 'accountNumber' IS accno)))
FROM accounts
WHERE accno = 101;
{
"acctInfo": {
"transactions": [
{
"transID": 1,
"transDate": "2024-09-28T18:49:57",
"transAmount": 1000
},
{
"transID": 2,
"transDate": "2024-08-09T18:49:57",
"transAmount": 2000
},
{
"transID": 3,
"transDate": "2024-10-08T18:49:57",
"transAmount": 1500
}
],
"accountName": "John Smith",
"accountType": "S",
"accountNumber": 101
}
}
SELECT json_object ('acctInfo' IS ( json_object (
'transactions' VALUE
json_arrayagg
(json_object
('transID' VALUE t.trans_id,
'transDate' VALUE t.trans_dt,
'transAmount' VALUE t.trans_amt
)
),
'accountName' VALUE a.accname,
'accountType' VALUE a.acctype,
'accountNumber' VALUE a.accno
)))
FROM transactions t, accounts a
WHERE a.accno = t.accno
AND a.accno = 101
GROUP BY a.accname, a.acctype, a.accno;
The advantage of below query is you have the flexibility to add more nodes to the array.
I will do it as follows
Thought for the day.
SELECT json_object ('acctInfo' IS ( json_object (
'transactions' VALUE
json_arrayagg
(json_object
('transID' VALUE t.trans_id,
'transDate' VALUE t.trans_dt,
'transAmount' VALUE t.trans_amt
)
),
'tdsInfo' VALUE
json_arrayagg
(json_object
('accNo' VALUE ti.accno,
'tdsDate' VALUE ti.tds_date,
'tdsAmount' VALUE ti.tds_amount
)
),
'accountName' VALUE a.accname,
'accountType' VALUE a.acctype,
'accountNumber' VALUE a.accno
)))
FROM transactions t, accounts a, tds_info ti
WHERE a.accno = t.accno
AND a.accno = ti.accno
AND t.trans_dt = ti.tds_date
AND a.accno = 101
GROUP BY a.accname, a.acctype, a.accno;
{
"acctInfo": {
"transactions": [
{
"transID": 1,
"transDate": "2025-01-01T12:32:37",
"transAmount": 1000
},
{
"transID": 3,
"transDate": "2025-03-01T12:33:35",
"transAmount": 1500
},
{
"transID": 2,
"transDate": "2025-02-01T12:33:13",
"transAmount": 2000
}
],
"tdsInfo": [
{
"accNo": 101,
"tdsDate": "2025-01-01T12:32:37",
"tdsAmount": 100
},
{
"accNo": 101,
"tdsDate": "2025-03-01T12:33:35",
"tdsAmount": 110
},
{
"accNo": 101,
"tdsDate": "2025-02-01T12:33:13",
"tdsAmount": 105
}
],
"accountName": "John Smith",
"accountType": "S",
"accountNumber": 101
}
}
The output of this query still does not give me the json in the format that is require.
Below is the query which will give the desired output.
SELECT json_object(
'Version' VALUE json_object('Ver' VALUE '1.1'),
'acctdtls' VALUE treat(
(SELECT JSON_OBJECT(
KEY 'acctname' VALUE a.ACCNAME,
KEY 'accttype' VALUE a.ACCTYPE,
KEY 'accno' VALUE a.ACCNO,
KEY 'transactions' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
KEY 'transID' VALUE t.trans_id,
KEY 'transDate' VALUE t.trans_dt,
KEY 'transAmount' VALUE t.trans_amt
)
ORDER BY trans_id
)
RETURNING CLOB
) AS json
-- INTO p_json_acct
FROM transactions t, accounts a
WHERE a.accno = t.accno
AND a.ACCNO = 101
GROUP BY a.ACCTYPE, a.ACCNAME, a.ACCNO) as json
),
'tdsdtls' VALUE treat(
(SELECT JSON_OBJECT(
KEY 'accno' VALUE ACCNO,
KEY 'tds_info' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
KEY 'tdsdate' VALUE TDS_DATE,
KEY 'tdsamt' VALUE TDS_AMOUNT
)
ORDER BY ACCNO
)
RETURNING CLOB
) AS json
-- INTO p_json_tdsinfo
FROM TDS_INFO
WHERE ACCNO = 101
GROUP BY ACCNO
) as json
)
)
FROM DUAL;
You can write the same query in a PLSQL block to make each json tag more readable.
Below is the sample block for the same.
DECLARE
p_json_versn CLOB;
p_json_acct CLOB;
p_json_tdsinfo CLOB;
p_json_all CLOB;
BEGIN
p_json_versn := '{
"Ver": "1.1"
}';
SELECT JSON_OBJECT(
KEY 'acctname' VALUE a.ACCNAME,
KEY 'accttype' VALUE a.ACCTYPE,
KEY 'accno' VALUE a.ACCNO,
KEY 'transactions' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
KEY 'transID' VALUE t.trans_id,
KEY 'transDate' VALUE t.trans_dt,
KEY 'transAmount' VALUE t.trans_amt
)
ORDER BY trans_id
)
RETURNING CLOB
) AS json
INTO p_json_acct
FROM transactions t, accounts a
WHERE a.accno = t.accno
AND a.ACCNO = 101
GROUP BY a.ACCTYPE, a.ACCNAME, a.ACCNO;
SELECT JSON_OBJECT(
KEY 'accno' VALUE ACCNO,
KEY 'tds_info' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
KEY 'tdsdate' VALUE TDS_DATE,
KEY 'tdsamt' VALUE TDS_AMOUNT
)
ORDER BY ACCNO
)
RETURNING CLOB
) AS json
INTO p_json_tdsinfo
FROM TDS_INFO
WHERE ACCNO = 101
GROUP BY ACCNO;
select json_object (
'Version' value treat ( p_json_versn as json ),
'acctdtls' value treat ( p_json_acct as json ),
'tdsdtls' value treat ( p_json_tdsinfo as json )
)
into p_json_all
from dual;
-- DBMS_OUTPUT.PUT_LINE(p_json_versn);
-- DBMS_OUTPUT.PUT_LINE(p_json_emp);
-- DBMS_OUTPUT.PUT_LINE(p_json_dept);
DBMS_OUTPUT.PUT_LINE(p_json_all);
END;
/
References
Thought for the day.
Blessings of Wisdom (On seeking wisdom)
Search out and seek, and she
will become known to you
and when you get hold of her,
do not let her go.
For at last you will find the rest she gives.
And she will be changed into joy for you.
Then her fetters will become for you a strong defense,
And her collar a glorious robe.
Her yoke is a golden ornament,
And her bonds a purple cord.
You will wear her like a glorious robe
And put her on like a splendid crown.
Sirach 6:27:31
No comments:
Post a Comment