JSON_QUERY Function - SQL Server to MariaDB Migration

In SQL Server, you can query JSON data and extract an object or array (but not a scalar value) using JSON_QUERY function. In MariaDB you can use JSON_EXTRACT function.

SQL Server:

  -- Extract "color" object
  SELECT JSON_QUERY('{"color":{"name":"Red","category":"R"}}', '$.color');
  # {"name":"Red","category":"R"}
 
  -- Returns NULL as JSON_QUERY cannot be used to extract scalar values
  SELECT JSON_QUERY('{"name":"Red","category":"R"}', '$.name');
  # NULL
 
  -- If PATH is not specified, the entire JSON is returned i.e. equivalent to specifying '$' 
  SELECT JSON_QUERY('{"name":"Red","category":"R"}');
  # {"name":"Red","category":"R"}

In MariaDB you can use JSON_EXTRACT function:

MariaDB:

  -- Extract "color" object
  SELECT JSON_EXTRACT('{"color":{"name":"Red","category":"R"}}', '$.color');
  # {"name":"Red","category":"R"}
 
  -- Unlike SQL Server JSON_EXTRACT can be used to extract scalar values
  SELECT JSON_EXTRACT('{"name":"Red","category":"R"}', '$.name');
  # "Red"
 
  -- PATH must be specified, no default allowed
  SELECT JSON_EXTRACT('{"name":"Red","category":"R"}');
  # Incorrect parameter count in the call to native function 'JSON_EXTRACT'
 
  -- Use '$' explicitly to return the entire JSON
  SELECT JSON_EXTRACT('{"name":"Red","category":"R"}', '$');
  # {"name": "Red", "category": "R"}

For more information, see SQL Server to MariaDB Migration.