0

Below is the data that is stored in one column of a table(table:stg, col:LN) and query that i used to access the columns:

{"locations": [{"id": "893d7ef0", "name": "Organization", "type": "region"},
{"id": "7ad8787c", "name": "CORONA", "type": "st", "st_id": "1127"}]}

Query used is:

select * from(
               select
                   replace(LN : locations.id , '"' , '')as  loc_id,
                   replace(LN : locations.name , '"' , '') as loc_name,
                   replace(LN : locations.type , '"' , '') as loc_type,               
                   replace(LN : locations.st_id , '"' , '') as loc_store_id

           from db.schema.STG)

The query is giving NULL in columns, is the issue. Any suggestions?

Gokhan Atil
  • 9,278
  • 2
  • 11
  • 24
Shashank Shekhar
  • 47
  • 1
  • 6
  • This is the Json data: { "locations": [ { "id": "893d7ef0", "name": "Organization", "type": "region" }, { "id": "7ad8787c", "name": "CORONA", "type": "st", "st_id": "1127" } ] } – Shashank Shekhar May 25 '20 at 18:06

1 Answers1

0

Can you try this one?

select 
replace( j.value:id  , '"' , '')as  loc_id,
replace( j.value:name , '"' , '') as loc_name,
replace( j.value:type , '"' , '') as loc_type,               
replace( j.value:st_id , '"' , '') as loc_store_id
from STG, table(flatten ( LN, path => 'locations' )) j;
Gokhan Atil
  • 9,278
  • 2
  • 11
  • 24
  • Can you help me with this: https://stackoverflow.com/questions/62952090/snowflake-query-please-help-me-with-the-below-data-and-query-desired-output-is – Shashank Shekhar Jul 17 '20 at 10:32