Suppose you have these documents stored in a JSON column:<\/p>\n\n\n\n
CREATE TABLE tableName (id int auto_increment primary key, data JSON);\nINSERT INTO tableName (data) VALUES ('\n{\n \"2022\/03\/29\": [\"macguffin\", \"plot coupon\"], \n \"2022\/03\/31\": [\"lorem\", \"ipsum\"], \n \"2022\/04\/01\": [\"dolor\", \"sit amet\"], \n \"2022\/04\/02\": [\"silent engine\", \"radioactive ore\"], \n \"2022\/04\/03\": [\"pulpy briefcase\", \"Schmilblick\"]\n}\n'), ('\n{\n \"2022\/03\/30\": [\"Big Dumb Object\", \"Unobtanium\"], \n \"2022\/03\/31\": \"glowing orb\", \n \"2022\/04\/02\": \"blinker fluid\",\n \"2022\/04\/03\": \"hunted snipe\",\n \"2022\/04\/04\": [\"wild goose\", \"alien space bat\"]\n}\n');<\/pre>\n\n\n\nAnd suppose you want the values for each row from the range 29 March through 01 April.<\/p>\n\n\n\n
JSON has no idea what a “date” is, nor how to cover a “range” of values. But MySQL does. So, we can turn the keys into something that MySQL can work with, filter those keys with SQL, and then look up the values in JSON again.<\/p>\n\n\n\n
SELECT\n JSON_ARRAYAGG(\n JSON_EXTRACT(data, CONCAT('$.\"', DATE_FORMAT(d, '%Y\/%m\/%d'), '\"'))\n ) AS importantValues \nFROM\n tableName\n JOIN JSON_TABLE(\n REPLACE(JSON_KEYS(data), '\/', '-'),\n \"$[*]\" COLUMNS (d DATE PATH \"$\")\n ) AS jt \nWHERE d BETWEEN '2022-03-29' AND '2022-04-01'\nGROUP BY id;\n+---------------------------------------------------------------------------+\n| importantValues |\n+---------------------------------------------------------------------------+\n| [[\"macguffin\", \"plot coupon\"], [\"lorem\", \"ipsum\"], [\"dolor\", \"sit amet\"]] |\n| [[\"Big Dumb Object\", \"Unobtanium\"], \"glowing orb\"] |\n+---------------------------------------------------------------------------+<\/pre>\n\n\n\nLet’s break that down.<\/p>\n\n\n\n
JSON_KEYS gets an array of keys from a JSON object:<\/p>\n\n\n\n
SELECT JSON_KEYS(data) FROM tableName;\n+------------------------------------------------------------------------+\n| JSON_KEYS(data) |\n+------------------------------------------------------------------------+\n| [\"2022\/03\/29\", \"2022\/03\/31\", \"2022\/04\/01\", \"2022\/04\/02\", \"2022\/04\/03\"] |\n| [\"2022\/03\/30\", \"2022\/03\/31\", \"2022\/04\/02\", \"2022\/04\/03\", \"2022\/04\/04\"] |\n+------------------------------------------------------------------------+<\/pre>\n\n\n\nAt the moment, MySQL still understands any delimiter character between the date parts, in this case a forward slash. However, as of 8.0.29, this allowance is deprecated, so to be proper the slashes must be replaced with dashes:<\/p>\n\n\n\n
SELECT REPLACE(JSON_KEYS(data), '\/', '-') FROM tableName;\n+------------------------------------------------------------------------+\n| REPLACE(JSON_KEYS(data), '\/', '-') |\n+------------------------------------------------------------------------+\n| [\"2022-03-29\", \"2022-03-31\", \"2022-04-01\", \"2022-04-02\", \"2022-04-03\"] |\n| [\"2022-03-30\", \"2022-03-31\", \"2022-04-02\", \"2022-04-03\", \"2022-04-04\"] |\n+------------------------------------------------------------------------+<\/pre>\n\n\n\nJSON_TABLE turns JSON data into a table. The “$[*]” path selects the part of the expression that will be turned into the table. The COLUMNS section turns those values into MySQL dates.<\/p>\n\n\n\n
SELECT * FROM \n JSON_TABLE(\n '[\"2022\/03\/29\", \"2022\/03\/31\", \"2022\/04\/01\", \"2022\/04\/02\", \"2022\/04\/03\"]',\n \"$[*]\" COLUMNS (d DATE PATH \"$\")\n ) AS jt;\n+------------+\n| d |\n+------------+\n| 2022-03-29 |\n| 2022-03-31 |\n| 2022-04-01 |\n| 2022-04-02 |\n| 2022-04-03 |\n+------------+<\/pre>\n\n\n\nYou can provide a column name to JSON_TABLE, provided it comes from a table listed earlier in the FROM clause. Filtering and grouping is ordinary SQL. JSON_ARRAYGG is an aggregate function that turns rows into a JSON array; it’s the JSON version of GROUP_CONCAT.<\/p>\n\n\n\n
SELECT\n id, JSON_ARRAYAGG(d)\nFROM\n tableName\n JOIN JSON_TABLE(\n JSON_KEYS(data),\n \"$[*]\" COLUMNS (d DATE PATH \"$\")\n ) AS jt \nWHERE d BETWEEN '2022-03-29' AND '2022-04-01'\nGROUP BY id;<\/pre>\n\n\n\nJSON_EXTRACT returns the values from a given path. It requires an exact match for looking up keys, so those dashes must be turned back into slashes – REPLACE would work, but DATE_FORMAT is more obvious. It also needs the key wrapped in double quotes, and prepend “$.” to make it into a proper path.<\/p>\n","protected":false},"excerpt":{"rendered":"
Suppose you have these documents stored in a JSON column: CREATE TABLE tableName (id int auto_increment primary key, data JSON); INSERT INTO tableName (data) VALUES (‘ { “2022\/03\/29”: [“macguffin”, “plot coupon”], “2022\/03\/31”: [“lorem”, “ipsum”], “2022\/04\/01”: [“dolor”, “sit amet”], “2022\/04\/02”: [“silent engine”, “radioactive ore”], “2022\/04\/03”: [“pulpy briefcase”, “Schmilblick”] } ‘), (‘ { “2022\/03\/30”: [“Big Dumb Object”, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[4],"tags":[],"class_list":["post-667","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-aL","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/667","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=667"}],"version-history":[{"count":1,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/667\/revisions"}],"predecessor-version":[{"id":668,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/667\/revisions\/668"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=667"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=667"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=667"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}