Convert fields with empty or null values to JSON data on MYSQL

Question

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.

Here is my current approach:

    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;

But the problem is the json_search output is incompatible with the json_remove input. ¿Any ideas?

Here's some example data:

-------------------------
| tag_1 | tag_2 | tag_3 |
-------------------------
|   x   |       |  null |
|       |   y   |   z   |
-------------------------

And what I spect as a result:

--------------------------------
| result                       |
--------------------------------
| {'tag_1': 'x'}               |
| {'tag_2': 'y', 'tag_3': 'z'} |
--------------------------------

Thanks.

Later I want too use the sentence to insert data in the json field... something like update leads set tagData = json_remove(... — Alvaro Quezada
– Alvaro Quezada, Commented Mar 15, 2019 at 14:35
Because SQL and JSON are both declarative languages, this combination which makes this question impossible to answer without knowing example data and expected results.. See Why should I provide an MCVE for what seems to me to be a very simple SQL query? for providing example data and expected results... — Raymond Nijland
– Raymond Nijland, Commented Mar 15, 2019 at 14:41
Does it have to be dynamic or is the number of columns known and fixed? — Raymond Nijland
– Raymond Nijland, Commented Apr 5, 2019 at 13:27
@RaymondNijland thanks a lot for your comment it put me on track to discover the solution. — Alvaro Quezada
– Alvaro Quezada, Commented Apr 9, 2019 at 5:10

Alvaro Quezada · Accepted Answer · 2019-04-09 05:44:48Z

2

I get the solution...

If somebody is interested in the solution....

With this data:

CREATE TABLE t (
  id INT(11) unsigned not null auto_increment,
  `tag_1` VARCHAR(255),
  `tag_2` VARCHAR(255),
  `tag_3` VARCHAR(255),
  primary key (id)
);

INSERT INTO t
  (`tag_1`, `tag_2`, `tag_3`)
VALUES
  ('x', '', null),
  ('x','x', null),
  ('x', '', 'x'),
  ('x','x','x'),
  (null, null, 'x')
  ;

Here is the query:

select id, json_objectagg(field, val) as JSON  from (
  select id, 'tag_1' field, tag_1 val from t union
  select id, 'tag_2' field, tag_2 val from t union 
  select id, 'tag_3' field, tag_3 val from t
) sub where val is not null and val != ''
group by sub.id;

The subquery pivots the data to use JSON_OBJECTAGG

id  field   val
1   tag_1   x
2   tag_1   x
3   tag_1   x
4   tag_1   x
5   tag_1   null
1   tag_2   ''
2   tag_2   x
...

And then the grouping with json_objectagg does the trick!

| id  | JSON                                       |
| --- | ------------------------------------------ |
| 1   | {"tag_1": "x"}                             |
| 2   | {"tag_1": "x", "tag_2": "x"}               |
| 3   | {"tag_1": "x", "tag_3": "x"}               |
| 4   | {"tag_1": "x", "tag_2": "x", "tag_3": "x"} |
| 5   | {"tag_3": "x"}                             |

Here is the DB Fiddle

Thanks a lot to @Raimond Nijland for his comment, it put me on track! :)

edited Apr 9, 2019 at 5:44

answered Apr 9, 2019 at 5:09

Alvaro Quezada

511 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arash Over a year ago

Yes I'm interested. Thank you. It didn't help me directly, but i learned from it.

Collectives™ on Stack Overflow

Convert fields with empty or null values to JSON data on MYSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related