json_type is not a function

[carlsverre]

whereNull on a JSON expression generates this test json_type(JSON_EXTRACT_JSON(data, 'value1')) = 'NULL' which does not work in SingleStore.

Also: need to go through the tests in JsonWhereTest.php since it appears they aren't running against SingleStore. If they were, we would have caught this issue.

[fgilio]

Indeed, both where_null and where_not_null are not running against SingleStore and only assert that the SQL string matches the expected one (which is wrong).

Can you share the SQL that will work on SingleStore?

[carlsverre]

One way you can do it is try to extract as string - if the value is a literal "json null" then you will get back a SingleStore NULL.

MemSQL [test]> select json_extract_string(j, '') is null from j;
+------------------------------------+
| json_extract_string(j, '') is null |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (0.053 sec)

MemSQL [test]> select json_extract_string(j, '') from j;
+----------------------------+
| json_extract_string(j, '') |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.036 sec)

The other option is json_get_type:

MemSQL [test]> select json_get_type(j) = "null" from j;
+---------------------------+
| json_get_type(j) = "null" |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.037 sec)

I'm not sure which is more performant - probably json_get_type, since we don't have to process the value if it's not null.