MYSQL Json remove objects from array based on duplicate field

Let’s say I try to insert some json data in my column like this:

[{"key": "a", "value": 1}, {"key":"a", "value": 20}]

The same key-value object could occur multiple times in the array. But there is only one array in the column.

I would like to have it so that only the first occurence of the same key gets entered into the database, in this array.

So the end result would be

[{"key": "a", "value": 1}]

Either that or after inserting a separate SQL update statement to filter out all the duplicates. Is that possible with Mysql 5.7, or 8?

My situation is similar to this question, but for MYSQL

Answer

Test this:

WITH cte AS (
SELECT test.id, 
       json_parse.*, 
       ROW_NUMBER() OVER (PARTITION BY test.id, json_parse.keyname ORDER BY json_parse.rowid) rn
FROM test
CROSS JOIN JSON_TABLE(val,
                      "$[*]" COLUMNS (rowid FOR ORDINALITY,
                                      keyname VARCHAR(255) PATH "$.key",
                                      keyvalue VARCHAR(255) PATH "$.value")) json_parse
)
SELECT id, JSON_ARRAY(JSON_OBJECTAGG(keyname, keyvalue)) output
FROM cte
WHERE rn = 1
GROUP BY id

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=de17680ab3f962359c7c2d40e26a5c8c

If keynames may vary (are dynamic) then gather all present keynames (JSON_KEYS() function) then build correct SQL text and execute as prepared statement. Use above code as a pattern.