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).

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).

• mysql
• json
• group-by

• GROUP BY path->"*[0]"? – Barmar Commented Feb 14 at 18:31
• Do you think extracting from a JSON array is different in GROUP BY? – Barmar Commented Feb 14 at 18:33

1 Answer 1

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; 

fiddle