MySQL 8.1.0 JSON JSONPath problem in SELECT

Question

Is there a reason the following SQL/JSON query (in the SELECT) is returning an error?

Error: [42000][3143] Invalid JSON path expression. The error is around character position 18.

Goal: Extract all the values from sensor_readings where "model" = "TMP117" of "metrics" where "name" = "temp_c".

I have tested this on https://jsonpath.com/ and other tools and works as expected.

MySQL is version 8.1.0 from SELECT @@version;

SELECT `datetime_created`,
       `datetime_created` as local_datetime_created,
       `raw_data` ->> '$.sensor_readings[?(@.model=="TMP117")].metrics[?(@.name=="temp_c")].value' as notWorking
FROM   `ps_data`.`devices_generic`;

The flow broken down is as follows:

1. Start with the root of the JSON: $.

2. Navigate to the "sensor_readings" array: sensor_readings

3. Filter the array to include only elements with "model" equal to "TMP117": [?(@.model=="TMP117")]

4. Navigate into the "metrics" array of the filtered element: [0].metrics

5. Finally, filter the "metrics" array to include only elements with "name" equal to "temp_c": [?(@.name=="temp_c")].value

Sample JSON in column raw_data:

{
    "device_info": {
        "device_id": "diJEJR5bpgY88qu98EubLx",
        "timestamp": "2023-10-16 22:40:01.854681+00:00",
        "network_ip": "192.168.1.112",
        "is_in_docker": true,
        "engine_version:": "0.01c",
        "datetime_created": "2023-10-16 22:40:01.854779"
    },
    "sensor_readings": [
        {
            "mfg": "core electronics",
            "model": "VEML6030",
            "range": "piicodev",
            "metrics": [
                {"name": "light_lux", "value": 112.32}
            ],
            "version": 0
        },
        {
            "mfg": "core electronics",
            "model": "SSD1306",
            "range": "piicodev",
            "metrics": [
                {"name": "SSD1306", "value": "connected"}
            ],
            "version": 0
        },
        {
            "mfg": "core electronics",
            "model": "TMP117",
            "range": "piicodev",
            "metrics": [
                {"name": "temp_c", "value": 19.1640625},
                {"name": "temp_f", "value": 66.4953125},
                {"name": "temp_k", "value": 292.3140625}
            ],
            "version": 0
        },
        {
            "mfg": "core electronics",
            "model": "ENS160",
            "range": "piicodev",
            "metrics": [
                {"name": "sensor_air_quality_operation", "value": "operating ok"},
                {"name": "aqi_value", "value": 2},
                {"name": "aqi_rating", "value": "good"},
                {"name": "tvoc_ppb_value", "value": 115},
                {"name": "tvoc_ppb_rating", "value": "good"},
                {"name": "eco2_value", "value": 574},
                {"name": "eco2_rating", "value": "excellent"}
            ],
            "version": 0
        },
        {
            "mfg": "core electronics",
            "model": "BUZZER",
            "range": "piicodev",
            "metrics": [
                {"name": "BUZZER", "value": "connected"}
            ],
            "version": 0
        },
        {
            "mfg": "core electronics",
            "model": "BME280",
            "range": "piicodev",
            "metrics": [
                {"name": "temp_c", "value": 17.67},
                {"name": "pres_pa", "value": 102893.9765625},
                {"name": "hum_rh", "value": 50.5078125},
                {"name": "pres_hpa", "value": 1028.939765625},
                {"name": "altitude_change", "value": -129.8058612186422}
            ],
            "version": 0
        }
    ]
}