🐥 Extracting unique IDs from JSON arrays in MySQL

When working with MySQL, it’s common to store arrays of IDs as JSON inside a column. For example, you might have a table that caches data and keeps a list of related IDs in a JSON column. Over time, you might want to extract a unique list of all these IDs across the table.

Consider a generic table like this:

CREATE TABLE `example_cache` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `entity_id` BIGINT UNSIGNED NOT NULL,
  `cache_key` VARCHAR(191) NOT NULL,
  `related_ids` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `example_cache_entity_id_cache_key_unique` (`entity_id`, `cache_key`),
  KEY `example_cache_entity_id_index` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Here, the related_ids column contains arrays of IDs:

[101, 102, 103, 104, 105]

In MySQL 8.0+, the cleanest way to get a unique list of IDs is to use JSON_TABLE with DISTINCT:

SELECT DISTINCT jt.related_id
FROM example_cache ec
JOIN JSON_TABLE(
  ec.related_ids,
  '$[*]' COLUMNS (
    related_id BIGINT PATH '$'
  )
) AS jt
WHERE ec.related_ids IS NOT NULL;

How it works

• JSON_TABLE(... '$[*]') converts each JSON array into individual rows. Each element becomes a row in the virtual table jt.
• DISTINCT ensures duplicate IDs across multiple rows are removed.
• WHERE ec.related_ids IS NOT NULL filters out rows without any IDs.

If you want unique IDs for a specific entity:

SELECT DISTINCT jt.related_id
FROM example_cache ec
JOIN JSON_TABLE(
  ec.related_ids,
  '$[*]' COLUMNS (
    related_id BIGINT PATH '$'
  )
) AS jt
WHERE ec.entity_id = 42
  AND ec.related_ids IS NOT NULL;

You can also aggregate the results back into a single JSON array:

SELECT JSON_ARRAYAGG(DISTINCT jt.related_id) AS related_ids
FROM example_cache ec
JOIN JSON_TABLE(
  ec.related_ids,
  '$[*]' COLUMNS (
    related_id BIGINT PATH '$'
  )
) AS jt
WHERE ec.related_ids IS NOT NULL;

Considerations

• This approach requires MySQL 8.0.4+.
• If your related_ids arrays grow large, consider normalizing them into a separate join table. Querying and indexing will be more efficient than querying JSON.
• JSON_TABLE provides a clean way to explode arrays without relying on string manipulation.

This method is a practical and idiomatic way to extract and deduplicate JSON array elements in MySQL.