Question 104204 in Azure Question posted in
The official documentation to get you started can be found here.

what is the meaning of WITH (jsonContent varchar(MAX)) AS [result] in this code on azure synapse analytics

I am not able to undertsand why we are writing with (doc nvarchar(max)) as rows

JSON FILE

{"date_rep":"2020-07-24","day":24,"month":7,"year":2020,"cases":3,"deaths":0,"geo_id":"AF"}
{"date_rep":"2020-07-25","day":25,"month":7,"year":2020,"cases":7,"deaths":0,"geo_id":"AF"}
{"date_rep":"2020-07-26","day":26,"month":7,"year":2020,"cases":4,"deaths":0,"geo_id":"AF"}
{"date_rep":"2020-07-27","day":27,"month":7,"year":2020,"cases":8,"deaths":0,"geo_id":"AF"}

My Code to open this JSON file start from here.

select
    JSON_VALUE(doc, '$.date_rep') AS date_reported,
    JSON_VALUE(doc, '$.countries_and_territories') AS country,
    CAST(JSON_VALUE(doc, '$.deaths') AS INT) as fatal,
    JSON_VALUE(doc, '$.cases') as cases,
    doc
from openrowset(
        bulk 'https://demoaccname.dfs.core.windows.net/demoadlscontainer/afg.json',

        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
Tags:

2

Answers


  1. 0

    nvarchar(max) in SQL can be used to store any string types. when we are loading the JSON from source to SQL table, we don’t know exactly the length of the JSON and JSON is a string type.

    In SQL there is no specific datatype for JSON so we use WITH (jsonContent nvarchar(MAX)) AS [result] to store JSON from file to nvarchar column.

    JSON documents can be stored as-is in NVARCHAR columns. This is the best way for quick data load and ingestion because the loading speed is matching loading of string columns.

    Login or Signup to reply.
  2. 0

    jsonContent nvarchar(max) creates a external table with the column name jsonContent .

    in 
FROM
    OPENROWSET(
        BULK 'https://XXXXX',
        FORMAT = 'CSV',
        FIELDQUOTE = '0x0b',
        FIELDTERMINATOR ='0x0b',
        ROWTERMINATOR = '0x0b'
    )  with (**jsonContent nvarchar(max)**) as rows

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search