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


Test this:

WITH cte AS (
       ROW_NUMBER() OVER (PARTITION BY, json_parse.keyname ORDER BY json_parse.rowid) rn
FROM test
                      "$[*]" 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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *