Azure Data Lake中U-SQL查询报错及JSON路径遍历咨询

Hey there! Let's break down your U-SQL JSON issues step by step—first fixing that extraction error in @jsonnodes, then covering how to traverse all JSON objects properly.

Fixing the Extraction Error in @jsonnodes #

Most extraction errors in U-SQL's JSON handling boil down to three common issues. Let's walk through each with examples:

1. Mismatched JSON Path vs. Actual Structure #

If your JSON root is an array (super common!) but you're using an object path, you'll hit errors immediately. For example, if your raw JSON looks like this:

[
  {"orderId": 101, "customer": {"name": "Alice"}},
  {"orderId": 102, "customer": {"name": "Bob"}}
]

A wrong query would try to extract directly from the root:

@jsonnodes =
    SELECT JsonFunctions.GetValue(jsonContent, "$.orderId") AS orderId
    FROM @rawData;

Fix: Use $[*] to iterate over array elements first, then extract properties from each item:

@jsonnodes =
    SELECT JsonFunctions.GetValue(orderItem, "$.orderId") AS orderId,
           JsonFunctions.GetValue(orderItem, "$.customer.name") AS customerName
    FROM @rawData
    CROSS APPLY JsonFunctions.JsonTuple(jsonContent, "$[*]") AS j(orderItem);

2. Invalid Path Syntax for Special Characters #

If your JSON has property names with hyphens, spaces, or special characters, you need to wrap them in double quotes in the path. For example:

{"user-id": 123, "full name": "Charlie Brown"}

Wrong path: $.user-id (throws syntax error)
Correct path: $."user-id" or $."full name"

3. Unhandled NULL/Missing Values #

If some JSON objects lack the property you're extracting, you'll get a runtime error. Use ISNULL or TRY_CAST to handle this gracefully:

@jsonnodes =
    SELECT ISNULL(JsonFunctions.GetValue(orderItem, "$.discount"), "0") AS discount
    FROM @rawData
    CROSS APPLY JsonFunctions.JsonTuple(jsonContent, "$[*]") AS j(orderItem);

Traversing All JSON Objects in U-SQL #

To traverse all objects across every level of your JSON, use the recursive descent JSONPath $..*. This path matches every element in the JSON hierarchy—then you can filter to only keep objects with JsonFunctions.GetType().

Here's a full example:

// Assume @rawData has a column 'jsonContent' with your JSON data
@allObjects =
    SELECT jsonNode AS objectContent
    FROM @rawData
    CROSS APPLY JsonFunctions.JsonTuple(jsonContent, "$..*") AS j(jsonNode)
    WHERE JsonFunctions.GetType(jsonNode) == "object";

If you only want to traverse objects at the immediate root level, use $.* instead:

@rootLevelObjects =
    SELECT JsonFunctions.GetValue(jsonContent, "$.*") AS rootObject
    FROM @rawData;

Quick Debug Tip #

If you're still stuck, add a step to print the raw JSON and node types to diagnose mismatches:

@debug =
    SELECT jsonContent,
           JsonFunctions.GetType(jsonNode) AS nodeType,
           jsonNode
    FROM @rawData
    CROSS APPLY JsonFunctions.JsonTuple(jsonContent, "$..*") AS j(jsonNode);
OUTPUT @debug TO "/debug/json_debug.csv" USING Outputters.Csv();

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