Need help parsing this in snowflake

Question

[ { "legs": [ { "points": [ { "latitude": 29.76524, "longitude": -95.35406 }, { "latitude": 30.74438, "longitude": -101.71203 }, { "latitude": 30.74421, "longitude": -101.71247 }, { "latitude": 30.74404, "longitude": -101.71289 }, { "latitude": 30.74366, "longitude": -101.71374 }, { "latitude": 30.74346, "longitude": -101.71415 }, { "latitude": 30.74303, "longitude": -101.71495 }, { "latitude": 30.74274, "longitude": -101.71543 }, { "latitude": 30.74234, "longitude": -101.71606 }, { "latitude": 31.82985, "longitude": -102.34753 }, { "latitude": 31.8302, "longitude": -102.34597 }, { "latitude": 31.83029, "longitude": -102.34557 }, { "latitude": 31.83038, "longitude": -102.34526 }, { "latitude": 31.83051, "longitude": -102.3448 }, { "latitude": 31.83081, "longitude": -102.344 }, { "latitude": 31.83099, "longitude": -102.34356 }, { "latitude": 31.83113, "longitude": -102.34328 }, { "latitude": 31.83145, "longitude": -102.34271 }, { "latitude": 31.83174, "longitude": -102.34226 }, { "latitude": 31.83207, "longitude": -102.34181 }, { "latitude": 31.83267, "longitude": -102.34109 }, { "latitude": 31.83317, "longitude": -102.34053 }, { "latitude": 31.83359, "longitude": -102.34007 }, { "latitude": 31.8339, "longitude": -102.33971 }, { "latitude": 31.83499, "longitude": -102.33852 }, { "latitude": 31.83547, "longitude": -102.338 }, { "latitude": 31.83553, "longitude": -102.33793 }, { "latitude": 31.83685, "longitude": -102.33648 }, { "latitude": 31.83764, "longitude": -102.3356 }, { "latitude": 31.83838, "longitude": -102.33479 }, { "latitude": 31.84575, "longitude": -102.32666 }, { "latitude": 31.84603, "longitude": -102.32636 }, { "latitude": 31.84679, "longitude": -102.32551 }, { "latitude": 31.84878, "longitude": -102.32333 }, { "latitude": 31.85095, "longitude": -102.32094 }, { "latitude": 31.85131, "longitude": -102.32054 }, { "latitude": 31.85134, "longitude": -102.32044 }, { "latitude": 31.85259, "longitude": -102.31886 }, { "latitude": 31.85273, "longitude": -102.31859 }, { "latitude": 31.85462, "longitude": -102.3165 }, { "latitude": 31.85467, "longitude": -102.31644 }, { "latitude": 31.85489, "longitude": -102.3162 }, { "latitude": 31.85505, "longitude": -102.3164 }, { "latitude": 31.8552, "longitude": -102.3166 }, { "latitude": 31.85533, "longitude": -102.31677 }, { "latitude": 31.85506, "longitude": -102.31706 }, { "latitude": 31.85655, "longitude": -102.32234 }, { "latitude": 31.85851, "longitude": -102.32294 } ], "summary": { "arrivalTime": "2020-06-04T01:22:22-05:00", "departureTime": "2020-06-03T17:28:11-05:00", "lengthInMeters": 863989, "trafficDelayInSeconds": 528, "travelTimeInSeconds": 28451 } } ], "sections": [ { "endPointIndex": 5797, "sectionType": "TRAVEL_MODE", "startPointIndex": 0, "travelMode": "car" } ], "summary": { "arrivalTime": "2020-06-04T01:22:22-05:00", "departureTime": "2020-06-03T17:28:11-05:00", "lengthInMeters": 863989, "trafficDelayInSeconds": 528, "travelTimeInSeconds": 28451 } } ]

Hello Og101010, welcome to StackOverflow. Can you give more explanations and also give the code you have already written ? Take a tour on https://stackoverflow.com/tour . This is important to understand which type of question is acceptable ! — schlebe, Jun 09 '20 at 14:22
I assume that this is VARIANT column JSON that you wish to parse. Can you tell us which elements and in which format you'd like to query this? It appears there are several layers and some arrays, which could be read separately. If you want the entire thing flattened out, it'll create quite a lot of repetitive data. — Mike Walton, Jun 09 '20 at 15:04
Also, you'll likely want to get rid of those outer square brackets when you load the data. It'll make it easier to parse. — Mike Walton, Jun 09 '20 at 15:06

