SELECT rows where a JSON key is bigger/smaller than

Question

my table contains a value json-column. The format is following:

{
    "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 a given date. I can get an array set of the keys by selecting JSON_KEYS(´value', '$.history'). But I can't find a solution how to filter this.

Thanks for help!

Answer 1

• JSON_KEYS(t.value, '$.history') to get the keys from history.
• JSON_TABLE to flatten the array of keys.
• CAST(j.t_key AS UNSIGNED) to convert to number and allow filtering.

SELECT 
  DISTINCT CAST(value AS CHAR) 
FROM 
  sample_table AS t 
  JOIN JSON_TABLE(
    JSON_KEYS(t.value, '$.history'), 
    '$[*]' COLUMNS (
      t_key VARCHAR(20) PATH '$'
    )
  ) AS j ON TRUE 
WHERE 
  CAST(j.t_key AS UNSIGNED)
    BETWEEN 1737100000 AND 1737290000

dbfiddle