JSON support in SQL Server (BULK INSERT INTO SQL SERVER) – Part 4

There are multiple ways to store JSON document into SQL Server. Such as writing the C# application to read JSON document and insert it into SQL Server, using SSIS script component with .net libraries, using PowerShell or T-SQL scripts.

In this topic, I will try to cover all. Let’s start with T-SQL

I have a JSON  file with the document structure as below.

[{
    "_id": {
        "$oid": "52b213b38594d8a2be17c780"
    },
    "approvalfy": 1999,
    "board_approval_month": "November",
    "boardapprovaldate": "2013-11-12T00:00:00Z",
    "borrower": "FEDERAL DEMOCRATIC REPUBLIC OF ETHIOPIA",
    "closingdate": "2018-07-07T00:00:00Z",
    "country_namecode": "Federal Democratic Republic of Ethiopia!$!ET",
    "countrycode": "ET",
    "countryname": "Federal Democratic Republic of Ethiopia",
    "countryshortname": "Ethiopia",
    "docty": "Project Information Document,Indigenous Peoples Plan,Project Information Document",
    "envassesmentcategorycode": "C",
    "grantamt": 0,
    "ibrdcommamt": 0,
    "id": "P129828",
    "idacommamt": 130000000,
    "impagency": "MINISTRY OF EDUCATION",
    "lendinginstr": "Investment Project Financing",
    "lendinginstrtype": "IN",
    "lendprojectcost": 550000000,
    "majorsector_percent": [
        {
            "Name": "Education",
            "Percent": 46
        },
        {
            "Name": "Education",
            "Percent": 26
        },
        {
            "Name": "Public Administration, Law, and Justice",
            "Percent": 16
        },
        {
            "Name": "Education",
            "Percent": 12
        }
    ],
    "mjsector_namecode": [
        {
            "name": "Education",
            "code": "EX"
        },
        {
            "name": "Education",
            "code": "EX"
        },
        {
            "name": "Public Administration, Law, and Justice",
            "code": "BX"
        },
        {
            "name": "Education",
            "code": "EX"
        }
    ],
    "mjtheme": [
        "Human development"
    ],
    "mjtheme_namecode": [
        {
            "name": "Human development",
            "code": "8"
        },
        {
            "name": "",
            "code": "11"
        }
    ],
    "mjthemecode": "8,11",
    "prodline": "PE",
    "prodlinetext": "IBRD/IDA",
    "productlinetype": "L",
    "project_abstract": {
        "cdata": "The development objective of the Second Phase of General Education Quality Improvement Project for Ethiopia is to improve learning conditions in primary and secondary schools and strengthen institutions at different levels of educational administration. The project has six components. The first component is curriculum, textbooks, assessment, examinations, and inspection. This component will support improvement of learning conditions in grades KG-12 by providing increased access to teaching and learning materials and through improvements to the curriculum by assessing the strengths and weaknesses of the current curriculum. This component has following four sub-components: (i) curriculum reform and implementation; (ii) teaching and learning materials; (iii) assessment and examinations; and (iv) inspection. The second component is teacher development program (TDP). This component will support improvements in learning conditions in both primary and secondary schools by advancing the quality of teaching in general education through: (a) enhancing the training of pre-service teachers in teacher education institutions; and (b) improving the quality of in-service teacher training. This component has following three sub-components: (i) pre-service teacher training; (ii) in-service teacher training; and (iii) licensing and relicensing of teachers and school leaders. The third component is school improvement plan. This component will support the strengthening of school planning in order to improve learning outcomes, and to partly fund the school improvement plans through school grants. It has following two sub-components: (i) school improvement plan; and (ii) school grants. The fourth component is management and capacity building, including education management information systems (EMIS). This component will support management and capacity building aspect of the project. This component has following three sub-components: (i) capacity building for education planning and management; (ii) capacity building for school planning and management; and (iii) EMIS. The fifth component is improving the quality of learning and teaching in secondary schools and universities through the use of information and communications technology (ICT). It has following five sub-components: (i) national policy and institution for ICT in general education; (ii) national ICT infrastructure improvement plan for general education; (iii) develop an integrated monitoring, evaluation, and learning system specifically for the ICT component; (iv) teacher professional development in the use of ICT; and (v) provision of limited number of e-Braille display readers with the possibility to scale up to all secondary education schools based on the successful implementation and usage of the readers. The sixth component is program coordination, monitoring and evaluation, and communication. It will support institutional strengthening by developing capacities in all aspects of program coordination, monitoring and evaluation; a new sub-component on communications will support information sharing for better management and accountability. It has following three sub-components: (i) program coordination; (ii) monitoring and evaluation (M and E); and (iii) communication."
    },
    "project_name": "Ethiopia General Education Quality Improvement Project II",
    "projectdocs": [
        {
            "DocTypeDesc": "Project Information Document (PID),  Vol.",
            "DocType": "PID",
            "EntityID": "090224b081e545fb_1_0",
            "DocURL": "http://www-wds.worldbank.org/servlet/WDSServlet?pcont=details&eid=090224b081e545fb_1_0",
            "DocDate": "28-AUG-2013"
        },
        {
            "DocTypeDesc": "Indigenous Peoples Plan (IP),  Vol.1 of 1",
            "DocType": "IP",
            "EntityID": "000442464_20130920111729",
            "DocURL": "http://www-wds.worldbank.org/servlet/WDSServlet?pcont=details&eid=000442464_20130920111729",
            "DocDate": "01-JUL-2013"
        },
        {
            "DocTypeDesc": "Project Information Document (PID),  Vol.",
            "DocType": "PID",
            "EntityID": "090224b0817b19e2_1_0",
            "DocURL": "http://www-wds.worldbank.org/servlet/WDSServlet?pcont=details&eid=090224b0817b19e2_1_0",
            "DocDate": "22-NOV-2012"
        }
    ],
    "projectfinancialtype": "IDA",
    "projectstatusdisplay": "Active",
    "regionname": "Africa",
    "sector": [
        {
            "Name": "Primary education"
        },
        {
            "Name": "Secondary education"
        },
        {
            "Name": "Public administration- Other social services"
        },
        {
            "Name": "Tertiary education"
        }
    ],
    "sector1": {
        "Name": "Primary education",
        "Percent": 46
    },
    "sector2": {
        "Name": "Secondary education",
        "Percent": 26
    },
    "sector3": {
        "Name": "Public administration- Other social services",
        "Percent": 16
    },
    "sector4": {
        "Name": "Tertiary education",
        "Percent": 12
    },
    "sector_namecode": [
        {
            "name": "Primary education",
            "code": "EP"
        },
        {
            "name": "Secondary education",
            "code": "ES"
        },
        {
            "name": "Public administration- Other social services",
            "code": "BS"
        },
        {
            "name": "Tertiary education",
            "code": "ET"
        }
    ],
    "sectorcode": "ET,BS,ES,EP",
    "source": "IBRD",
    "status": "Active",
    "supplementprojectflg": "N",
    "theme1": {
        "Name": "Education for all",
        "Percent": 100
    },
    "theme_namecode": [
        {
            "name": "Education for all",
            "code": "65"
        }
    ],
    "themecode": "65",
    "totalamt": 130000000,
    "totalcommamt": 130000000,
    "url": "http://www.worldbank.org/projects/P129828/ethiopia-general-education-quality-improvement-project-ii?lang=en"
}]

