Нужна помощь в разборе этого в снежинке

Question

Нужна помощь в разборе этого в снежинке

[ { "ноги": [ { "точки": [ { "широта": 29.76524, "долгота": -95.35406}, {"широта": 30.74438, "долгота": -101.71203}, {"широта": 30.74421, "долгота": -101.71247}, {"широта": 30.74404, "долгота": -101.71289}, {"широта": 30.74366, "долгота": -101.71374}, {"широта": 30.74346, "долгота": - 101.71415}, {"latitude": 30.74303, "longitude": -101.71495}, {"latitude": 30.74274, "longitude": -101.71543}, {"latitude": 30.74234, "longitude": -101.71606}, {" широта ": 31.82985," долгота ": -102.34753}, {" широта ": 31.8302," долгота ": -102.34597}, {" широта ": 31.83029,"longitude": -102.34557}, {"latitude": 31.83038, "longitude": -102.34526}, {"latitude": 31.83051, "longitude": -102.3448}, {"latitude": 31.83081, "longitude": - 102.344}, {"широта": 31.83099, "долгота": -102.34356}, {"широта": 31.83113, "долгота": -102.34328}, {"широта": 31.83145, "долгота": -102.34271}, {" широта ": 31.83174," долгота ": -102.34226}, {" широта ": 31.83207," долгота ": -102.34181}, {" широта ": 31.83267," долгота ": -102.34109}, {" широта ": 31.83317, "долгота": -102.34053}, {"широта": 31.83359, "долгота": -102.34007}, {"широта": 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", " leaveTime": "2020-06-03T17:28:11-05:00", "lengthInMeters": 863989, "trafficDelayInSeconds": 528, "travelTimeInSeconds": 28451 } } ], "section": [ { "endPointIndex": 5797, "sectionType": "TRAVEL_MODE", "startIndex": 0Point, "travelMode": "автомобиль" } ], "summary": { "Время прибытия": "2020-06-04T01: 22: 22-05: 00", "Время отъезда": "2020-06-03T17: 28: 11- 05:00 "," lengthInMeters": 863989," trafficDelayInSeconds ": 528," travelTimeInSeconds ": 28451}}]22-05: 00 "," отправление Time ":" 2020-06-03T17: 28: 11-05: 00 "," lengthInMeters": 863989," trafficDelayInSeconds ": 528," travelTimeInSeconds ": 28451}}]," разделы ": [ { "endPointIndex": 5797, "sectionType": "TRAVEL_MODE", "startPointIndex": 0," travelMode ":" car "}]," summary ": {" arrivalTime ":" 2020-06-04T01: 22: 22-05: 00 "," отправление Time ":" 2020-06-03T17: 28: 11-05: 00 "," lengthInMeters": 863989," trafficDelayInSeconds ": 528," travelTimeInSeconds ": 28451}}]22-05: 00 "," отправление Time ":" 2020-06-03T17: 28: 11-05: 00 "," lengthInMeters": 863989," trafficDelayInSeconds ": 528," travelTimeInSeconds ": 28451}}]," разделы ": [ { "endPointIndex": 5797, "sectionType": "TRAVEL_MODE", "startPointIndex": 0," travelMode ":" car "}]," summary ": {" arrivalTime ":" 2020-06-04T01: 22: 22-05: 00 "," отправление Time ":" 2020-06-03T17: 28: 11-05: 00 "," lengthInMeters": 863989," trafficDelayInSeconds ": 528," travelTimeInSeconds ": 28451}}]28451}}], "секции": [ { "endPointIndex": 5797, "sectionType": "TRAVEL_MODE", "startPointIndex": 0, "travelMode": "car" } ], "summary": { "arrivalTime": "2020-06-04T01: 22: 22-05: 00", "leaveTime": "2020-06-03T17: 28: 11-05: 00", "lengthInMeters": 863989, "trafficDelayInSeconds": 528, "travelTimeInSeconds" ": 28451}}]28451}}], "секции": [ { "endPointIndex": 5797, "sectionType": "TRAVEL_MODE", "startPointIndex": 0, "travelMode": "car" } ], "summary": { "arrivalTime": "2020-06-04T01: 22: 22-05: 00", "leaveTime": "2020-06-03T17: 28: 11-05: 00", "lengthInMeters": 863989, "trafficDelayInSeconds": 528, "travelTimeInSeconds" ": 28451}}]trafficDelayInSeconds ": 528," travelTimeInSeconds ": 28451}}]trafficDelayInSeconds ": 528," travelTimeInSeconds ": 28451}}]

0

sql json snowflake-cloud-data-platform snowflake-schema snowflake-task

Источник

user13714173 09 июн '20 в 17:12

1 ответ

Другие вопросы по тегам sql json snowflake-cloud-data-platform snowflake-schema snowflake-task

user2019978 10 июн '20 в 00:28 2020-06-10 00:28 · Answer 1 · 2020-06-10 00:28

Я бы проконсультировался с документами, особенно https://docs.snowflake.com/en/sql-reference/functions/flatten.html и https://docs.snowflake.com/en/user-guide/semistructured-concepts.html

Это будет немного зависеть от того, как вы загружаете данные, но вот пример запроса, который поможет вам начать:

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
;

Он дает такие результаты, которые являются лишь предположением о том, что вы, возможно, захотите сделать с точки зрения синтаксического анализа.

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  }