score 1 · Answer 1 · answered Jun 09 '20 at 21:28

I would consult the docs, especially https://docs.snowflake.com/en/sql-reference/functions/flatten.html and https://docs.snowflake.com/en/user-guide/semistructured-concepts.html

It will depend a bit on how you load the data, but here is an example query to get you started:

with tbl as (select parse_json($1)::variant list_json from values ('[ { "legs": [ { "points": [ { "latitude": 29.76524, "longitude": -95.35406 }, { "latitude": 30.74438, "longitude": -101.71203 }, { "latitude": 30.74421, "longitude": -101.71247 }, { "latitude": 30.74404, "longitude": -101.71289 }, { "latitude": 30.74366, "longitude": -101.71374 }, { "latitude": 30.74346, "longitude": -101.71415 }, { "latitude": 30.74303, "longitude": -101.71495 }, { "latitude": 30.74274, "longitude": -101.71543 }, { "latitude": 30.74234, "longitude": -101.71606 }, { "latitude": 31.82985, "longitude": -102.34753 }, { "latitude": 31.8302, "longitude": -102.34597 }, { "latitude": 31.83029, "longitude": -102.34557 }, { "latitude": 31.83038, "longitude": -102.34526 }, { "latitude": 31.83051, "longitude": -102.3448 }, { "latitude": 31.83081, "longitude": -102.344 }, { "latitude": 31.83099, "longitude": -102.34356 }, { "latitude": 31.83113, "longitude": -102.34328 }, { "latitude": 31.83145, "longitude": -102.34271 }, { "latitude": 31.83174, "longitude": -102.34226 }, { "latitude": 31.83207, "longitude": -102.34181 }, { "latitude": 31.83267, "longitude": -102.34109 }, { "latitude": 31.83317, "longitude": -102.34053 }, { "latitude": 31.83359, "longitude": -102.34007 }, { "latitude": 31.8339, "longitude": -102.33971 }, { "latitude": 31.83499, "longitude": -102.33852 }, { "latitude": 31.83547, "longitude": -102.338 }, { "latitude": 31.83553, "longitude": -102.33793 }, { "latitude": 31.83685, "longitude": -102.33648 }, { "latitude": 31.83764, "longitude": -102.3356 }, { "latitude": 31.83838, "longitude": -102.33479 }, { "latitude": 31.84575, "longitude": -102.32666 }, { "latitude": 31.84603, "longitude": -102.32636 }, { "latitude": 31.84679, "longitude": -102.32551 }, { "latitude": 31.84878, "longitude": -102.32333 }, { "latitude": 31.85095, "longitude": -102.32094 }, { "latitude": 31.85131, "longitude": -102.32054 }, { "latitude": 31.85134, "longitude": -102.32044 }, { "latitude": 31.85259, "longitude": -102.31886 }, { "latitude": 31.85273, "longitude": -102.31859 }, { "latitude": 31.85462, "longitude": -102.3165 }, { "latitude": 31.85467, "longitude": -102.31644 }, { "latitude": 31.85489, "longitude": -102.3162 }, { "latitude": 31.85505, "longitude": -102.3164 }, { "latitude": 31.8552, "longitude": -102.3166 }, { "latitude": 31.85533, "longitude": -102.31677 }, { "latitude": 31.85506, "longitude": -102.31706 }, { "latitude": 31.85655, "longitude": -102.32234 }, { "latitude": 31.85851, "longitude": -102.32294 } ], "summary": { "arrivalTime": "2020-06-04T01:22:22-05:00", "departureTime": "2020-06-03T17:28:11-05:00", "lengthInMeters": 863989, "trafficDelayInSeconds": 528, "travelTimeInSeconds": 28451 } } ], "sections": [ { "endPointIndex": 5797, "sectionType": "TRAVEL_MODE", "startPointIndex": 0, "travelMode": "car" } ], "summary": { "arrivalTime": "2020-06-04T01:22:22-05:00", "departureTime": "2020-06-03T17:28:11-05:00", "lengthInMeters": 863989, "trafficDelayInSeconds": 528, "travelTimeInSeconds": 28451 } } ]') t)
     ,flat_tbl as (select unnested.value json from tbl, lateral flatten(list_json) unnested)

