Борьба с путем при использовании JSON_EXTRACT

Question

У меня есть метаданные, которые хранятся в mysql с geostamps. Я хочу извлечь lat и long для определенного набора данных, которые я за всю жизнь не могу понять, синтаксис, чтобы указать уровень до значения

Мне нужно, чтобы значения разделились, чтобы я мог использовать его, чтобы проверить, совпадает ли это местоположение с местоположением в записи, и если не на расстоянии. Часть расчета GPS проста.

Интересующая меня строка данных выглядит следующим образом:

"result_rating":{"value":"continue_support","timestamp":"2019-05-29 16:11:07", "geostamp":"lat=-29.787506666666665, long=31.022944999999996, alt=64.0, точность =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;

демонстрация