{"id":596,"date":"2020-01-09T11:03:48","date_gmt":"2020-01-09T17:03:48","guid":{"rendered":"http:\/\/thenoyes.com\/littlenoise\/?p=596"},"modified":"2020-01-09T11:03:54","modified_gmt":"2020-01-09T17:03:54","slug":"aggregate-json-arrays","status":"publish","type":"post","link":"https:\/\/thenoyes.com\/littlenoise\/?p=596","title":{"rendered":"Aggregate JSON arrays"},"content":{"rendered":"

Given: a table with JSON arrays<\/p>\n

CREATE TABLE t (id int auto_increment primary key, d json);\nINSERT INTO t VALUES (1, '[\"apple\", \"apple\", \"orange\"]');\nINSERT INTO t VALUES (2, '[\"apple\", \"banana\", \"orange\", \"orange\", \"orange\"]');<\/pre>\n
The desired output is each row with a count of the unique objects:<\/p>\n
+------+----------------------------------------+\n| id   | fruitCount                             |\n+------+----------------------------------------+\n|    1 | {\"apple\": 2, \"orange\": 1}              |\n|    2 | {\"apple\": 1, \"banana\": 1, \"orange\": 3} |\n+------+----------------------------------------+<\/pre>\n
JSON_TABLE()<\/code> can transform the array into rows.<\/p>\n
SELECT id, fruit\nFROM t,\nJSON_TABLE(d,\n    \"$[*]\" COLUMNS (\n        fruit VARCHAR(100) PATH \"$\"\n    )\n) AS dt;\n\n+----+--------+\n| id | fruit  |\n+----+--------+\n|  1 | apple  |\n|  1 | apple  |\n|  1 | orange |\n|  2 | apple  |\n|  2 | banana |\n|  2 | orange |\n|  2 | orange |\n|  2 | orange |\n+----+--------+<\/pre>\n
(If you got an error there about either permissions or an unknown table\/column in the JSON_TABLE, upgrade to at least 8.0.14 to get past some bugs.)<\/p>\n
We can then use the ordinary aggregation functions:<\/p>\n
SELECT\n    id,\n    fruit,\n    COUNT(*)\nFROM t,\nJSON_TABLE(d,\n    \"$[*]\" COLUMNS (\n        fruit VARCHAR(100) PATH \"$\"\n    )\n) AS dt\nGROUP BY id, fruit;\n\n+----+--------+----------+\n| id | fruit  | COUNT(*) |\n+----+--------+----------+\n|  1 | apple  |        2 |\n|  1 | orange |        1 |\n|  2 | apple  |        1 |\n|  2 | banana |        1 |\n|  2 | orange |        3 |\n+----+--------+----------+<\/pre>\n
With a subquery or a common table expression, we can aggregate individual rows at a time (meaning you can also select other columns from t<\/code>). Then JSON_OBJECTAGG will combine those results into a single object.<\/p>\n
SELECT\n    id,\n    (\n        WITH cte AS (\n            SELECT fruit, COUNT(*) AS c\n            FROM JSON_TABLE(d, \n                \"$[*]\" COLUMNS (\n                    fruit VARCHAR(100) PATH \"$\"\n                )\n            ) AS dt\n            GROUP BY fruit\n        )\n        SELECT JSON_OBJECTAGG(fruit, c) FROM cte\n    ) AS fruitCount\nFROM t;\n\n+------+----------------------------------------+\n| id   | fruitCount                             |\n+------+----------------------------------------+\n|    1 | {\"apple\": 2, \"orange\": 1}              |\n|    2 | {\"apple\": 1, \"banana\": 1, \"orange\": 3} |\n+------+----------------------------------------+<\/pre>\n
et voil\u00e0<\/p>\n","protected":false},"excerpt":{"rendered":"
Given: a table with JSON arrays CREATE TABLE t (id int auto_increment primary key, d json); INSERT INTO t VALUES (1, ‘[“apple”, “apple”, “orange”]’); INSERT INTO t VALUES (2, ‘[“apple”, “banana”, “orange”, “orange”, “orange”]’); The desired output is each row with a count of the unique objects: +——+—————————————-+ | id | fruitCount | +——+—————————————-+ | […]<\/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-596","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-9C","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/596","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=596"}],"version-history":[{"count":7,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/596\/revisions"}],"predecessor-version":[{"id":603,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/596\/revisions\/603"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=596"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=596"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=596"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}