SQLize Online / PHPize Online  /  SQLtest Online

set @json='{"response":[{"id":23405434,"bdate":"29.10.1994","city":{"id":3488,"title":"Суворов"},"has_photo":1,"can_write_private_message":0,"site":"","last_seen":{"platform":2,"time":1713334069},"counters":{"albums":11,"badges":0,"audios":107,"followers":227,"friends":352,"gifts":109,"notes":1,"online_friends":23,"pages":89,"photos":217,"subscriptions":8,"videos":76,"video_playlists":0,"mutual_friends":0,"clips_followers":579},"sex":1,"first_name":"Валерия","last_name":"Еремкина"}]}'; select * from json_table( JSON_EXTRACT(@JSON, '$.response'), '$[*]' columns( first_name varchar(64) path '$.first_name', last_name varchar(64) path '$.last_name', user_id varchar(64) path '$.id', bdate varchar(64) path '$.bdate', city varchar(64) path '$.city.title', photo varchar(64) path '$.has_photo', can_message INT(64) path '$.can_write_private_message', site varchar(64) path '$.site', sex INT(64) path '$.sex', last_seen INT(64) path '$.last_seen.time', last_seen INT(64) path '$.last_seen.platform' ) ) as jt;