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": "[email protected]"
    }, 
   { 
     "name": "someone",
     "email": "[email protected]"
    }

]

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
– GMB, Commented Jun 16, 2020 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
– Vasanth, Commented Jun 16, 2020 at 14:02
@GMB do you have any idea on this, how we can achieve this in My Sql — Vasanth
– Vasanth, Commented Jun 17, 2020 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
– underscore_d, Commented Jun 17, 2020 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
– Vasanth, Commented Jun 17, 2020 at 11:31

Erme Schultz · Accepted Answer · 2020-06-17 12:25:50Z

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, 2020 at 12:25

answered Jun 17, 2020 at 11:23

Erme Schultz

3795 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vasanth Over a year ago

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

Erme Schultz Over a year ago

Sorry. Let me see more precise: All I found in the official doc, is that mysql supports only this kind of functions natively: 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.

Vasanth Over a year ago

thank you @Erme Schultz,, do you have any idea on this stackoverflow.com/q/62344730/9066195

Erme Schultz Over a year ago

Sorry, but I don't know much about this issue.

Collectives™ on Stack Overflow

Exporting MySql table into JSON Object

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related