How to query multiple rows to a single row using MySql?

Question

I am trying to combine several rows in a table into a single row based on a condition.

For example, my table like this:

id    date        name    count1    count2    count3


1     2019-01-01  name1    8         10        5
2     2019-01-01  name2    12        5         20
3     2019-01-01  name3    15        5         10
4     2019-01-02  name4    12        5         40
5     2019-01-02  name5    3         5         10
6     2019-01-03  name6    10        2         7

And now, I want to the result of query to look like this:

{
 date:"2019-01-01",
 name1:{
      count1:8,
      count2:10,
      count3:15
    },
 name2:{
      count1:12,
      count2:5,
      count3:20
    },
 name3:{
      count1:15,
      count2:5,
      count3:10
    }
},
{
 date:"2019-01-02"
 name4:{
     count1:12,
     count2:5,
     count3:10
    },
 name5:{
     count1:3,
     count2:5,
     count3:10
   }
},
{
 date:"2019-01-03",
 name6:{
     count1:10,
     count2:2,
     count3:7
   }
}

How do I query a row that converts to json like above

Can someone help me in achieving this?

Note that any time you find yourself with enumerated columns (above, say, 2) alarm bells should start ringing. Your model is likely suboptimal. — Strawberry
– Strawberry, Commented Dec 1, 2019 at 17:14
I am not sure but it must be something like this ... SELECT count1 AS (count1.name), count2 AS (count2.name), count3 AS (count3.name) FROM yourtable GROUP BY date FOR JSON PATH — user4038080
– user4038080, Commented Dec 1, 2019 at 17:37
@HenryStack, that's a Microsoft SQL Server syntax. The question is tagged mysql. — Bill Karwin
– Bill Karwin, Commented Dec 1, 2019 at 19:10
ok, @BillKarwin he said :"mysql, sql" so it also could be some MSSQL .. he didn't specify :)) but I tried something in mariadb , scroll down ;)) — user4038080
– user4038080, Commented Dec 1, 2019 at 19:21
In the title of the question, the OP said "using MySQL". I think that's pretty clear. The sql tag means the SQL language, not Microsoft SQL Server. For that, folks should use the sql-server tag. — Bill Karwin
– Bill Karwin, Commented Dec 1, 2019 at 19:28

Bill Karwin · Accepted Answer · 2019-12-01 19:27:32Z

2

Source data:

+----+------------+-------+--------+--------+--------+
| id | date       | name  | count1 | count2 | count3 |
+----+------------+-------+--------+--------+--------+
|  1 | 2019-01-01 | name1 |      8 |     10 |      5 |
|  2 | 2019-01-01 | name2 |     12 |      5 |     20 |
|  3 | 2019-01-01 | name3 |     15 |      5 |     10 |
|  4 | 2019-01-02 | name4 |     12 |      5 |     40 |
|  5 | 2019-01-02 | name5 |      3 |      5 |     10 |
|  6 | 2019-01-03 | name6 |     10 |      2 |      7 |
+----+------------+-------+--------+--------+--------+

Here's a solution in MySQL 5.7:

select json_pretty(json_arrayagg(json_merge_patch(json_object('date', date), names))) as data 
from (
  select date, json_objectagg(name, counts) as names 
  from (
    select id, date, name, 
      json_object('count1', count1, 'count2', count2, 'count3', count3) as counts 
    from mytable group by id, date, name
  ) as t1 group by date
) as t2\G

Output:

*************************** 1. row ***************************
data: [
  {
    "date": "2019-01-01",
    "name1": {
      "count1": 8,
      "count2": 10,
      "count3": 5
    },
    "name2": {
      "count1": 12,
      "count2": 5,
      "count3": 20
    },
    "name3": {
      "count1": 15,
      "count2": 5,
      "count3": 10
    }
  },
  {
    "date": "2019-01-02",
    "name4": {
      "count1": 12,
      "count2": 5,
      "count3": 40
    },
    "name5": {
      "count1": 3,
      "count2": 5,
      "count3": 10
    }
  },
  {
    "date": "2019-01-03",
    "name6": {
      "count1": 10,
      "count2": 2,
      "count3": 7
    }
  }
]

answered Dec 1, 2019 at 19:27

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

2 Comments

user4038080 Over a year ago

What is that \G at the end ? is it your signature ?

Bill Karwin Over a year ago

See "Displaying Query Results Vertically" in dev.mysql.com/doc/refman/8.0/en/mysql-tips.html

user4038080 · Accepted Answer · 2019-12-01 19:17:46Z

I created a test table

CREATE TABLE `test` (
    `id` INT(11) NULL DEFAULT NULL,
    `date` DATE NULL DEFAULT NULL,
    `name` CHAR(50) NULL DEFAULT NULL,
    `count1` SMALLINT(6) NULL DEFAULT NULL,
    `count2` SMALLINT(6) NULL DEFAULT NULL,
    `count3` SMALLINT(6) NULL DEFAULT NULL
);

and the closest I get are two posibilities :

SELECT GROUP_CONCAT(DISTINCT date , name,':', JSON_OBJECT("count1", count1,'count2', count2,'count3', count3),'}') FROM test GROUP BY date ORDER BY date;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(DISTINCT date , name,':', JSON_OBJECT("count1", count1,'count2', count2,'count3', count3),'}')                                                                     |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2019-01-01name1:{"count1": 8, "count2": 10, "count3": 5}},2019-01-01name2:{"count1": 12, "count2": 5, "count3": 20}},2019-01-01name3:{"count1": 15, "count2": 5, "count3": 10}} |
| 2019-01-02name4:{"count1": 12, "count2": 5, "count3": 40}},2019-01-02name5:{"count1": 3, "count2": 5, "count3": 10}}                                                            |
| 2019-01-03name6:{"count1": 10, "count2": 2, "count3": 7}}                                                                                                                       |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

and

SELECT GROUP_CONCAT( DISTINCT date ,JSON_OBJECT(name , JSON_OBJECT("count1", count1,'count2', count2,'count3', count3))) FROM test GROUP BY date ORDER BY date;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT( DISTINCT date ,JSON_OBJECT(name , JSON_OBJECT("count1", count1,'count2', count2,'count3', count3)))                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2019-01-01{"name1": {"count1": 8, "count2": 10, "count3": 5}},2019-01-01{"name2": {"count1": 12, "count2": 5, "count3": 20}},2019-01-01{"name3": {"count1": 15, "count2": 5, "count3": 10}} |
| 2019-01-02{"name4": {"count1": 12, "count2": 5, "count3": 40}},2019-01-02{"name5": {"count1": 3, "count2": 5, "count3": 10}}                                                                |
| 2019-01-03{"name6": {"count1": 10, "count2": 2, "count3": 7}}                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

it is not 100% what you want but that's all I know

Collectives™ on Stack Overflow

How to query multiple rows to a single row using MySql?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related