0

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

Improve this question
3
  • The provided (not)JSON does not contain neither '$.id' nor $.finance.timing.position. Commented Jul 5, 2023 at 14:39
  • Please provide some valid sample data and query which matches it. Commented Jul 5, 2023 at 14:39
  • yes , because those are different column.So the table contains column1, column2, column3 and data I am trying to get is from column3 Commented Jul 5, 2023 at 15:00
Related questions
11654
Which JSON content type do I use?
9776
Can comments be used in JSON?
4129
Create ArrayList from array
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.