Combine CONCAT () and COALESCE () to generate JSON in MySQL

Question

I am building small JSON blocks from functions, and I need to filter null values ​​when quoting non-zero values ​​like this ( MySQL 5.0 , so there are no built-in JSON functions):

COALESCE(CONCAT('[', 
    group_concat(
        CONCAT('{ "key": "', REPLACE(a.val, '"', '\\"'), '"}') 
        SEPARATOR ', ')
, ']'), 'null') AS jsonval

which outputs something like this (this is the value that should be embedded in the full JSON block):

• with values: [{"key": "foo"}, {"key": "bar"}, {"key": "baz"}]
• without values ​​(NULL): null
• with an empty string: [{"key": ""}]

For each, a.valI want to add an entry to my list, but instead of a complete list instead of a string null, if the value is not found. It works very well, but I need to handle empty strings as well as values nullas null as a result of JSON.

REPLACE(), CONCAT() COALESCE() , , NULL ?

, .

Answer 1

!

COALESCE(CONCAT('[', 
    group_concat(
        CONCAT('{ "key": ',COALESCE(CONCAT('"', REPLACE(a.val, '"', '\\"'), '"'), 'null') ,'}') 
        SEPARATOR ', ')
, ']'), 'null') AS jsonval

:

COALESCE(CONCAT('[', 
    group_concat(
        CONCAT('{ "key": ',
            CASE WHEN a.val IS NULL THEN 'null'
                 WHEN a.val = ''    THEN 'null'
                 ELSE CONCAT('"', REPLACE(a.val, '"', '\\"'), '"')
            END
        ,'}')
    SEPARATOR ', ')
, ']'), 'null') AS jsonval