clear markdown compare help best fiddles feedback
≡
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601485 fiddles created (47959 in the last week).
CREATE TABLE `work_orders` ( `work_workers` json DEFAULT NULL ); INSERT INTO work_orders VALUES ('[{"id": "23"},{"id": "254"},{"id": "586"}]'); INSERT INTO work_orders VALUES ('[{"id": "12"},{"id": "180"}]'); INSERT INTO work_orders VALUES ('[{"id": "9"},{"id": "3"},{"id": "4"},{"id": "6"}]');
✓
✓
✓
✓
 hidden batch(es)
SELECT * , JSON_EXTRACT(work_workers,'$[*].id') AS workersId FROM work_orders
work_workers workersId
[{"id": "23"}, {"id": "254"}, {"id": "586"}] ["23", "254", "586"]
[{"id": "12"}, {"id": "180"}] ["12", "180"]
[{"id": "9"}, {"id": "3"}, {"id": "4"}, {"id": "6"}] ["9", "3", "4", "6"]
…
 hidden batch(es)
SELECT pos, CAST(JSON_EXTRACT( work_workers, CONCAT( '$[',pos,'].id') ) AS UNSIGNED) wId, work_workers FROM work_orders JOIN ( SELECT 0 as pos UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 ) tbl ON (pos)<JSON_LENGTH(work_workers)
pos wId work_workers
0 23 [{"id": "23"}, {"id": "254"}, {"id": "586"}]
0 12 [{"id": "12"}, {"id": "180"}]
0 9 [{"id": "9"}, {"id": "3"}, {"id": "4"}, {"id": "6"}]
1 254 [{"id": "23"}, {"id": "254"}, {"id": "586"}]
1 180 [{"id": "12"}, {"id": "180"}]
1 3 [{"id": "9"}, {"id": "3"}, {"id": "4"}, {"id": "6"}]
2 586 [{"id": "23"}, {"id": "254"}, {"id": "586"}]
2 4 [{"id": "9"}, {"id": "3"}, {"id": "4"}, {"id": "6"}]
3 6 [{"id": "9"}, {"id": "3"}, {"id": "4"}, {"id": "6"}]
…
 hidden batch(es)
SELECT CAST(wId as UNSIGNED) wId, work_workers FROM work_orders JOIN JSON_TABLE( work_workers, "$[*].id" COLUMNS( wId INT PATH "$" ) ) tbl ON 1=1
wId work_workers
23 [{"id": "23"}, {"id": "254"}, {"id": "586"}]
254 [{"id": "23"}, {"id": "254"}, {"id": "586"}]
586 [{"id": "23"}, {"id": "254"}, {"id": "586"}]
12 [{"id": "12"}, {"id": "180"}]
180 [{"id": "12"}, {"id": "180"}]
9 [{"id": "9"}, {"id": "3"}, {"id": "4"}, {"id": "6"}]
3 [{"id": "9"}, {"id": "3"}, {"id": "4"}, {"id": "6"}]
4 [{"id": "9"}, {"id": "3"}, {"id": "4"}, {"id": "6"}]
6 [{"id": "9"}, {"id": "3"}, {"id": "4"}, {"id": "6"}]
…
 hidden batch(es)