AWS Athena json_extract query from string field renvoie des valeurs vides

Question

J'ai une table dans athena avec la structure suivante

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'='\;') 

Une chaîne Json comme celle-ci est stockée dans "col4" :

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

J'essaie de faire une requête json_extract :

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

Mais la requête renvoie des valeurs vides.

Toute aide serait appréciée.

Answer 1

Le JSON doit utiliser des guillemets doubles ( " ) pour entourer les valeurs.

Comparez :

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

et

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

(Note : '' dans SQL varchar literal signifie simple ' dans la valeur construite, donc le littéral ici est du même format que dans la question).

Si votre valeur de chaîne est un "JSON avec des guillemets simples", vous pouvez essayer de le corriger avec replace(string, search, replace) → varchar

Answer 2

Le problème était le caractère guillemet simple de la chaîne json stockée.

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

Changement en guillemets

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

Athena Query fonctionne correctement :

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