title: MySQL many-many JSON aggregation merging duplicate keys
body: <p>I'm having trouble returning a JSON representation of a many-many join. My plan was to encode the columns returned using the following JSON format</p>
  <pre class="lang-json prettyprint-override"><code>{
    &quot;dog&quot;: [
      &quot;duke&quot;
    ],
    &quot;location&quot;: [
      &quot;home&quot;,
      &quot;scotland&quot;
    ]
  }
  </code></pre>
  <p>This format would handle duplicate keys by aggregating the results in a JSON array, howver all of my attempts at aggregating this structure so far have just removed duplicates, so the arrays only ever have a single element.</p>
  <h2>Tables</h2>
  <p>Here is a simplified table structure I've made for the purposes of explaining this query.</p>
  <h3>media</h3>
  <pre><code>| media_id | sha256                                                           | filepath             |
  | 1        | 33327AD02AD09523C66668C7674748701104CE7A9976BC3ED8BA836C74443DBC | /photos/cat.jpeg     |
  | 2        | 323b5e69e72ba980cd4accbdbb59c5061f28acc7c0963fee893c9a40db929070 | /photos/dog.jpeg     |
  | 3        | B986620404660DCA7B3DEC4EFB2DE80C0548AB0DE243B6D59DA445DE2841E474 | /photos/dog2.jpeg    |
  | 4        | 1be439dd87cd87087a425c760d6d8edc484f126b5447beb2203d21e09e2a8f11 | /photos/balloon.jpeg |
  </code></pre>
  <h3>media_metdata_labels_has_media (for many-many joins)</h3>
  <pre><code>| media_metadata_labels_label_id | media_media_id |
  | 1                              | 1              |
  | 2                              | 1              |
  | 3                              | 1              |
  | 1                              | 2              |
  | 4                              | 2              |
  | 5                              | 2              |
  | 1                              | 3              |
  | 6                              | 3              |
  | 7                              | 3              |
  | 8                              | 4              |
  | 9                              | 4              |
  </code></pre>
  <h3>media_metadata_labels</h3>
  <pre><code>| label_id | label_key | label_value |
  | 2        | cat       | lily        |
  | 4        | dog       | duke        |
  | 6        | dog       | rex         |
  | 1        | pet size  | small       |
  | 3        | location  | home        |
  | 7        | location  | park        |
  | 8        | location  | scotland    |
  | 9        | location  | sky         |
  | 5        | location  | studio      |
  </code></pre>
  <h2>My current attempt</h2>
  <p>My latest attempt at querying this data uses <code>JSON_MERGE_PRESERVE</code> with two arguments, the first is just an empty JSON object and the second is an invalid JSON document. It's invalid because there are duplicate keys, but I was hoping that <code>JSON_MERGE_PRESERVE</code> would merge them. It turns out <code>JSON_MERGE_PRESERVE</code> will only merge duplicates if they're not in the same JSON argument.</p>
  <p>For example, this won't merge two keys</p>
  <pre class="lang-sql prettyprint-override"><code>SET @key_one = '{}';
  SET @key_two = '{&quot;location&quot;: [&quot;home&quot;], &quot;location&quot;: [&quot;scotland&quot;]}';
  SELECT JSON_MERGE_PRESERVE(@key_one, @key_two);
  
  -- returns {&quot;location&quot;: [&quot;scotland&quot;]}
  </code></pre>
  <p>but this will</p>
  <pre class="lang-sql prettyprint-override"><code>SET @key_one = '{&quot;location&quot;: [&quot;home&quot;] }';
  SET @key_two = '{&quot;location&quot;: [&quot;scotland&quot;]}';
  SELECT JSON_MERGE_PRESERVE(@key_one, @key_two);
  
  -- returns {&quot;location&quot;: [&quot;home&quot;, &quot;scotland&quot;]}
  </code></pre>
  <p>So anyway, here's my current attempt</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT
    m.media_id,
    m.filepath,
    JSON_MERGE_PRESERVE(
      '{}',
      CAST(
        CONCAT(
          '{',
          GROUP_CONCAT(CONCAT('&quot;', l.label_key, '&quot;:[&quot;', l.label_value, '&quot;]')),
          '}'
        )
        AS JSON)
    )
    as labels
  FROM media AS m
  LEFT JOIN media_metadata_labels_has_media AS lm ON lm.media_media_id = m.media_id
  LEFT JOIN media_metadata_labels AS l ON l.label_id = lm.media_metadata_labels_label_id
  GROUP BY m.media_id, m.filepath
  -- HAVING JSON_CONTAINS(labels, '&quot;location&quot;', CONCAT('$.', '&quot;home&quot;')); -- this would let me filter on labels one they're in the correct JSON format
  </code></pre>
  <p>After trying different combinations of <code>JSON_MERGE</code>, <code>JSON_OBJECTAGG</code>, <code>JSON_ARRAYAGG</code>, <code>CONCAT</code> and <code>GROUP_CONCAT</code> this still leaves me scratching my head.</p>