0

I have table with the following data structure:

name | age |     date    | value
---------------------------------
John | 20  |  2020-01-01 |   5
Peter| 21  |  2020-01-02 |  6.5
John | 26  |  2019-02-26 |  1.8
John | 20  |  2029-10-13 |  0.7
Peter| 47  |  2020-01-18 | 11.3
Peter| 21  |  2020-02-01 | 41.7
John | 20  |  2020-01-22 |   4

I want only by Mysql 5.5.5 Stored Proc(no json aggregate functions) to get result like this:

{
  "John (20)" : {
    ["2020-01-01", 5],
    ["2029-10-13", 0.7],
    ["2029-10-13", 4]
  },
  "John (26)" : {
    ["2019-02-26", 1.8]
  },
  "Peter (21)" : {
    ["2020-01-02", 6.5],
    ["2020-02-01", 41.7],
  },
  "Peter (47)" : {
    ["2020-01-18", 11.3]
  }
}

And can't figure out how to make it. Only getting flat json....

   select concat('[', group_concat(
   '{"name":"',`name`,'",',
   '"age":"',`age`,'",',
   '"date":"',`date`,'",',
   '"value":',`value`, '}' separator ','), ']') from `data`
markalex
  • 8,623
  • 2
  • 7
  • 32
iMarh
  • 189
  • 16

1 Answers1

1

One option:

SELECT
  CONCAT(
    '{',
    GROUP_CONCAT(
      CONCAT(
        `der`.`name_age`,
        ': {',
        `der`.`date_value`,
        '}'
      )
      SEPARATOR ', '),
    '}'
  ) `json_result`
FROM (
  SELECT
    CONCAT(
      '"',
      `name`,
      ' (', `age`, ')"'
    ) `name_age`,
    GROUP_CONCAT(
      CONCAT(
        '["',
        `date`,
        '", ',
        `value`,
        ']'
      )
      SEPARATOR ', ') `date_value`
  FROM
    `data`
  GROUP BY
    `name`, `age`
) `der`;

See dbfiddle.

wchiquito
  • 16,177
  • 2
  • 34
  • 45
  • Maybe I have found something strange. No matter if I add " order by `date` asc " I receive randomly ordered result. But I need ordered by date asc. – iMarh Feb 12 '20 at 10:16
  • Have found the solution - "order by `date` asc" have to be inside group_concat() before separator - https://stackoverflow.com/questions/8631210/group-concat-order-by#8631273 – iMarh Feb 12 '20 at 10:28
  • And now wondering how to keep ordering of `der`.`name_age` in the same way like they stored in the table i.e. - John (20), Peter (21), John (26), Peter (47)...? – iMarh Feb 12 '20 at 10:58
  • Hi again. So now I'm wondering how to get every Nth (lets say every 5th) row in the JSON where "date":"value" group_concat() part is ? – iMarh Apr 12 '20 at 11:19