But, I did just stumble upon these functions, so maybe there are use-cases I haven't considered yet. It seems that having structured data is always better than having a delimited list. Now that we have JSON (JavaScript Object Notation) aggregation functions in MySQL 5.7 (added in 5.7.22), I can't think of a reason that I'd want to use the GROUP_CONCAT() function anymore. And, the JSON_OBJECTAGG() function gathers each tag id and name and appends them to a single object (using the id column as the key and the name column as the value). And, when we run this SQL code, we get the following results:Īs you can see, the JSON_ARRAYAGG() function gathers each tag name and pushes it onto a single array. However, we can then extract that grouping information on a per-entry-row basis by using the JSON aggregation functions. Since we're using a GROUP BY on the blog entries, all of the many-to-many tag information is getting collapsed down into a grouping. information about the tags using our JSON functions above!ĬOUNT( * ) > 1 - To make the grouping more exciting! Since we're GROUPING on the blog entry records, all of the `INNER JOIN` tag JSON_OBJECTAGG( t.id, t.name ) AS tagIndexīlog_entry_tag_jn jn - Our many-to-many join table. Pull back all the tag names a single array. I'm going both the JSON_ARRAYAGG() and JSON_OBJECTAGG() functions in the same query so that we can compare the two results: Given this high-level schema, let's look at how I might query for blog entries and - for each blog entry record - pull back information about the tags associated with that entry.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |