Hubro Hubro - 2 months ago 14
SQL Question

Is it possible to write an SQL function that outputs JSON?

I just had the idea of writing a function in MySQL that I can pass a subquery to for it to output the JSON representation of that subquery.

I have quite a lot of data that I often fetch from MySQL, then convert it to JSON for API output. Could it perhaps be a speed increase to write a MySQL function to do this on the SQL server that just returns the JSON?

My imagination:

query('SELECT * FROM people');

// Output:
// +----+--------+-----+
// | id | name | Age |
// +----+--------+-----+
// | 1 | Molly | 24 |
// | 2 | Edward | 28 |
// +----+--------+-----+

query('JSON(SELECT * FROM people)');

// Output:
// [{"id":1,"name":"Molly","Age":24},{"id":2,"name":"Edward","Age":28}]

Possible? If yes, any clues to how I can start?

Val Val

First, look at this thread (SQL Server) on StackOverflow.

You can also see here for PL/JSON, and here for sql2json (PHP).