In SQL Server, you can use OPENJSON table-valued function to generate rows and columns from JSON data.
In MySQL you can use JSON_TABLE function, but its syntax and parameters are different.
SQL Server:
-- Convert JSON array to rows SELECT * FROM OPENJSON('[{"a":1, "b":"b1", "c":true}, {"a":2, "b":"b2", "c":false}]') WITH(a INT '$.a', b VARCHAR(30) '$.b', c BIT '$.c') t;
Result:
a | b | c |
1 | b1 | 1 |
2 | b2 | 0 |
In MySQL you can use JSON_TABLE function:
MySQL:
SELECT * FROM JSON_TABLE('[{"a":1, "b":"b1", "c":true}, {"a":2, "b":"b2", "c":false}]', '$[*]' COLUMNS(a INT PATH '$.a', b VARCHAR(30) PATH '$.b', c TINYINT PATH '$.c')) t;
Result:
a | b | c |
1 | b1 | 1 |
2 | b2 | 0 |
For more information, see SQL Server to MySQL Migration.