What is the best way to output an array of objects from an express mysql api server?

Question

I have created express API servers using document-based databases (like MongoDB). I want to try out using a MySQL database but I am just stumped on how someone would go about using SQL to output in JSON an array of objects

Similar to this:

[
   {
     data1: "datavalue1",
       arr: [
         "value1",
         "value2",
         "value3"
       ]
  }
]

while mysql can output json (https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html) I'd suggest to use middleware server to perform conversion, here is php example https://stackoverflow.com/questions/383631/json-encode-mysql-results — Iłya Bursov, Aug 25 '22 at 02:14

score 0 · Answer 1 · answered Oct 17 '22 at 22:15

This should output that data structure you want.

SELECT JSON_ARRAYAGG(JSON_OBJECT( 'data1', t.data1,
                                  'arr', (SELECT JSON_ARRAYAGG(t2.vals) FROM tbl2 t2 WHERE t2.id = t.id))
) FROM tbl t;

For Reference

https://dev.mysql.com/doc/refman/8.0/en/json.html

What is the best way to output an array of objects from an express mysql api server?

1 Answers1