请求协助在Snowflake中解析路径规划JSON数据

Got it, let's walk through how to parse this detailed travel JSON (with route points, leg summaries, and trip-level stats) in Snowflake. I'll break it down into actionable steps, starting with setting up test data and then extracting each layer of information.

Step 1: Create a Test Table with Your JSON Data #

First, let's load your JSON into a Snowflake table using a VARIANT column (Snowflake's go-to for semi-structured data):

CREATE OR REPLACE TABLE TRAVEL_DATA (
    RAW_JSON VARIANT
);

INSERT INTO TRAVEL_DATA (RAW_JSON)
SELECT PARSE_JSON('[ { "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 } } ]');

Step 2: Extract Trip-Level Summary Stats #

If you just need the top-level trip summary, you can directly reference the JSON properties with Snowflake's dot notation and cast them to proper data types:

SELECT
    RAW_JSON:summary:arrivalTime::TIMESTAMP AS TRIP_ARRIVAL_TIME,
    RAW_JSON:summary:departureTime::TIMESTAMP AS TRIP_DEPARTURE_TIME,
    RAW_JSON:summary:lengthInMeters::NUMBER AS TRIP_TOTAL_LENGTH_METERS,
    RAW_JSON:summary:trafficDelayInSeconds::NUMBER AS TRIP_TRAFFIC_DELAY_SECONDS,
    RAW_JSON:summary:travelTimeInSeconds::NUMBER AS TRIP_TOTAL_TRAVEL_SECONDS
FROM TRAVEL_DATA;

Step 3: Unpack Leg Details + Route Points #

To get into the nested legs array and the individual GPS points inside each leg, use LATERAL FLATTEN to expand the arrays. This will give you a row for each GPS point, tied to its parent leg and trip:

SELECT
    -- Trip-level metadata
    t.RAW_JSON:summary:arrivalTime::TIMESTAMP AS TRIP_ARRIVAL_TIME,
    t.RAW_JSON:summary:departureTime::TIMESTAMP AS TRIP_DEPARTURE_TIME,
    
    -- Leg-level summary
    l.value:summary:arrivalTime::TIMESTAMP AS LEG_ARRIVAL_TIME,
    l.value:summary:lengthInMeters::NUMBER AS LEG_LENGTH_METERS,
    l.value:summary:travelTimeInSeconds::NUMBER AS LEG_TRAVEL_SECONDS,
    
    -- Individual GPS point data
    p.value:latitude::FLOAT AS POINT_LATITUDE,
    p.value:longitude::FLOAT AS POINT_LONGITUDE,
    -- Add a sequence number to keep track of the point order
    ROW_NUMBER() OVER (PARTITION BY t.RAW_JSON, l.index ORDER BY p.index) AS POINT_SEQUENCE
FROM TRAVEL_DATA t,
     LATERAL FLATTEN(input => t.RAW_JSON:legs) l,
     LATERAL FLATTEN(input => l.value:points) p;

Key Notes: #

• LATERAL FLATTEN is used to "unroll" JSON arrays into rows. The first flatten handles the legs array, the second handles the points array inside each leg.
• Using ::CAST_TYPE converts the JSON values into Snowflake native types (TIMESTAMP, NUMBER, FLOAT) for easier analysis.
• The ROW_NUMBER() window function preserves the order of GPS points as they appear in the JSON array, which is crucial for mapping the route.

Bonus: Extract Section Information #

If you also need the sections data (like travel mode), you can add another flatten step for that:

SELECT
    t.RAW_JSON:summary:arrivalTime::TIMESTAMP AS TRIP_ARRIVAL_TIME,
    s.value:sectionType::STRING AS SECTION_TYPE,
    s.value:travelMode::STRING AS TRAVEL_MODE,
    s.value:startPointIndex::NUMBER AS SECTION_START_POINT,
    s.value:endPointIndex::NUMBER AS SECTION_END_POINT
FROM TRAVEL_DATA t,
     LATERAL FLATTEN(input => t.RAW_JSON:sections) s;

内容的提问来源于stack exchange，提问作者Og101010