Exporting MySql table into JSON Object

Question

how to convert my mysql table result into json Object in database level

for example ,

SELECT json_array(
         group_concat(json_object( name, email))
FROM ....

it will produce the result as

[ 
   { 
     "name": "something",
     "email": "someone@somewhere.net"
    }, 
   { 
     "name": "someone",
     "email": "something@someplace.com"
    }

]

but what i need is i need to given my own query which may contains functions, subqueries etc.

like in postgres select row_to_json(select name,email,getcode(branch) from .....) then i will get the whole result as json object

in mysql is there any possibilities to do like this?

select jsonArray(select name,email,getcode(branch) from .....)

Why did you tag 3 different MySQL versions, and MariaDB? Please tag only the one database/version that you are using. — GMB, Jun 16 '20 at 13:32
@GMB I need to support all these versions and I know Json support is not available in mysql lower version(s) — Vasanth, Jun 16 '20 at 14:02
@GMB do you have any idea on this, how we can achieve this in My Sql — Vasanth, Jun 17 '20 at 10:39
Show an example of your complex query that needs to be converted to JSON. This is too broad without specifics. — underscore_d, Jun 17 '20 at 11:17
@underscore_d first of all thanks for considering my question. no need for complex query and all. just for simple query itself , for example i having table called **department** it has depid,deptname,salr etc.in below example i need to mention the name of the columns in that **json_object** function without mentioning the columns i can give query which is dynamic for ex:: select deptid,depname from department , example 2 ::: SELECT department_name, CURSOR(SELECT salary, commission_pct FROM employees e WHERE e.department_id = d.department_id) FROM departments d; — Vasanth, Jun 17 '20 at 11:31

Erme Schultz · Accepted Answer · 2020-06-17T12:25:50.603

1

I only found in official Mysql 8 and 5.7 documentation that it supports casting to JSON type. It includes a JSON_ARRAY function in MySQL 8 and 5.7, and JSON_ARRAYAGG function in MySQL 8. Please see the full JSON functions reference here.

It means that does not exist an easy mysql built-in solution to the problem.

Fortunately, our colleagues started a similar discussion here. Maybe you could find your solution there.

For one searching for well-defined attributes JSON casting, the solution is here.

edited Jun 17 '20 at 12:25

answered Jun 17 '20 at 11:23

Erme Schultz

319
5
17

1

thanks, but that is what i have mentioned already in question i can able to generate json like that but i need to mention the particular column names in that **json_object** function, but my requirement is my query is standard SQL format and its is dynamic like this select deptid,depname, getDepCode(depcode) from department. that why i mentioned in postgres sql if we pass the sql query to **row_to_json()** function it will convert the result table to **json_array** – Vasanth Jun 17 '20 at 11:54
1

Sorry. Let me see more precise: All I found in the official doc, is that mysql supports only this kind of functions natively: https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html. It means that does not exist MySQL easily native support as Postgres supports. I will edit the question to be more precise. And will point to the other discussions on StackOverflow and StackExchange. – Erme Schultz Jun 17 '20 at 12:19
thank you @Erme Schultz,, do you have any idea on this https://stackoverflow.com/q/62344730/9066195 – Vasanth Jun 17 '20 at 12:23
Sorry, but I don't know much about this issue. – Erme Schultz Jun 18 '20 at 22:33

Exporting MySql table into JSON Object

1 Answers1