MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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"}]');
SELECT * , JSON_EXTRACT(work_workers,'$[*].id') AS workersId FROM work_orders


work_workers
workersId


5b7b226964223a20223233227d2c207b226964223a2022323534227d2c207b226964223a2022353836227d5d
["23", "254", "586"]

5b7b226964223a20223132227d2c207b226964223a2022313830227d5d
["12", "180"]

5b7b226964223a202239227d2c207b226964223a202233227d2c207b226964223a202234227d2c207b226964223a202236227d5d
["9", "3", "4", "6"]


   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
9
5b7b226964223a202239227d2c207b226964223a202233227d2c207b226964223a202234227d2c207b226964223a202236227d5d

0
12
5b7b226964223a20223132227d2c207b226964223a2022313830227d5d

0
23
5b7b226964223a20223233227d2c207b226964223a2022323534227d2c207b226964223a2022353836227d5d

1
3
5b7b226964223a202239227d2c207b226964223a202233227d2c207b226964223a202234227d2c207b226964223a202236227d5d

1
180
5b7b226964223a20223132227d2c207b226964223a2022313830227d5d

1
254
5b7b226964223a20223233227d2c207b226964223a2022323534227d2c207b226964223a2022353836227d5d

2
4
5b7b226964223a202239227d2c207b226964223a202233227d2c207b226964223a202234227d2c207b226964223a202236227d5d

2
586
5b7b226964223a20223233227d2c207b226964223a2022323534227d2c207b226964223a2022353836227d5d

3
6
5b7b226964223a202239227d2c207b226964223a202233227d2c207b226964223a202234227d2c207b226964223a202236227d5d


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
5b7b226964223a20223233227d2c207b226964223a2022323534227d2c207b226964223a2022353836227d5d

254
5b7b226964223a20223233227d2c207b226964223a2022323534227d2c207b226964223a2022353836227d5d

586
5b7b226964223a20223233227d2c207b226964223a2022323534227d2c207b226964223a2022353836227d5d

12
5b7b226964223a20223132227d2c207b226964223a2022313830227d5d

180
5b7b226964223a20223132227d2c207b226964223a2022313830227d5d

9
5b7b226964223a202239227d2c207b226964223a202233227d2c207b226964223a202234227d2c207b226964223a202236227d5d

3
5b7b226964223a202239227d2c207b226964223a202233227d2c207b226964223a202234227d2c207b226964223a202236227d5d

4
5b7b226964223a202239227d2c207b226964223a202233227d2c207b226964223a202234227d2c207b226964223a202236227d5d

6
5b7b226964223a202239227d2c207b226964223a202233227d2c207b226964223a202234227d2c207b226964223a202236227d5d

work_workers	workersId
5b7b226964223a20223233227d2c207b226964223a2022323534227d2c207b226964223a2022353836227d5d	["23", "254", "586"]
5b7b226964223a20223132227d2c207b226964223a2022313830227d5d	["12", "180"]
5b7b226964223a202239227d2c207b226964223a202233227d2c207b226964223a202234227d2c207b226964223a202236227d5d	["9", "3", "4", "6"]