How to select rows as JSON object in MySQL

Question

I'd like to convert rows to JSON object in MySQL. For example with

| name  | count |
| apple |   1   |
| banana|   2   |

the expected JSON output would be

{apple: 1, banana: 2}

Answer 1

For MySQL 5.7 and 8.0 use json_objectagg

CREATE TABLE table1 (
  `name` VARCHAR(6),
  `count` INTEGER
);

INSERT INTO table1
  (`name`, `count`)
VALUES
  ('apple', '1'),
  ('banana', '2');

SELECT json_objectagg(`name`,`count`) jt FROM table1

| jt                        |
| :------------------------ |
| {"apple": 1, "banana": 2} |

db<>fiddle here