title: Convert fields with empty or null values to JSON data on MYSQL
body: <p>I have a table with several fields that I want to compile in just one json field. The problem is there several null and emtpy string values in those columns and y don't want to put them in the json, just store the relevant values. I want to do this with a minimum of queries as possible.</p>
  
  <p>Here is my current approach:</p>
  
  <pre class="lang-sql prettyprint-override"><code>    select
         json_remove(
           json_remove(json, json_search(json, 'one', 'null')),
           json_search(json, 'all', '')
         ) result
      from (
         select
                json_object(
                    'tag_1', coalesce(tag_1, 'null'),
                    'tag_2', coalesce(tag_2, 'null'),
                    'tag_3', coalesce(tag_3, 'null')
                  ) json
         from leads
       ) l2;
  </code></pre>
  
  <p>But the problem is the json_search output is incompatible with the json_remove input. ¿Any ideas? </p>
  
  <p>Here's some example data:</p>
  
  <pre><code>-------------------------
  | tag_1 | tag_2 | tag_3 |
  -------------------------
  |   x   |       |  null |
  |       |   y   |   z   |
  -------------------------
  
  </code></pre>
  
  <p>And what I spect as a result:</p>
  
  <pre><code>--------------------------------
  | result                       |
  --------------------------------
  | {'tag_1': 'x'}               |
  | {'tag_2': 'y', 'tag_3': 'z'} |
  --------------------------------
  </code></pre>
  
  <p>Thanks.</p>