mysqljsongroup-by

Group by a part of json array column


I have a table with path column that is json.

The values are something like:

[1]
[1,2,3]
[1,5,10]

I would like to GROUP BY the first value (in this case 1).


Solution

  • CREATE TABLE test (val JSON)
    SELECT '[1]' val UNION ALL
    SELECT '[1,2,3]' UNION ALL
    SELECT '[1,5,10]'UNION ALL
    SELECT '[2,2,3]' UNION ALL
    SELECT '[2,5,10]'UNION ALL
    SELECT '[123,5,10]';
    SELECT CAST(val AS CHAR) FROM test;
    SELECT val->"$[0]" + 0, COUNT(*) FROM test GROUP BY 1; 
    
    CAST(val AS CHAR)
    [1]
    [1, 2, 3]
    [1, 5, 10]
    [2, 2, 3]
    [2, 5, 10]
    [123, 5, 10]
    val->"$[0]" + 0 COUNT(*)
    1 3
    2 2
    123 1

    fiddle