Geek asks and answers

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

I got an idea to write a function in MySQL so that I can pass a subquery so that it displays the JSON representation of this subquery.

I have quite a lot of data that I often extract from MySQL and then convert it to JSON to output the API. Could there be an increase in speed to write a MySQL function for this on an SQL server that simply returns 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 so, any tips on how I can get started?

+5
source share
4 answers

First, check out this thread (SQL Server) on StackOverflow.

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

+2

, JSON MySQl.

SELECT 
     CONCAT("[",
          GROUP_CONCAT(
               CONCAT("{username:'",username,"'"),
               CONCAT(",email:'",email),"'}")
          )
     ,"]") 
AS json FROM users;

MySQL-, JSON.

[
     {username:'mike',email:'mike@mikesplace.com'},
     {username:'jane',email:'jane@bigcompany.com'},
     {username:'stan',email:'stan@stanford.com'}
]

, JSON, UDF.

+2

SQL JSON, , , , SQL; SQL , - " " - . JSON, script, JSON - .

+1

Starting with MySQL 5.7, you can output JSON using JSON_ARRAYand JSON_MERGEamong other functions.

It works like this:

RETURN JSON_ARRAY('id':1,'name':'Molly','Age':24);

You may also have several documents:

RETURN JSON_MERGE('{"id":1,"name":"Molly","Age":24}', '{"id":2,"name":"Edward","Age":28}');
+1
source

More articles:


All Articles