Import the JSON  file document  into SQL Server using OPENROWSET

CREATE TABLE org_cl_json_files
(
ID int IDENTITY,
CL_json_document nvarchar(max)
)

INSERT INTO org_cl_json_files (CL_json_document) 
SELECT * 
FROM OPENROWSET (BULK 'G:\Dummy_Data\world_bank\world_bank.json', SINGLE_CLOB) as j

SELECT CL_json_document  as BulkColumn FROM org_cl_json_files 

The JSON file documents are saved into SQL Server Table.

Reading each JSON documents  from JSON file and inserting into SQL Server  (As the JSON file is too huge, I am limiting my search only for top 10 documents)

INSERT INTO org_cl_json_files (CL_json_document) 
SELECT ff.value
FROM OPENROWSET (BULK 'G:\Dummy_Data\world_bank\world_bank.json', SINGLE_CLOB) as j 
CROSS APPLY OPENJSON (j.BulkColumn) ff

inserting data into SQL Server in the Tabular format with selected columns from JSON document

CREATE TABLE [dbo].[org_cl_json_files_tb](
  [_id] [nvarchar](20) NULL,
  [_approvalfy] [nvarchar](4) NULL,
  [_board_approval_month] [varchar](20) NULL,
  [_boardapprovaldate] [datetime] NULL,
  [_borrower] [varchar](500) NULL,
  [_closingdate] [datetime] NULL,
  [_country_namecode] [varchar](100) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[org_cl_json_files_tb]
SELECT  json_data.*  
  FROM OPENROWSET (BULK 'G:\Dummy_Data\world_bank\world_bank.json', SINGLE_CLOB) as j 
CROSS APPLY OPENJSON (j.BulkColumn)
 with 
  (
    _id				nvarchar(20)	'$._id."$oid"',
    _approvalfy			nvarchar(4)	'$.approvalfy',
    _board_approval_month	varchar(20)	'$.board_approval_month',
    _boardapprovaldate		datetime	'$.boardapprovaldate',
    _borrower			varchar(500)	'$.borrower',
    _closingdate		datetime	'$.closingdate',
    _country_namecode		varchar(100)	'$.country_namecode') as json_data

Leave a Reply

Your email address will not be published. Required fields are marked *