Preuk Preuk - 7 months ago 76
SQL Question

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

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):

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):


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

  • without values (NULL):
    null

  • with empty string:
    [{"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.

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 ?

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

Answer

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
Comments