SQL example for 'querying JSON' seems incorrect in the learn module

Question

SQL example for 'querying JSON' seems incorrect in the learn module

Karthik Narayanan 20

The SQL example given under the heading "Querying JSON files" seems to be incorrect on the following module.

https://learn.microsoft.com/en-us/training/modules/query-data-lake-using-azure-synapse-serverless-sql-pools/3-query-files

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

It seems to be written for CSV files. Not JSON.

Smaran Thoomu 35,375 Reputation points Microsoft External Staff Moderator

2024-01-03T07:17:24.63+00:00

@Karthikaeya Narayanan P Just checking in to see if the below answer provided by Bruno Gomes helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Karthik Narayanan 20 Reputation points

2024-01-03T16:36:26.4066667+00:00

Is there a way to inform the learn portal to fix it as well?

That will be useful for many others like me who are learning from it.
Smaran Thoomu 35,375 Reputation points Microsoft External Staff Moderator

2024-01-03T17:15:53.1666667+00:00

Hi @Karthikaeya Narayanan P
At the end of each module, you'll find a feedback section as below.

By using this feature, you can convey your suggestions directly to our product teams. This input is valuable as it helps prioritize your request in relation to our current backlog of features and provides insight into the impact that implementing your proposed feature could have.

Answer accepted by question author

0 additional answers

Your answer

Smaran Thoomu 35,375 Reputation points Microsoft External Staff Moderator

2024-01-03T07:17:24.63+00:00

@Karthikaeya Narayanan P Just checking in to see if the below answer provided by Bruno Gomes helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Karthik Narayanan 20 Reputation points

2024-01-03T16:36:26.4066667+00:00

Is there a way to inform the learn portal to fix it as well?

That will be useful for many others like me who are learning from it.
Smaran Thoomu 35,375 Reputation points Microsoft External Staff Moderator

2024-01-03T17:15:53.1666667+00:00

Hi @Karthikaeya Narayanan P
At the end of each module, you'll find a feedback section as below.

By using this feature, you can convey your suggestions directly to our product teams. This input is valuable as it helps prioritize your request in relation to our current backlog of features and provides insight into the impact that implementing your proposed feature could have.

Answer 1

Hi Karthikaeya,

I agree with you that the SQL example given under the heading "Querying JSON files" in the learn module is incorrect. The example is using the OPENROWSET() function with the FORMAT = 'csv' option, which is only valid for CSV files.

To query JSON files, you should use the FORMAT = 'json' option. The following is a corrected version of the example:

SQL


SELECT doc

FROM

  OPENROWSET(

    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',

    FORMAT = 'json'

  ) WITH (doc NVARCHAR(MAX)) as rows

This example will correctly load the JSON data from the specified Azure Blob Storage container and return the doc column, which contains the entire JSON document as a single string.

Here is a more detailed explanation of the changes I made:

I changed the FORMAT option from 'csv' to 'json'. This tells the OPENROWSET() function to expect JSON data.
I removed the FIELDTERMINATOR and FIELDQUOTE options. These options are only used for CSV data.
I added the ROWTERMINATOR option with the value '0x0b'. This tells the OPENROWSET() function to use the carriage return (\r) character as a row terminator.

I hope this helps! Let me know if you have any other questions.

Sincerely,

Bruno Gomes.

Share via

SQL example for 'querying JSON' seems incorrect in the learn module

0 additional answers

Your answer