select
    legs.value:summary.trafficDelayInSeconds trafficDelayInSeconds,
    legs.value:summary summary,
    points.value point
from flat_tbl,
    lateral flatten(json:legs) legs,
    lateral flatten(legs.value:points) points
;

It produces results like this, which is just a guess at what you might be looking to do in terms of parsing.

TRAFFICDELAYINSECONDS   SUMMARY POINT
528 {    "arrivalTime": "2020-06-04T01:22:22-05:00",    "departureTime": "2020-06-03T17:28:11-05:00",    "lengthInMeters": 863989,    "trafficDelayInSeconds": 528,    "travelTimeInSeconds": 28451  }  {    "latitude": 29.76524,    "longitude": -95.35406  }
528 {    "arrivalTime": "2020-06-04T01:22:22-05:00",    "departureTime": "2020-06-03T17:28:11-05:00",    "lengthInMeters": 863989,    "trafficDelayInSeconds": 528,    "travelTimeInSeconds": 28451  }  {    "latitude": 30.74438,    "longitude": -101.71203  }
528 {    "arrivalTime": "2020-06-04T01:22:22-05:00",    "departureTime": "2020-06-03T17:28:11-05:00",    "lengthInMeters": 863989,    "trafficDelayInSeconds": 528,    "travelTimeInSeconds": 28451  }  {    "latitude": 30.74421,    "longitude": -101.71247  }
528 {    "arrivalTime": "2020-06-04T01:22:22-05:00",    "departureTime": "2020-06-03T17:28:11-05:00",    "lengthInMeters": 863989,    "trafficDelayInSeconds": 528,    "travelTimeInSeconds": 28451  }  {    "latitude": 30.74404,    "longitude": -101.71289  }
528 {    "arrivalTime": "2020-06-04T01:22:22-05:00",    "departureTime": "2020-06-03T17:28:11-05:00",    "lengthInMeters": 863989,    "trafficDelayInSeconds": 528,    "travelTimeInSeconds": 28451  }  {    "latitude": 30.74366,    "longitude": -101.71374  }
528 {    "arrivalTime": "2020-06-04T01:22:22-05:00",    "departureTime": "2020-06-03T17:28:11-05:00",    "lengthInMeters": 863989,    "trafficDelayInSeconds": 528,    "travelTimeInSeconds": 28451  }  {    "latitude": 30.74346,    "longitude": -101.71415  }
528 {    "arrivalTime": "2020-06-04T01:22:22-05:00",    "departureTime": "2020-06-03T17:28:11-05:00",    "lengthInMeters": 863989,    "trafficDelayInSeconds": 528,    "travelTimeInSeconds": 28451  }  {    "latitude": 30.74303,    "longitude": -101.71495  }
528 {    "arrivalTime": "2020-06-04T01:22:22-05:00",    "departureTime": "2020-06-03T17:28:11-05:00",    "lengthInMeters": 863989,    "trafficDelayInSeconds": 528,    "travelTimeInSeconds": 28451  }  {    "latitude": 30.74274,    "longitude": -101.71543  }
528 {    "arrivalTime": "2020-06-04T01:22:22-05:00",    "departureTime": "2020-06-03T17:28:11-05:00",    "lengthInMeters": 863989,    "trafficDelayInSeconds": 528,    "travelTimeInSeconds": 28451  }  {    "latitude": 30.74234,    "longitude": -101.71606  }
528 {    "arrivalTime": "2020-06-04T01:22:22-05:00",    "departureTime": "2020-06-03T17:28:11-05:00",    "lengthInMeters": 863989,    "trafficDelayInSeconds": 528,    "travelTimeInSeconds": 28451  }  {    "latitude": 31.82985,    "longitude": -102.34753  }

Need help parsing this in snowflake

1 Answers1