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:
Start with the root of the JSON: $.
Navigate to the "sensor_readings" array: sensor_readings
Filter the array to include only elements with "model" equal to "TMP117": [?(@.model=="TMP117")]
Navigate into the "metrics" array of the filtered element: [0].metrics
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
}
]
}