How to get rows from table as json array list format in mySQL

Question

I want to select rows information from table in MySQL. Rather than getting as it is, I want to get this information as JSON array list type.

For example,

A   B   C
=========
1   2   3 
2   3   4

I want it to become data like the followings.

{ rows: [ 
            { "A" :  1, "B" : 2, "C" : 3}, 
            { "A" :  2, "B" : 3, "C" : 4}
]}

@Strawberry I thought this function may be provided by MySQL itself and it is gonna be good function whenever I want to test REST APIs with json data. — verystrongjoe
– verystrongjoe, Commented Jan 30, 2018 at 9:24

Mittal Patel · Accepted Answer · 2018-01-30 09:16:41Z

1

In MySQL you can achieve as below:

SELECT CONCAT('{ rows:[', result, ']}') AS result1 FROM
(
SELECT GROUP_CONCAT('{', jsondata, '}' SEPARATOR ',') AS result FROM
(
  SELECT 
    CONCAT
    (
      '"A":'   , A  , ',' 
      '"B":', B, ','
      '"C":'  , C
    ) AS jsondata
  FROM test
) AS jsondata
) AS jsonsdata1;

SQL Demo link: http://sqlfiddle.com/#!9/95bbbc/2

answered Jan 30, 2018 at 9:16

Mittal Patel

2,74716 silver badges23 bronze badges

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

2 Comments

verystrongjoe Over a year ago

Thanks for sharing your code and help. Is this only way to do this? In this way, I should write every columns and data by myself. I have many tables with tons of columns.

Mittal Patel Over a year ago

You can also convert this at the server side like C#, PHP. It would be easy

Collectives™ on Stack Overflow

How to get rows from table as json array list format in mySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related