Here is my table structure. (MariaDB OR MySql)

I would like to get the output like this

Values
1 = Good
2 = More Good
3 = Bad
4 = Very Bad

Here is my bad Query in MySQL

SET @ONE = 'Good';
SET @TWO = 'More Good';
SET @THREE = 'Bad';
SET @FOUR = 'Very Bad';

SELECT id,

CASE
    WHEN REPLACE(json_extract(data, '$.one'), '"', '') = 1 THEN @ONE
    WHEN REPLACE(json_extract(data, '$.one'), '"', '') = 2 THEN @TWO
    WHEN REPLACE(json_extract(data, '$.one'), '"', '') = 3 THEN @THREE
    WHEN REPLACE(json_extract(data, '$.one'), '"', '') = 4 THEN @FOUR
    ELSE 'NO'
END as One,

CASE
    WHEN REPLACE(json_extract(data, '$.two'), '"', '') = 1 THEN @ONE
    WHEN REPLACE(json_extract(data, '$.two'), '"', '') = 2 THEN @TWO
    WHEN REPLACE(json_extract(data, '$.two'), '"', '') = 3 THEN @THREE
    WHEN REPLACE(json_extract(data, '$.two'), '"', '') = 4 THEN @FOUR
    ELSE 'NO'
END as Two
From TableName;

CodePudding user response：

WITH
dictionary AS ( SELECT 1 id, 'Good' val  UNION ALL
                SELECT 2,    'More Good' UNION ALL
                SELECT 3,    'Bad'       UNION ALL
                SELECT 4,    'Very Bad'  ) 
SELECT test.id, dict_1.val One, dict_2.val Two
FROM test
JOIN dictionary dict_1 ON JSON_EXTRACT(test.data, '$.one')   0 = dict_1.id
JOIN dictionary dict_2 ON JSON_EXTRACT(test.data, '$.two')   0 = dict_2.id

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=4e7cee70ca62f4c19936bc2896d1791e

PS. Save "I would like to get the output like this" into separate dictionary table.

CodePudding user response：

You could use string functions, like CONCAT_WS() to create a comma separated list of all the string variables and SUBSTRING_INDEX() to pick the correct value:

SET @ONE = 'Good';
SET @TWO = 'More Good';
SET @THREE = 'Bad';
SET @FOUR = 'Very Bad';

SELECT id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(
         CONCAT_WS(',', @ONE, @TWO, @THREE, @FOUR),
         ',',
         json_extract(data, '$.one')
       ), ',', -1) One,
       SUBSTRING_INDEX(SUBSTRING_INDEX(
         CONCAT_WS(',', @ONE, @TWO, @THREE, @FOUR),
         ',',
         json_extract(data, '$.two')
       ), ',', -1) Two
FROM tablename;

See the demo.