Openrowset

Question

Hi How to open the blow code using openrowset using synapse {"ONE":"JJJJ",

"TWO":"HH",

"TGHREE":"JJKK",

"4":"HJJJJ",

"5":"UUUU",

"_6":"UUUU",

"_7":"dbJJJJ",

"_8":""4GGG"",

"_9":"EE/",

"_ts":WW}

Answer 1

@Vineet S - Thanks for the question and using MS Q&A platform.

Before jumping into the solution make sure to validate the JSON file which you are referring to?

I had used online JSON Lint: JSON Online Validator and Formatter

Here is the modified valid JSON:

JSONCopy

{   
    "ONE": "JJJJ",
    "TWO": "HH",
    "THREE": "JJKK",
    "4": "HJJJJ",
    "5": "UUUU",
    "_6": "UUUU",
    "_7": "dbJJJJ",
    "_8": "4GGG",
    "_9": "EE/",
    "_ts": "WW"
}

I had used online JSON Lint: JSON Online Validator and Formatter

 

To open the provided JSON code using OPENROWSET in Synapse SQL, you need to first save the JSON code as a file in an Azure Storage account. Then, you can use the OPENROWSET function to read the contents of the file and return the content as a set of rows. Here is an example query that reads the contents of a JSON file using OPENROWSET: 

Step1: Create Synapse Analytics workspace and open Synapse studio. 

Step2: Go to Data tab, click on linked and select the storage account which you created while creating synapse workspace. Select the storage account container and upload your Json file.

Step3:  Right click on Json file uploaded, select new SQL script and select top 100 rows. 

Step4: Once the SQL script is open, click on Run. You will see the output of the Json file displayed. 

For more details, refer to Query JSON files using serverless SQL pool in Azure Synapse Analytics 

Hope this helps. Do let us know if you have any further queries.  

------------  

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.