CODEWITHSUNDEEP
azureazure-data-lakeu-sql
MAK
MAK

Reputation: 1288

Syntax error on U-SQL Script to get data from JSON complex type

This is my input JSON file.

[
  {
    "Tag": "STACK007",
    "data": [
      {
        "item": "UNIFY109",
        "timestamp": "2018-08-27T17:28:51.8490000Z",
        "jsonVersion": 1,
        "messageType": 1,
        "velocity": 709
      }
    ],
    "EventProcessedUtcTime": "2018-08-27T17:36:17.5519639Z",
    "EventEnqueuedUtcTime": "2018-08-27T17:28:52.0010000Z"
  }
]

I'm trying to convert this input JSON file to CSV. Here's my U-SQL Script

REFERENCE ASSEMBLY [Newtonsoft.Json];
REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];

USING Microsoft.Analytics.Samples.Formats.Json;

DECLARE @input string = @"/demodata/logs/2018/08/input.json";

@json = 
EXTRACT 
        Tag string, 
        EventProcessedUtcTime DateTime,
        EventEnqueuedUtcTime DateTime,
        JsonFunctions.JsonTuple(data) AS data
  FROM @input 
USING new JsonExtractor();

@result =
SELECT Tag,          
       address["velocity"]AS Velocity,
       address["messageType"]AS MessageType,
       address["timestamp"]AS Timestamp
FROM @json;

OUTPUT @result
TO "/output/demooutput.csv"
USING Outputters.Csv();

This script is giving me a syntax error with the message "syntax error. Expected one of: '.' "

How do I fix this?

Upvotes: 1

Views: 77

Answers (1)

MAK
MAK

Reputation: 1288

I found that this had been answered previously:

@resultset = 
    EXTRACT 

        item string, 
        jsonversion int, 
        messageType int, 
        velocity float
    FROM @input
   USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor("data[*]");

This is from an answer by Michael Rys to this stackoverflow question: U- SQL Unable to extract data from JSON file

"Actually the JSONExtractor supports the rowpath parameter expressed in JSONPath that gives you the ability to identify the JSON object or JSON array items that you want to map into rows. So you can extract your data with a single statement from your JSON document"

Upvotes: 1

Related Questions