By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test
SELECT '{"result": {"topResult": {"score": 1}, "middleResult": {"score": 2}}}' val UNION ALL
SELECT '{"result": {"bottomResult": {"score": 3}}}' UNION ALL
SELECT '{"result": {"bottomResult": {"not_score": 4}}}';
SELECT *
FROM test
CROSS JOIN JSON_TABLE(test.val,
'$.result.*' COLUMNS (score INT PATH '$.score')) jsontable;
| val | score |
|---|---|
| {"result": {"topResult": {"score": 1}, "middleResult": {"score": 2}}} | 1 |
| {"result": {"topResult": {"score": 1}, "middleResult": {"score": 2}}} | 2 |
| {"result": {"bottomResult": {"score": 3}}} | 3 |
| {"result": {"bottomResult": {"not_score": 4}}} | null |