Error when calling JSON_TABLE() in MySQL 5.7–compatible Amazon Aurora

Question

I am getting the below error when trying to use the JSON_TABLE() function in MySQL 5.7–compatible Amazon Aurora.

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(@json_col, '$.people[*]' COLUMNS ( name VARCHAR(40) PATH '$.na' at line 1

In Amzon Mysql JSON Documentation states that it supports a lot of JSON function. However JSON_TABLE is not listed among them.

I can execute the below query in Mysql 8(Which is not AWS Aurora) and it gives me the below result.

SET @json_col = '{
  "people": [
    {
      "name": "John Smith"
    },
    {
      "name": "Sally Brown"
    },
    {
      "name": "John Johnson"
    }
  ]
}';
SELECT * from JSON_TABLE(@json_col, '$.people[*]' COLUMNS (
  name VARCHAR(40)  PATH '$.name')
  ) people;

Is there any alternative that I can use in MySQL Aurora 5.7 to achieve the above result. I tried replacing JSON_EXTRACT. However, it results in showing the same error as above.

Answer 1

If you don't mind the complexity of temporary tables and stored procedures, this elaboration creates a temporary table to join against:

drop temporary table if exists temp_numbers;

create TEMPORARY TABLE IF NOT EXISTS temp_numbers(
    num INT
);

drop procedure if exists fill_num;

delimiter //
create procedure fill_num(in num int)
begin
    declare i int default 0;
    while (i < num) do
       insert into temp_numbers values (i);
       set i = i + 1;
    end while;
end
//
delimiter ;


SET @json_col = '{
  "people": [
    {
      "name": "John Smith"
    },
    {
      "name": "Sally Brown"
    },
    {
      "name": "John Johnson"
    }
  ]
}';

set @json_people = json_extract(@json_col,'$.people[*]');
call fill_num(json_length(@json_people));

select json_unquote(json_extract(@json_people,concat('$[',num,'].name'))) from temp_numbers;