OPENJSON Table-Valued Function - SQL Server to MySQL Migration

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.