By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SET @json = '[{"id": 1,"name": "Leanne Graham","username": "Bret","email": "Sincere@april.biz","address": {"street": "Kulas Light","suite": "Apt. 556","city": "Gwenborough","zipcode": "92998-3874","geo": {"lat": "-37.3159","lng": "81.1496"}},"phone": "1-770-736-8031 x56442","website": "hildegard.org","company": {"name": "Romaguera-Crona","catchPhrase": "Multi-layered client-server neural-net","bs": "harness real-time e-markets"}},{"id": 2,"name": "Ervin Howell","username": "Antonette","email": "Shanna@melissa.tv","address": {"street": "Victor Plains","suite": "Suite 879","city": "Wisokyburgh","zipcode": "90566-7771","geo": {"lat": "-43.9509","lng": "-34.4618"}},"phone": "010-692-6593 x09125","website": "anastasia.net","company": {"name": "Deckow-Crist","catchPhrase": "Proactive didactic contingency","bs": "synergize scalable supply-chains"}}]';
SELECT idx + 1,
JSON_VALUE(single_object, '$.id') id,
JSON_VALUE(single_object, '$.name') name,
JSON_VALUE(single_object, '$.username') username,
JSON_VALUE(single_object, '$.email') email
FROM ( SELECT idx, JSON_EXTRACT(@json, CONCAT('$[', idx, ']')) single_object
FROM ( SELECT 0 idx UNION SELECT 1 UNION SELECT 2) idxs ) objects
HAVING id;
| idx + 1 | id | name | username | |
|---|---|---|---|---|
| 1 | 1 | Leanne Graham | Bret | Sincere@april.biz |
| 2 | 2 | Ervin Howell | Antonette | Shanna@melissa.tv |