How can I SELECT rows where a JSON key is bigger/smaller than a certain date?

Question:

My table contains a value json-column:

{
    "id":"string",
    "user":int,
    "history":{
        "unixtimestamp":{
            "progress":"string",
            "editorId":int
        },
        "unixtimestamp":{
            "progress":"string",
            "editorId":int
        },
        ...
    }
}

I try to get all rows where the unixtimestamp contains a key which is between given dates. I can get an array set of the keys by selecting JSON_KEYS(´value', '$.history'). But I can't find how to filter this.

Answer:

The function JSON_KEYS(t.value, '$.history') retrieves a JSON array containing all the keys within the history object, and JSON_TABLE(..., '$[*]' COLUMNS (ts_key ...)) transforms this array into a table format where each key becomes a separate row. By casting each key from a string to an unsigned integer using CAST(jt.ts_key AS UNSIGNED), you enable numeric comparison to filter only those keys that fall within the specified range in the WHERE clause. Joining this result back to the main table allows you to select only the rows where at least one timestamp key matches the condition. To avoid duplicates and return each row only once regardless of how many keys match, you can use the EXISTS clause instead of a direct join.

How it looks visually in dbForge Studio: