Snowflake Searching string in semi structured data

Question

I have a table. There are many columns and rows. One column that I am trying to query in Snowflake has semi structured data. For example, when I query

select response
from table
limit 5

This is what is returned

[body={\n "id": "xxxxx",\n "object": "charge",\n "amount": 500,\n "amount_refunded": 0,\n "application": null,\n "application_fee": null,\n "application_fee_amount": null,\n "balance_transaction": null,\n "billing_details": {\n "address": {\n "city": null,\n "zip": "xxxxx",]

I want to select only the zip in this data. When I run code:

select response:zip
from table
limit 5

I get an error. SQL compilation error: error line 1 at position 21 Invalid argument types for function 'GET': (VARCHAR(16777216), VARCHAR(11))

Is there a reason why this is happening? I am new to snowflake so trying to parse out this data but stuck. Thanks!

Answer 1

Snowflake has very good documentation on the subject

For your specific case, have you attempted to use dot notation? It's the appropiate method for accessing JSON. So

Select result:body.zip
from table

Remember that you have your 'body' element. You need to access that one first with semicolon because it's a level 1 element. Zip is located within body so it's a level 2. Level 1 elements are accessed with semicolon, level 2 elements are accessed with dot notation.

Answer 2

I think you have multiple issues with this.

1. First I think your response column is not a variant column. Please run the below query and confirm

   SHOW COLUMNS ON table;

2. Even if the column is variant, the way the data is stored is not in a valid JSON format. You will need to strip the JSON part and then store that in the variant column.

Please do the first part and share the information, I will then suggest next steps. I wanted to put that in the comment but comment does not allow to write so many sentences.