How to remove null attributes from my JSON in MySQL

Question

I am having a table which is storing the JSON values. Within these JSONs, the JSON is having null attributes like below :

{ 
  "name" : "AAAA",
  "department" : "BBBB",
  "countryCode" : null,
  "languageCode" : null,
  "region" : "AP"
}

I would like to write a query so that all the null attributes are removed from the output. For e.g. for the above-mentioned JSON, the resultant output JSON should be like this.

 {
   "name" : "AAAA",
   "department" : "BBBB",
   "region" : "AP"
 }

I would like to have a generic query which I can apply to any JSON to get rid of null attributes in MySQL (v5.7).

Answer 1

In case you don't know all the keys in advance:

  WITH j AS (SELECT CAST('{"a": 1, "b": "null", "c": null}' AS JSON) o)
SELECT j.o, (SELECT JSON_OBJECTAGG(k, JSON_EXTRACT(j.o, CONCAT('$."', jt.k, '"')))
               FROM JSON_TABLE(JSON_KEYS(o), '$[*]' COLUMNS (k VARCHAR(200) PATH '$')) jt
              WHERE JSON_EXTRACT(j.o, CONCAT('$."', jt.k, '"')) != CAST('null' AS JSON)) removed
  FROM j;

Outputs:

o	removed
{"a": 1, "b": "null", "c": null}	{"a": 1, "b": "null"}

And this will keep your keys with string value "null", which is different from json null.