IS JSON Condition - Oracle to MariaDB Migration

In Oracle, the IS JSON boolean condition checks if the specified value is valid JSON data. In MariaDB, you can use the JSON_VALID function.

Oracle:

  -- IS JSON returns TRUE for valid JSON, and FALSE for invalid
  SELECT
    CASE WHEN '{"a":1}' IS JSON THEN 'Ok' ELSE 'Not Valid' END AS c1
  FROM dual;

MariaDB - Oracle Compatibility:

  -- JSON_VALID returns 1 for valid JSON, and 0 for invalid
  SELECT
    CASE WHEN JSON_VALID('{"a":1}') = 1 THEN 'Ok' ELSE 'Not Valid' END AS c1;

Both queries return:

In Oracle, you can also use IS NOT JSON to check for invalid JSON data:

Oracle:

  -- IS NOT JSON returns TRUE for invalid JSON
  SELECT
    CASE WHEN 'abc' IS NOT JSON THEN 'Not JSON' ELSE 'JSON' END AS c1
  FROM dual;

MariaDB - Oracle Compatibility:

  -- JSON_VALID returns 0 for invalid JSON
  SELECT
    CASE WHEN JSON_VALID('abc') = 0 THEN 'Not JSON' ELSE 'JSON' END AS c1;

Both queries return:

For more information, see Oracle to MariaDB Migration - Oracle Compatibility Mode.