Question 162499 in Mysql Question posted in
The official MySQL documentation can be found here.

Can MySQL JSON column data be queried for null values?

I’m storing JSON data in JSON type column in MySQL database. Tried to filter extra_data column for where payment_address is not null. Unfortunately couldn’t find the right way.

These are the queries I tried:

SELECT * FROM orders WHERE extra_data->"$.payment_address" != NULL;
SELECT * FROM orders WHERE extra_data->"$.payment_address" IS NOT NULL;
SELECT * FROM orders WHERE extra_data->"$.payment_address" != "null";

For the extra_data column, JSON data is like this:

extra_data
{"tracking_number": "", "payment_amount": null, "payment_address": null}
{"tracking_number": "", "payment_amount": null, "payment_address": "testaddress"}

How can I query some field in JSON column for null/not null values?

Tags:

2

Answers


  1. 0

    You could use REPLACE to replace ‘null’ value into empty value, then get only not empty values :

    SELECT extra_data
FROM orders
where REPLACE(JSON_EXTRACT(extra_data, '$.payment_address'), 'null', '')  <> '' ;

    Result :

    {"payment_amount": null, "payment_address": "testaddress", "tracking_number": ""}

    Demo here

    Login or Signup to reply.
  2. 0

    This is likely related to bug 85755 which relates to the difference between JSON null and SQL NULL. Anyway, you can workaround this by using ->> or JSON_UNQUOTE instead:

    SELECT *
FROM orders
WHERE extra_data->>"$.payment_address" != 'null';

SELECT *
FROM orders
WHERE JSON_UNQUOTE(extra_data->"$.payment_address") != 'null';

    In both cases the output is:

    extra_data
    {"payment_amount": null, "payment_address": "testaddress", "tracking_number": ""}

    Demo on db-fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search