CODEWITHSUNDEEP
jsonarraylistamazon-athenaunnestjson-extract
dips
dips

Reputation: 3

Extract Json field data from array

Trying to query on AWS Athena table which have column containing data as below :

[{'abc': {'field1': 1683215785219, 'field2': None, 'field3': None, 'field4': None, 'field5': 1}, 'emp': '40', 'indicator': False, 'Description': 'AAP: Authorization failure'},{'abc': {'field1': 168321570000, 'field2': None, 'field3': None, 'field4': None, 'field5': 1}, 'emp': '400', 'indicator': False, 'Description': 'AAP: Authorization failure'}]

while querying on athena.

Need to get the emp, indicator, Description value but returning empty result.

select
Description as DESC
from(
select  
   json_extract_scalar(replace(replace(column1, chr(39), chr(34)), ': False', ': false'),'$.id') as id,
   json_format(json_extract(replace(replace(column2 ,chr(39), chr(34)),'None','""'), '$.finance.timing.position'))  as position,column3
from "tablename"  limit 20),
  unnest(regexp_extract_all(column3, '''Description''\s?:\s?\b(\w*)\b', 1)) as t(Description);

this query returns empty result.

Also tried using json_parse but that give INVALID_FUNCTION_ARGUMENT: Cannot convert to json

Upvotes: 0

Views: 40

Answers (0)

Related Questions