CODEWITHSUNDEEP
mysql
Mohit Mehrotra
Mohit Mehrotra

Reputation: 301

How to remove null attributes from my JSON in MySQL

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

Upvotes: 4

Views: 4680

Answers (3)

EricW
EricW

Reputation: 521

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.

Upvotes: 2

Fahad Anjum
Fahad Anjum

Reputation: 1256

How you can remove null keys using JSON_REMOVE function. $.dummy is used if the condition is false.

select json_remove(abc,
case when json_unquote(abc->'$.name') = 'null' then '$.name' else '$.dummy' end,
case when json_unquote(abc->'$.department') = 'null' then '$.department' else '$.dummy' end,
case when json_unquote(abc->'$.countryCode') = 'null' then '$.countryCode' else '$.dummy' end,
case when json_unquote(abc->'$.languageCode') = 'null' then '$.languageCode' else '$.dummy' end,
case when json_unquote(abc->'$.region') = 'null' then '$.region' else '$.dummy' end) 
from (
select cast('{ 
  "name" : "AAAA",
  "department" : "BBBB",
  "countryCode" : null,
  "languageCode" : null,
  "region" : "AP"
}' as json) as abc ) a

Output: 

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522787

The following query will work for removing a single key value pair, where the value is NULL:

SELECT JSON_REMOVE(col, '$.countryCode')
FROM yourTable
WHERE CAST(col->"$.countryCode" AS CHAR(50)) = 'null';

But, I don't see a clean way of doing multiple removals in a single update. We could try to chain the updates together, but that would be ugly and non readable.

Also, to check for your JSON null, I had to cast the value to text first.

Demo

Upvotes: 1

Related Questions