0

I am new to using snowflake. I have a table that includes the original and full JSON message from which the table was created. So when I query e.g. 

select * from dbo.TradeData

one of the columns is called JSON and contains the complete JSON message for that order. It will include keys which form the columns e..g part of the message in that column is: 

"ProdGrp": [
  {
    "prodID": "Confectionary:Choc",
    "prodIDSoruce": "104"
  },

how do I adapt my select* from dbo.TradeData query to include the nesting ProdGrp::ProdID as TAXONOMY (col name)? I hope this makes sense. I basically want to include a column into the table which wasn't already there from a JSON format from that table.

Squti
  • 4,171
  • 3
  • 10
  • 21
Maths12
  • 852
  • 3
  • 17
  • 31
  • Can you provide an example of the output that you want to achieve? It looks like ProdGrp contains an array and can have multiple values per row of json so would be interested to see what you'd expect as output. – Simon D Jan 23 '20 at 13:10

1 Answers1

0

Snowflake supports traversing JSON using a colon (:) between JSON column names

https://docs.snowflake.net/manuals/user-guide/querying-semistructured.html#traversing-semi-structured-data

So you may do something like:

select ProdGrp:prodID from dbo.TradeData as TAXONOMY;
manuelschipper
  • 117
  • 1
  • 12