find all rows that have duplicate entries inside json column

Question

I am stuck with a problem where I have a table with JSON column like this:

ID|VALUE
1 |{"a":"text1234","b":"default"}
2 |{"a":"text1234","b":"default"}
3 |{"a":"text1234","b":"text234"}
4 |{"a":"text1234","b":"default2"}
5 |{"a":"text1234","b":"default2"}

I would like to get all rows where value "b" is duplicate, so with the table above I would get rows 1,2,4,5.

I tried to group rows by value->b

$value_ids = ProductsAttributesValues::groupBy("value->b")->get();

but when i dd($value_ids) rows are not grouped by value->default. And I can't find a way to group them, so I can then count them. Or would there be a better way with doing this?

Answer 1

Try the json_extract function:

select count(id) dup_count, json_extract(`value`,"$.b") as dup_value
from test
group by json_extract(`value`,"$.b") 
having dup_count>1
;
-- result set:
| dup_count | dup_value  |
+-----------+------------+
|         2 | "default"  |
|         2 | "default2" |

-- to get the id involved:
select id,dup_count,dup_value
from (select id,json_extract(`value`,"$.b") as dup_v
    from test) t1 
    join
    (select count(id) dup_count, json_extract(`value`,"$.b") as dup_value
    from test
    group by json_extract(`value`,"$.b") 
    having dup_count>1) t2
    on t1.dup_v=t2.dup_value
;
-- result set:
| id   | dup_count | dup_value  |
+------+-----------+------------+
|    1 |         2 | "default"  |
|    2 |         2 | "default"  |
|    4 |         2 | "default2" |
|    5 |         2 | "default2" |