JSON Data from MySQL
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?