Extract Json into table

Question

I have json where state wise district are exists.

I want the state wise data into row wise107519-my-data.txt

Please help.

Thanks in advance

Answer 1

Hi @sourav dutta ,

You would need SQL Server 2016 or later.

I saved your JSON file as 'e:\Temp\souravdutta-2069.json'.

Here is how to convert it into a rectangular/relational format and load into a DB table.

SQL

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, District VARCHAR(100), City VARCHAR(100));  
  
INSERT INTO @tbl (District, City)  
SELECT t.[key] AS District, city.value AS City  
FROM OPENROWSET (BULK N'e:\Temp\souravdutta-2069.json'  
   , SINGLE_CLOB) as j  
   CROSS APPLY OPENJSON(BulkColumn) AS t  
   CROSS APPLY OPENJSON(t.value) AS city;  
  
-- test  
SELECT * FROM @tbl;  

Partial Output

+----+-----------------------------+-------------+  
| ID |          District           |    City     |  
+----+-----------------------------+-------------+  
|  1 | Andaman and Nicobar Islands | Port Blair* |  
|  2 | Andhra Pradesh              | Adoni       |  
|  3 | Andhra Pradesh              | Amalapuram  |  
|  4 | Andhra Pradesh              | Anakapalle  |  
|  5 | Andhra Pradesh              | Anantapur   |  
|  6 | Andhra Pradesh              | Bapatla     |  
+----+-----------------------------+-------------+