MYSQL Json remove objects from array based on duplicate field

Question

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

Does only 2 elements with shown keynames are present in each separate sub-object? — Akina
– Akina, Commented Mar 4, 2021 at 13:29

Akina · Accepted Answer · 2021-03-04 13:55:30Z

2

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.

edited Mar 4, 2021 at 13:55

answered Mar 4, 2021 at 13:47

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

2 Comments

Lehren Over a year ago

That works excellently, is it possible to make this for 5.7 as well? If not it still helps me a ton. Just wondering if I should upgrade to 8

Akina Over a year ago

is it possible to make this for 5.7 as well? No, this code needs at least 8.0.4

Collectives™ on Stack Overflow

MYSQL Json remove objects from array based on duplicate field

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest