How to query for Null or Missing Fields in Mysql X DevAPI?

How to query for null or missing field in Mysql X DevAPI?

I tried .find("age IS NULL") and .find("age = null") but both not work.

> db.createCollection('users')
> db.getCollection('users').add({ name: "foo", age: 30 })
> db.getCollection('users').add({ name: "bar", age: null })
> db.getCollection('users').find("age IS NULL")
Empty set (0.0003 sec)
> db.getCollection('users').find("age = null")
Empty set (0.0004 sec)

I'm able to reproduce that with the MySQL Shell (which I guess is what you are using) and with Connector/Node.js. I know for a fact that Connector/Node.js sends the correct datatype to the server (with the Shell you can check that with the --trace-proto option).

Mysqlx.Crud.Find {
  collection {
    name: "<some_schema>"
    schema: "users"
  }
  data_model: DOCUMENT
  criteria {
    type: OPERATOR
    operator {
      name: "is"
      param {
        type: IDENT
        identifier {
          document_path {
            type: MEMBER
            value: "age"
          }
        }
      }
      param {
        type: LITERAL
        literal {
          type: V_NULL
        }
      }
    }
  }
}

Which means it's some issue in the server.

In this case, looks like that X DevAPI expression is not resulting in the proper SQL query. If you look in the general log, you should see something like

SELECT doc FROM `<some_schema>`.`users` WHERE (JSON_EXTRACT(doc,'$.age') IS NULL)

The problem is that JSON_EXTRACT is returning null (JSON type) and not NULL (SQL "type"), and it is a limitation of the X Plugin.

One way for this to be fixed by the plugin is to replace JSON_EXTRACT() with JSON_VALUE(), which will return the proper NULL value in that case, but I don't know the implications of that.

As a workaround, you can always use

session.sql("select doc from `<some_schema>`.`users` where json_value(doc, '$.age') is null").execute()

In the meantime, I encourage you to report a bug at https://bugs.mysql.com/ using either the MySQL Server: Document Store: X Plugin or the MySQL Server: Document Store: MySQL Shell categories.

Disclaimer: I'm the lead developer of the MySQL X DevAPI Connector for Node.js