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