MySQL Forums
Forum List  »  General

JSON Data from MySQL
Posted by: Adwait Joshi
Date: August 14, 2019 02:24AM

I am having a little bit of a trouble using JSON_ARRAYAGG and JSON_OBJECT and hopign the community can help. I have the following structure

column names are filedate, processor, filetype, recordtype, sourcefilename, destinationfilename, recordcount etc.

I use the following to get a JSON string for data

SELECT filedate, processor, filetype, JSON_ARRAYAGG(JSON_OBJECT('recordtype', recordtype, 'recordtypedata', rt)) ft
from
(
SELECT filedate, processor, filetype, recordtype, JSON_OBJECT('soucefilename', sourcefilename, 'destinationfilename', destinationfilename, 'reccount', recordcount) rt
FROM file_tracker ft where filedate='20190304'
) as pft group by filedate, processor, filetype

however the data comes out as follows

[{
"recordtype": "type1",
"recordtypedata": {
"reccount": 60,
"soucefilename": "filename.txt",
"destinationfilename": "filename.txt"
}
}, {
"recordtype": "type1",
"recordtypedata": {
"reccount": 68,
"soucefilename": "filename.txt",
"destinationfilename": "filename.txt"
}
}, {
"recordtype": "type2",
"recordtypedata": {
"reccount": 114,
"soucefilename": "filename.txt",
"destinationfilename": "filename.txt"
}
}, {
"recordtype": "type3",
"recordtypedata": {
"reccount": 144,
"soucefilename": "filename.txt",
"destinationfilename": "filename.txt"
}
}, {
"recordtype": "type4",
"recordtypedata": {
"reccount": 144,
"soucefilename": "filename.txtt",
"destinationfilename": "filename.txt"
}
}, {
"recordtype": "type5",
"recordtypedata": {
"reccount": 80,
"soucefilename": "filename.txt",
"destinationfilename": "filename.txt"
}
}]

If you notice the type1 field is repeated twice, instead it should be nested as a single JSON tag for Type1 with 2 children one for each detail. how can I do that?

Options: ReplyQuote


Subject
Written By
Posted
JSON Data from MySQL
August 14, 2019 02:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.