结合CONCAT（）和COALESCE（）在MySQL中生成JSON

I'm building small JSON blocks from functions and I need to filter null values while quoting non-null values like this (MySQL 5.0, so no built-in JSON functions):

我正在从函数构建小的JSON块,我需要过滤空值,同时引用像这样的非空值(MySQL 5.0,所以没有内置的JSON函数):

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

which outputs something like this (this is a value to be embedded in a full JSON block):

输出类似这样的东西(这是一个嵌入完整JSON块的值):

with values: [{"key": "foo"}, {"key": "bar"}, {"key": "baz"}]

值为:[{“key”:“foo”},{“key”:“bar”},{“key”:“baz”}]

without values (NULL): null

没有值(NULL):null

with empty string: [{"key": ""}]

空字符串:[{“key”:“”}]

For each a.val I want to add an entry to my list but have the string null instead of the full list if no value is found. It works pretty well, but I need to handle empty strings as well as NULL values as null in resulting JSON.

对于每个a.val,我想在我的列表中添加一个条目,但如果没有找到值,则将字符串设置为null而不是完整列表。它工作得很好,但我需要在结果JSON中处理空字符串以及NULL值为null。

REPLACE(), CONCAT() and COALESCE() work pretty well together for null values, but empty strings are not detected, how can I handle NULL and empty string the same way ?

REPLACE(),CONCAT()和COALESCE()在空值上很好地协同工作,但是没有检测到空字符串,我怎样才能以相同的方式处理NULL和空字符串?

I've seen this question about striping null values, i'm looking for the opposite.

我已经看到了关于条带化空值的这个问题,我正在寻找相反的问题。

1 个解决方案

#1

Just iterating your idea!

只是迭代你的想法!

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

After your edit:

编辑后:

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

#1