MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (id TEXT, value JSON)
SELECT 1 id, '
{
    "id": "000689ba-9891-4d82-ad13-a7b96dc08ec4",
    "type": "emp", 
    "firstName": "Brooke", 
    "facilities": {
            "0001": [{
            "id": 1, 
            "code": "125", 
            "name": "DSL", 
            "type": "MGMTSupport", 
            "payRate": 18}], 
        "0002": [
        {
            "id": 1, 
            "code": "100", 
            "name": "Server", 
            "type": "FOH", 
            "payRate": 8
        }, {
            "id": 2, 
            "code": "320", 
            "name": "NCFOHTrainer", 
            "type": "NCHourlyTraining", 
            "payRate": 14.5
        }]
    },
    "permissions": ["read:availability", "..."], 
    "primaryJobCode": "150", 
    "primaryPayRate": 9, 
    "primaryFacility": "0260"
}
' value;
/* Desired output
   id   | companyId | facilityId | code | payRate
--------+-----------+------------+------+---------
   1    |  324337   |    0001    |  125 | 18.0000
   1    |  324337   |    0002    |  100 |  8.0000
   1    |  324337   |    0002    |  320 | 14.5000
*/
WITH
cte AS (
SELECT test.id,
       test.value,
       jsontable.facilityId,
       CONCAT('$.facilities."', jsontable.facilityId, '"') path
FROM test
CROSS JOIN JSON_TABLE(JSON_KEYS(test.value, '$.facilities'),
                      '$[*]' COLUMNS (facilityId CHAR(4) PATH '$')) jsontable
)
SELECT cte.id,
       cte.facilityId,
       jsontable.code,
       jsontable.payRate
FROM cte
CROSS JOIN JSON_TABLE(JSON_EXTRACT(cte.value, cte.path),
                      '$[*]' COLUMNS (code CHAR(3) PATH '$.code',
                                      payRate DECIMAL(6, 4) PATH '$.payRate')) jsontable


id
facilityId
code
payRate


1
0001
125
18.0000

1
0002
100
8.0000

1
0002
320
14.5000