By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable (
id bigint not null auto_increment,
json_col json,
primary key (id)
);
insert into mytable (json_col) values
('{
"items": [
{
"name":"foo",
"timestamp":"1612872027"
},
{
"name":"bar",
"timestamp":"1612872027"
}
]
}'),
('{
"items": [
{
"name":"bar",
"timestamp":"1612872027"
},
{
"name":"foo",
"timestamp":"1612872027"
}
]
}'),
('{
"items": [
{
"name":"bar",
"timestamp":"1612872027"
| id | json_col |
|---|---|
| 1 | {"items": [{"name": "foo", "timestamp": "1612872027"}, {"name": "bar", "timestamp": "1612872027"}]} |
| 2 | {"items": [{"name": "bar", "timestamp": "1612872027"}, {"name": "foo", "timestamp": "1612872027"}]} |
| 3 | {"items": [{"name": "bar", "timestamp": "1612872027"}, {"name": "foo", "timestamp": "0"}]} |
| 4 | {"items": [{"name": "bar", "timestamp": "1612872027"}]} |
SELECT *
FROM mytable
WHERE 0 + JSON_UNQUOTE(JSON_EXTRACT(json_col, REPLACE(JSON_UNQUOTE(JSON_SEARCH(json_col, 'one', 'foo')), 'name', 'timestamp'))) > 0;
| id | json_col |
|---|---|
| 1 | {"items": [{"name": "foo", "timestamp": "1612872027"}, {"name": "bar", "timestamp": "1612872027"}]} |
| 2 | {"items": [{"name": "bar", "timestamp": "1612872027"}, {"name": "foo", "timestamp": "1612872027"}]} |