AWS Athena json_extract query from string field returns empty values

Question

I have a table in athena with this structure

CREATE EXTERNAL TABLE `json_test`(
  `col0` string , 
  `col1` string , 
  `col2` string , 
  `col3` string , 
  `col4` string , 
  )
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ( 
  'quoteChar'='\"', 
  'separatorChar'='\;')

A Json String like this is stored in "col4":

{'email': 'test_email@test_email.com', 'name': 'Andrew', 'surname': 'Test Test'}

I´m trying to make a json_extract query:

SELECT json_extract(col4 , '$.email') as email FROM "default"."json_test"

But the query returns empty values.

Any help would be appreciated.

is the json quoted? i only ask because i have seen the opencsvserde parse all the data and not spit erros, when you did a select col4 from json_test are you getting what you are expecting. — shaine, Jun 18 '18 at 10:45
Json isn´t quoted. If I do a select * from json_test I get all rows correctly — Fernando Byn, Jun 18 '18 at 16:40
if you do select col4 from json_test do you get the col with the json in it? — shaine, Jun 19 '18 at 10:17
i only ask as i just ran something similar and i had problems. — shaine, Jun 19 '18 at 10:18
yes, you are right, I do select col4 from json_test and I get the json string — Fernando Byn, Jun 21 '18 at 10:36

Piotr Findeisen · Answer 1 · 2019-02-01T19:10:48.657

The JSON needs to use double quotes (") for enclosing values.

Compare:

presto> SELECT json_extract('{"email": "test_email@test_email.com", "name": "Andrew"}' , '$.email');
            _col0
-----------------------------
 "test_email@test_email.com"

and

presto> SELECT json_extract('{''email'': ''test_email@test_email.com'', ''name'': ''Andrew''}', '$.email');
 _col0
-------
 NULL

(Note: '' inside SQL varchar literal mean single ' in the constructed value, so the literal here is the same format that in the question.)

If your string value is a "JSON with single quotes", you can try to fix it with replace(string, search, replace) → varchar

score 1 · Answer 2 · answered Jun 24 '18 at 15:37

1

The problem was the single quote char of the json string stored

{'email': 'test_email@test_email.com', 'name': 'Andrew', 'surname': 'Test Test'}

Changing to double quote

{"email": "test_email@test_email.com", "name": "Andrew", "surname": "Test Test"}

Athena Query works properly:

SELECT json_extract(col4 , '$.email') as email FROM "default"."json_test"

answered Jun 24 '18 at 15:37

Fernando Byn

97
1
2
7

3

please mark @Piotr Findeisen answer as correct then, since he was the one who provided you with the answer first – Radu Simionescu May 31 '19 at 08:22

AWS Athena json_extract query from string field returns empty values

2 Answers2

Linked