mysql - 使用 JSON_EXTRACT 时遇到路径问题

Question

我有元数据存储在带有地理标记的 mysql 中。我想提取一组特定数据的纬度和经度，我这辈子都无法弄清楚将级别指定到值的语法

我需要单独的值，这样我就可以用它来检查该位置是否与记录的位置相匹配，如果不相距很远。 gps计算部分很简单。

我感兴趣的数据字符串如下所示:

"outcome_rating":{"value":"continue_support","timestamp":"2019-05-29 16:11:07", "geostamp":"lat=-29.787506666666665, long=31.022944999999996, alt=64.0, accuracy=8.2"}

该表称为“监控”该字段称为“devicemagic_metadata”

我的尝试

SELECT
    select JSON_EXTRACT(devicemagic_metadata, "$.outcome_rating"."$.geostamp"."$.lat")
FROM monitorings;

如有任何帮助或指导，我们将不胜感激

Answer 1

您当前的 JSON 内容无效，因为它确实包含一个未出现在大括号内的外键。看看下面正确的格式应该是什么。此解决方案假定您使用的是 MySQL 8+，它具有正则表达式替换功能。如果不是，那么您将需要一些非常丑陋的字符串函数调用来将纬度和经度与 JSON 值隔离开来。

WITH monitorings AS (
    SELECT '{"outcome_rating":{"value":"continue_support","timestamp":"2019-05-29 16:11:07", "geostamp":"lat=-29.787506666666665, long=31.022944999999996, alt=64.0, accuracy=8.2"}}' AS devicemagic_metadata
)

SELECT
    REGEXP_REPLACE(JSON_EXTRACT(devicemagic_metadata, "$.outcome_rating.geostamp"),
        '^"lat=([^,]+),.*$', '$1') AS lat,
    REGEXP_REPLACE(JSON_EXTRACT(devicemagic_metadata, "$.outcome_rating.geostamp"),
        '^.*long=([^,]+),.*$', '$1') AS lng
FROM monitorings;

Demo