I am trying to retrieve both parts of a JSON object through a stored procedure, for example from {bob:ben, 3:sue, James:cat} I want to get each part of the pair separately using sql. So it would be like this
col1 | col2
bob | ben
3 | sue
James| cat
How do i do this?
This is easy to achieve by using some of these functions JSON_KEYS, JSON_EXTRACT and JSON_CONCAT.
For example, you could create table iterating this select:
set @json = '{"bob:ben", "3":"sue", "James":"cat"}';
set @indx = 0;
set @keys = JSON_KEYS(@json);
select JSON_EXTRACT(@keys, CONCAT("$[", @indx, "]")) as colum1,
JSON_EXTRACT(@json,CONCAT("$.", JSON_EXTRACT(@keys, CONCAT("$[", @indx, "]")))) as colum2;
For further information you have all the JSON mariadb function in this link:
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments