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

share your query

irfanengineer
– irfanengineer

2018-06-20 05:12:36 +00:00
Commented Jun 20, 2018 at 5:12 — irfanengineer
– irfanengineer, Commented Jun 20, 2018 at 5:12
you can use JSON_REMOVE function of mysql.

Fahad Anjum
– Fahad Anjum

2018-06-20 05:14:15 +00:00
Commented Jun 20, 2018 at 5:14 — Fahad Anjum
– Fahad Anjum, Commented Jun 20, 2018 at 5:14

EricW · Accepted Answer · 2021-11-10 02:57:56Z

2

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.

answered Nov 10, 2021 at 2:57

EricW

5214 silver badges7 bronze badges

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

1 Comment

Lukas Eder Over a year ago

This needs a COALESCE(JSON_OBJECTAGG(...), JSON_OBJECT()) for the edge case where o is an empty object

Tim Biegeleisen · Accepted Answer · 2018-06-20 05:26:25Z

1

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

edited Jun 20, 2018 at 5:26

answered Jun 20, 2018 at 5:20

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Comments

Fahad Anjum · Accepted Answer · 2018-06-20 05:33:35Z

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"}

chris · Accepted Answer · 2025-08-13 09:30:15Z

0

MySQL 8+ answer

JSON_MERGE_PATCH is the answer due to its feature of merging 2 objects with:

[...] the result of the merge is an object with the following members [...] All members of the second object which do not have a corresponding key in the first object, and whose value is not the JSON null literal.

SELECT JSON_MERGE_PATCH('{}', '{ "name" : "AAAA", "department" : "BBBB", "countryCode" : null, "languageCode" : null, "region" : "AP" }');

Result:

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

answered Aug 13 at 9:30

chris

3,25227 silver badges23 bronze badges

Collectives™ on Stack Overflow

How to remove null attributes from my JSON in MySQL

4 Answers 4

1 Comment

Demo

Comments

Comments

MySQL 8+ answer

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

MySQL 8+ answer

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related