aws athena - cast as json don't return json object
You cannot cast varchar value
[{hop=1, error=null, result=[{x=null, from=192.168.0.1, rtt=0.378, ttl=64, err=null, ittl=null, edst=null, late=null, mtu=null, size=68, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=192.168.0.1, rtt=0.314, ttl=64, err=null, ittl=null, edst=null, late=null, mtu=null, size=68, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=192.168.0.1, rtt=0.303, ttl=64, err=null, ittl=null, edst=null, late=null, mtu=null, size=68, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}]}, {hop=2, error=null, result=[{x=null, from=71.120.7.1, rtt=8.135, ttl=254, err=null, ittl=null, edst=null, late=null, mtu=null, size=28, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=71.120.7.1, rtt=0.769, ttl=254, err=null, ittl=null, edst=null, late=null, mtu=null, size=28, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=71.120.7.1, rtt=0.761, ttl=254, err=null, ittl=null, edst=null, late=null, mtu=null, size=28, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}]}, {hop=3, error=null, result=[{x=null, from=100.41.136.160, rtt=5.583, ttl=253, err=null, ittl=null, edst=null, late=null, mtu=null, size=68, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=100.41.136.160, rtt=1.749, ttl=253, err=null, ittl=null, edst=null, late=null, mtu=null, size=68, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=100.41.136.160, rtt=1.805, ttl=253, err=null, ittl=null, edst=null, late=null, mtu=null, size=68, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}]}, {hop=4, error=null, result=[{x=*, from=null, rtt=null, ttl=null, err=null, ittl=null, edst=null, late=null, mtu=null, size=null, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=*, from=null, rtt=null, ttl=null, err=null, ittl=null, edst=null, late=null, mtu=null, size=null, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=*, from=null, rtt=null, ttl=null, err=null, ittl=null, edst=null, late=null, mtu=null, size=null, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}]}, {hop=5, error=null, result=[{x=null, from=140.222.239.91, rtt=3.121, ttl=251, err=null, ittl=null, edst=null, late=null, mtu=null, size=28, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=140.222.239.91, rtt=2.984, ttl=251, err=null, ittl=null, edst=null, late=null, mtu=null, size=28, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=140.222.239.91, rtt=3.0, ttl=251, err=null, ittl=null, edst=null, late=null, mtu=null, size=28, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}]}, {hop=6, error=null, result=[{x=*, from=null, rtt=null, ttl=null, err=null, ittl=null, edst=null, late=null, mtu=null, size=null, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=*, from=null, rtt=null, ttl=null, err=null, ittl=null, edst=null, late=null, mtu=null, size=null, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=*, from=null, rtt=null, ttl=null, err=null, ittl=null, edst=null, late=null, mtu=null, size=null, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}]}, {hop=7, error=null, result=[{x=null, from=4.69.153.150, rtt=100.718, ttl=244, err=null, ittl=null, edst=null, late=null, mtu=null, size=28, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=4.69.153.150, rtt=100.748, ttl=244, err=null, ittl=null, edst=null, late=null, mtu=null, size=28, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=4.69.153.150, rtt=100.708, ttl=244, err=null, ittl=null, edst=null, late=null, mtu=null, size=28, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}]}, {hop=8, error=null, result=[{x=null, from=212.73.203.18, rtt=101.694, ttl=244, err=null, ittl=null, edst=null, late=null, mtu=null, size=68, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=212.73.203.18, rtt=101.954, ttl=244, err=null, ittl=null, edst=null, late=null, mtu=null, size=68, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=212.73.203.18, rtt=101.721, ttl=244, err=null, ittl=null, edst=null, late=null, mtu=null, size=68, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}]}, {hop=9, error=null, result=[{x=null, from=193.171.23.41, rtt=101.096, ttl=244, err=null, ittl=null, edst=null, late=null, mtu=null, size=28, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=193.171.23.41, rtt=101.132, ttl=244, err=null, ittl=null, edst=null, late=null, mtu=null, size=28, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=193.171.23.41, rtt=101.115, ttl=244, err=null, ittl=null, edst=null, late=null, mtu=null, size=28, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}]}, {hop=10, error=null, result=[{x=null, from=193.170.114.242, rtt=101.884, ttl=56, err=null, ittl=null, edst=null, late=null, mtu=null, size=40, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=193.170.114.242, rtt=101.695, ttl=56, err=null, ittl=null, edst=null, late=null, mtu=null, size=40, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}, {x=null, from=193.170.114.242, rtt=100.881, ttl=56, err=null, ittl=null, edst=null, late=null, mtu=null, size=40, flags=null, dstoptsize=null, hbhoptsize=null, icmpext=null}]}]to JSON, because it's not well-formed JSON representation. E.g. the keys must be quoted and values separated with colons, like this: {"hop":1, "error": null, "result": ...
That's because it's stored as a ROW type field and not JSON. You can access the different keys by doing
select field.key from table
You can check more about it in here