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.