Extract and display field values as headers dynamically in mysql 8

Question

My schema looks like,

CREATE TABLE `Log` (
 `id` int,
  `changeData` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The data looks like,

INSERT INTO `Log`
(`id`,`changeData`)
VALUES
(1,'{"value": 4680, "SalaryType": "Housing Allowance"} '),
(1,'{"value": 5000, "SalaryType": "Other"} '),
(2,'{"value": 10000, "SalaryType": "Housing Allowance"} '),
(2,'{"value": 5500, "SalaryType": "Other"} '),
(2,'{"value": 700, "SalaryType": "Transport"} ')
;

The plan is to show the salary type as column headers. The following query will work but this would mean hard coding every possible value for SalaryType in case statements, which could number in the tens and new ones are added everytime which would mean adding more case statements.

SELECT 
    `id`,
    MAX(CASE
        WHEN TRIM(BOTH '"' FROM (JSON_EXTRACT(changeData, '$.SalaryType'))) = 'Housing Allowance' THEN JSON_EXTRACT(changeData, '$.value')
        ELSE 0
    END) AS 'Housing Allowance',
    MAX(CASE
        WHEN TRIM(BOTH '"' FROM (JSON_EXTRACT(changeData, '$.SalaryType'))) = 'Other' THEN JSON_EXTRACT(changeData, '$.value')
        ELSE 0
    END) AS 'Other',
    MAX(CASE
        WHEN TRIM(BOTH '"' FROM (JSON_EXTRACT(changeData, '$.SalaryType'))) = 'Transport' THEN JSON_EXTRACT(changeData, '$.value')
        ELSE 0
    END) AS 'Transport'
FROM
    `Log`
GROUP BY `id`;

Output for the query is as below, which is correct,

id  Housing Allowance   Other   Transport
1   4680                5000      0
2   10000               5500      700

Is there a way to do this without hard coding each value for SalaryType in case statements?

dbfiddle

question from:https://stackoverflow.com/questions/65924552/extract-and-display-field-values-as-headers-dynamically-in-mysql-8

Answer 1

Waitting for answers