1

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;

Result

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.

Harsha W
  • 3,162
  • 5
  • 43
  • 77
  • 1
    JSON_TABLE was implemented in MySQL 8.0.4. – Akina Jun 07 '21 at 10:54
  • @Akina Yes. But, Is there any alternative that I can use in MySQL Aurora 5.7(Using its available JSON functions) to achieve the above result. – Harsha W Jun 07 '21 at 11:04
  • 1
    Yes. You may parse using either JSON functions or common string functions. For this you may use base table which contains numbers from 1 to not less than max. amount of elements in JSON (static or dynamically generated), or you may use not query but iterative stored procedure. – Akina Jun 07 '21 at 11:16

2 Answers2

4
SELECT JSON_UNQUOTE(JSON_EXTRACT(@json_col, CONCAT('$.people[', num, '].name'))) name
FROM ( SELECT 0 num UNION ALL
       SELECT 1 UNION ALL
       SELECT 2 UNION ALL
       SELECT 3 UNION ALL
       SELECT 4 UNION ALL
       SELECT 5 ) numbers
HAVING name IS NOT NULL;

https://www.db-fiddle.com/f/gP4g2gpYgEq2XtARUf3c38/0

Akina
  • 39,301
  • 5
  • 14
  • 25
  • Hi Akina. Thank you for the answer. However, what if I need to retrieve a set of elements where the count is dynamic. E.g. 100 elements? Do I need to keep specifying indexes until 101? – Harsha W Jun 07 '21 at 14:43
2

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;
Eric Shieh
  • 697
  • 5
  • 11
  • Hi Eric, Thank you for the answer and this is a solution that I've been looking for in order to bypass the manual intervention by typing indexes. I will go through this and get back to you. – Harsha W Jun 13 '21 at 04:29