Menu
I have a list of json objects (result attribute) as in the example :
select result from mytable limit 1
I get :
[{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}]}]
I try to get this attribute as json object (without "=") so I can use the results in my application that take only json data.
I try the function cast (myattribute as json) :
select cast (result as json) from mytable limit 1
But, I get :
[[1,null,[[null,"192.168.0.1",0.378,64,null,null,null,null,null,null,68,null,null,null,null],[null,"192.168.0.1",0.314,64,null,null,null,null,null,null,68,null,null,null,null],[null,"192.168.0.1",0.303,64,null,null,null,null,null,null,68,null,null,null,null]]],[2,null,[[null,"71.120.7.1",8.135,254,null,null,null,null,null,null,28,null,null,null,null],[null,"71.120.7.1",0.769,254,null,null,null,null,null,null,28,null,null,null,null],[null,"71.120.7.1",0.761,254,null,null,null,null,null,null,28,null,null,null,null]]],[3,null,[[null,"100.41.136.160",5.583,253,null,null,null,null,null,null,68,null,null,null,null],[null,"100.41.136.160",1.749,253,null,null,null,null,null,null,68,null,null,null,null],[null,"100.41.136.160",1.805,253,null,null,null,null,null,null,68,null,null,null,null]]],[4,null,[["*",null,null,null,"*",null,null,null,null,null,null,null,null,null,null],["*",null,null,null,"*",null,null,null,null,null,null,null,null,null,null],["*",null,null,null,"*",null,null,null,null,null,null,null,null,null,null]]],[5,null,[[null,"140.222.239.91",3.121,251,null,null,null,null,null,null,28,null,null,null,null],[null,"140.222.239.91",2.984,251,null,null,null,null,null,null,28,null,null,null,null],[null,"140.222.239.91",3.0,251,null,null,null,null,null,null,28,null,null,null,null]]],[6,null,[["*",null,null,null,"*",null,null,null,null,null,null,null,null,null,null],["*",null,null,null,"*",null,null,null,null,null,null,null,null,null,null],["*",null,null,null,"*",null,null,null,null,null,null,null,null,null,null]]],[7,null,[[null,"4.69.153.150",100.718,244,null,null,null,null,null,null,28,null,null,null,null],[null,"4.69.153.150",100.748,244,null,null,null,null,null,null,28,null,null,null,null],[null,"4.69.153.150",100.708,244,null,null,null,null,null,null,28,null,null,null,null]]],[8,null,[[null,"212.73.203.18",101.694,244,null,null,null,null,null,null,68,null,null,null,null],[null,"212.73.203.18",101.954,244,null,null,null,null,null,null,68,null,null,null,null],[null,"212.73.203.18",101.721,244,null,null,null,null,null,null,68,null,null,null,null]]],[9,null,[[null,"193.171.23.41",101.096,244,null,null,null,null,null,null,28,null,null,null,null],[null,"193.171.23.41",101.132,244,null,null,null,null,null,null,28,null,null,null,null],[null,"193.171.23.41",101.115,244,null,null,null,null,null,null,28,null,null,null,null]]],[10,null,[[null,"193.170.114.242",101.884,56,null,null,null,null,null,null,40,null,null,null,null],[null,"193.170.114.242",101.695,56,null,null,null,null,null,null,40,null,null,null,null],[null,"193.170.114.242",100.881,56,null,null,null,null,null,null,40,null,null,null,null]]]]
As you see, the keys are omitted ! Any help?
734
JavaScript Object Notation (JSON) is a common method for encoding data structures as text. Many applications and tools output data that is JSON-encoded. In Amazon Athena, you can create tables from external data and include the JSON-encoded data in them. For such types of source data, use Athena together with JSON SerDe Libraries.
Many applications and tools output data that is JSON-encoded. In Amazon Athena, you can create tables from external data and include the JSON-encoded data in them. For such types of source data, use Athena together with JSON SerDe Libraries.
To find if there are invalid JSON rows or file names in the Athena table, do the following: 1. Create a table with a delimiter that's not present in the input files. Run a command similar to the following:
AWS Athena requires that your JSON objects be single line. That means you can’t have pretty printed JSON in your S3 bucket. { "key" : "value", "hats" : ["sombrero", "bowler", "panama"] }
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
195
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": ...
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
