Extract Json into table

sourav dutta 211 Reputation points
2021-06-21T09:17:13.793+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,664 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,941 Reputation points
    2021-06-21T18:32:50.213+00:00

    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     |  
    +----+-----------------------------+-------------+  
    
    0 comments No comments