Извлечение строки из массива JSON как число

Question

В MySQL таблице mytable есть колонка json_col, в которой данные хранятся в формате JSON следующем виде:

{
    "items": [
        {
            "name":"foo",
            "timestamp":"1612872027"
        },
        {
            "name":"bar",
            "timestamp":"1612872027"
        }
    ]
}

я пытаюсь получить все строки, у которых элемент foo имеет timestamp больше какой-либо цифры, например нуля:

select * from mytable 
  where json_contains(json_extract(json_col, '$.items'), "'foo'", '$[0].name')
  and cast(json_extract(json_col, '$.items[0].timestamp') as bigint) > 0

Тут 2 проблемы:

foo может необязательно быть первым элементом, и непонятно как искать по всем элементам, а не только первому ($[*] не работает)
Не кастится timestamp в bigint - как в примере не работает

Версия mysql 5.7.25

UPD

https://www.db-fiddle.com/f/nyTstzKyvBUzPw1GsWCygD/0

Ожидаю, что первые 2 строки будут выбраны, так как содержат элемент foo и у него значение timestamp больше 0.
Третья не будет выбрана, потому что у foo значение timestamp не больше нуля.
Четвертая не будет выбрана, потому что не содержит элемента foo.

Answer 1

SELECT * 
FROM mytable
WHERE 0 + JSON_UNQUOTE(JSON_EXTRACT(json_col, REPLACE(JSON_UNQUOTE(JSON_SEARCH(json_col, 'one', 'foo')), 'name', 'timestamp'))) > 0;

fiddle

PS. Предполагается, что каждое значение json_col содержит только один объект, имеющий компонент "name": "foo". Если их несколько - будет проверен только первый из них.

БЛОГ НА HUSL

Извлечение строки из массива JSON как число

Ответы (1 шт):