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.
There exists a many-to-many relationship between blog entries and tags that get codified using a "join table": I have "blog entries" and I have "tags" (ex, "ColdFusion", "SQL", "JavaScript"). So, let's consider the database schema for my blog. With SQL like this, the easiest way to understand it is to see an example. Each row within the grouping generates a key-value pair to be added to the aggregate Object. Within this JSON Object, one column defines the key and one column defines the value. The JSON_OBJECTAGG() function allows us to gather data from two columns and returns them in a single, aggregated JSON Object. The JSON_ARRAYAGG() function gathers all the values in a given column and returns then in a single, aggregated JSON Array. Only, they allow us to extract that information into higher-level data structures, not just delimited strings. Just like MIN(), MAX(), and GROUP_CONCAT(), the JSON aggregation functions allow us to extract information from a set of rows that have been collapsed through row-grouping (ex, using GROUP BY).
But somehow, when I was learning about the JSON support in MySQL, I missed the fact two new aggregation functions were added as well:
MYSQL JSON COMPARE UPGRADE
And, that I was excited to see that such an upgrade would introduce the JSON (JavaScript Object Notation) column-type and relevant manipulation functions. I'll definitely be using these instead of GROUP_CONCAT() going forward in my MySQL 5.7.32 reporting.Ī while back, I mentioned that we were upgrading to MySQL 5.7.32 at work. I had never seen these before but, oh chickens, these MySQL functions seem awesome for group aggregation. That said, when I got to the documentation, I noticed two other methods documented right next to it: JSON_ARRAYAGG() and JSON_OBJECTAGG(). As such, I haven't committed the function signature to memory. I rarely use the GROUP_CONCAT() function since I've had several problems with it in the past, from failing silently to it bringing back incorrect data (which I believe was a bug in the version of MySQL I was using at the time). Understanding Ruby – Recursion Require "json" # Using Data = JSON.The other day, when aggregating some SQL data for a Customer Success report, I went to look up the semantics for the GROUP_CONCAT() function in MySQL.
0 kommentar(er)
