MySQL merging json arrays in group by

Question

I'm trying to merge a scalar array json field within a group by to have all the distinct values in one list.

Consider the following table:

CREATE TABLE transaction
(
    id                 INT UNSIGNED AUTO_INCREMENT,
    source_account_id  VARCHAR(32)                                          NOT NULL,
    target_account_ids JSON                                                 NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB CHARSET utf8mb4;

source_account_ids is a simple array of strings for example '["account1", "account2"]'. I'd like to gather all the target_account_ids of a single source to have a unified result. For example:

id	source_account_id	target_account_ids
1.	account1	'["account1", "account2"]'
2.	account1	'["account1", "account3"]'

And the desired result set would be:

source_account_id	target_account_ids
account1	'["account1", "account2", "account3"]'

I tried to play around with JSON_ARRAYAGG but it just adds the arrays within another array and basically results in an "endless" array.

@Shadow Yeah. Obviously this is what I'm trying to avoid. I could also process this in the code I'm using the data in, but that's not really optimal neither. Though if there's really no solution to my problem then I'll need to approach this from a different angle — emul
– emul, Commented Feb 6, 2023 at 20:11
Why are you trying to avoid storing the data in a way you are supposed to store in a relational database? — Shadow
– Shadow, Commented Feb 6, 2023 at 20:35

Bill Karwin · Accepted Answer · 2023-02-06 20:35:54Z

1

You have to explode the array with JSON_TABLE(), then reduce the values with DISTINCT, then you can recombine them with JSON_ARRAYAGG().

select source_account_id, json_arrayagg(target_account_id) as target_account_ids
from ( 
  select distinct source_account_id, j.account_id as target_account_id
  from transaction
  cross join json_table(target_account_ids, '$[*]' columns (account_id varchar(32) path '$')) as j
) as t
group by source_account_id;

GROUP_CONCAT() supports a DISTINCT keyword in its argument, but JSON_ARRAYAGG() doesn't (this feature has been requested: https://bugs.mysql.com/bug.php?id=91993).

If this seems like a lot of needless work, or if you can't use JSON_TABLE() because you're still using MySQL 5.7, then you should store multi-valued attributes in normal rows and columns, instead of using JSON.

answered Feb 6, 2023 at 20:35

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL merging json arrays in group by

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related