April 21, 2022 at 11:35 am
Hi there
I have a JSON file , which through a SQL Script will parse the JSON file and extract the different parts
into tables
In the attached example, I have 4 main tables
1) Returns (Main Table)
2) ReturnItems (Child table linked to Returns)
3) ReturnItemsCalibration_Channels (Child table linked to ReturnItems)
4) ReturnItemsCalibration_Points (Child table linked to ReturnItemsCalibration_Channels)
Now what im stuck on is being able to extract is the value for Points in the following section
"calbration_channels": [
{
"channel": "A",
"name": "CO2",
"unit": "ppm",
"unit2": "ppmz",
"points": [
20,
80
]
}
],
Points is a comma seperated string and can be 1 or many
ive built the table ReturnItemsCalibration_Points, to take in mutiple entries
So ideally what I would have is
Returns table
d3r_id Reference PurchaseOrder Customer_Reference Type Notes Status Eori Sent_To_Salesforce received_date approved_date Calibration_Certification_Company_Name
4925 RET01042202 po1 Service Calibration NULL FALSE NULL NULL NULL NULL
ReturnItems Table
ReturnItemsID d3r_id Name Part_Number Serial_Number Calibration_Type Calibration_Notes Fault Return_Reason education_number calbration_channels
1 4925 TGE-0010 TGE-0010 10 Standard Example calibration notes NULL NULL NULL
2 4925 TGP-4703 TGP-4703 890716 Custom NULL NULL NULL
ReturnItemsCalibration_Channels table
ReturnItemsCalibration_ChannelsID ReturnItemsID RowNo Channel Channel_Name UNIT MIN MAX std_point_1 std_point_2 std_point_3
1 1 1 A CO2 ppm NULL NULL NULL NULL NULL
2 2 1 B Voltage V DC 0 2.5 0.5 2 NULL
ReturnItemsCalibration_Points table
ReturnItemsCalibration_PointsID ReturnItemsCalibration_ChannelsID Point
1 1 20
2 1 80
3 2 20
4 2 80
5 2 100
6 2 150
So for each entry in Points, I will create a new record in ReturnItemsCalibration_Points table with Foreign Key of ReturnItemsCalibration_ChannelsID
ive tried using String_split on the Points object but it doesnt work
/----------------------------------------------------------------
JSON FILE
--------------------------------------------------------------------/
{
"return": {
"d3r_id": "4925",
"reference": "RET01042202",
"po": "po1",
"customer_reference": "",
"calibration_certification_company_name": "Example company name for calibration certificate",
"type": "Service Calibration",
"notes": "",
"status": "complete",
"eori": false,
"sent_to_salesforce": null,
"received_date": "2022-04-01 11:16:03",
"approved_date": "2022-04-01 11:16:30",
"items": [
{
"name": "TGE-0010",
"part_number": "TGE-0010",
"serial_number": "10",
"calibration_type": "Standard",
"calbration_channels": [
{
"channel": "A",
"name": "CO2",
"unit": "ppmz",
"points": [
20,
80
]
}
],
"calibration_notes": "Example calibration notes ",
"return_reason": "",
"education_number": null
},
{
"name": "TGP-4703",
"part_number": "TGP-4703",
"serial_number": "890716",
"calibration_type": "Custom",
"calbration_channels": [
{
"channel": "B",
"name": "Voltage",
"unit": "V DC",
"points": [
20,
80,
100,
150
]
}
],
"calibration_notes": "",
"return_reason": "",
"education_number": null
}
]
},
"customer": {
"firstname": "Jahidul",
"lastname": "Islam",
"email": "jahidul@d3r.com",
"phone": "07940970758",
"company": ""
},
"address": {
"return": {
"line_1": "44 Hampshire Avenue",
"line_2": "",
"town": "Bognor Regis",
"state": "",
"county": "",
"postcode": "PO21 5JJ",
"country": "GB"
},
"delivery": {
"firstname": "Jahidul",
"lastname": "Islam",
"company": "",
"line_1": "44 Hampshire Avenue",
"line_2": "",
"town": "Bognor Regis",
"state": "",
"county": "",
"postcode": "PO21 5JJ",
"country": "GB",
"phone": "0794097058"
}
}
}
/----------------------------------------------------------------
DDL SCRIPT
--------------------------------------------------------------------/
DROP TABLE IF EXISTS [dbo].[Returns]
CREATE TABLE [dbo].[Returns](
[d3r_id] [int] NOT NULL,
[Reference] [nvarchar](30) NULL,
[PurchaseOrder] [nvarchar](30) NULL,
[Customer_Reference] [nvarchar](100) NULL,
[Type] [nvarchar](30) NULL,
[Notes] [nvarchar](100) NULL,
[Status] [nvarchar](30) NULL,
[Eori] [nvarchar](30) NULL,
[Sent_To_Salesforce] [datetime] NULL,
[received_date] [datetime] NULL,
[approved_date] [datetime] NULL,
[Calibration_Certification_Company_Name] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED
(
[d3r_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
DROP TABLE IF EXISTS [dbo].[ReturnItems]
GO
/****** Object: Table [dbo].[ReturnItems] Script Date: 21/04/2022 12:29:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ReturnItems](
[ReturnItemsID] [int] IDENTITY(1,1) NOT NULL,
[d3r_id] [int] NULL,
[Name] [nvarchar](30) NULL,
[Part_Number] [nvarchar](30) NULL,
[Serial_Number] [nvarchar](30) NULL,
[Calibration_Type] [nvarchar](30) NULL,
[Calibration_Notes] [nvarchar](30) NULL,
[Fault] [nvarchar](30) NULL,
[Return_Reason] [nvarchar](30) NULL,
[education_number] [nvarchar](30) NULL,
[calbration_channels] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED
(
[ReturnItemsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
DROP TABLE IF EXISTS [dbo].[ReturnItemsCalibration_Channels]
GO
/****** Object: Table [dbo].[ReturnItemsCalibration_Channels] Script Date: 21/04/2022 12:30:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ReturnItemsCalibration_Channels](
[ReturnItemsCalibration_ChannelsID] [int] IDENTITY(1,1) NOT NULL,
[ReturnItemsID] [int] NULL,
[RowNo] [bigint] NULL,
[Channel] [char](1) NULL,
[Channel_Name] [varchar](50) NULL,
[UNIT] [varchar](10) NULL,
[MIN] [varchar](20) NULL,
[MAX] [varchar](20) NULL,
[std_point_1] [varchar](20) NULL,
[std_point_2] [varchar](20) NULL,
[std_point_3] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[ReturnItemsCalibration_ChannelsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
DROP TABLE if exists [dbo].[ReturnItemsCalibration_Points]
GO
/****** Object: Table [dbo].[ReturnItemsCalibration_Points] Script Date: 21/04/2022 12:31:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ReturnItemsCalibration_Points](
[ReturnItemsCalibration_PointsID] [int] IDENTITY(1,1) NOT NULL,
[ReturnItemsCalibration_ChannelsID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ReturnItemsCalibration_PointsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/----------------------------------------------------------------
SCRIPT USED TO EXTRACT JSON
--------------------------------------------------------------------/
/******************************************************************************************************************************************/
/* LOAD DATA - RETURN */
/****************************************************************************************************************************************/
--1) Return
Declare @json varchar(max)
SELECT @json = BulkColumn
FROM OPENROWSET (BULK 'C:\ReturnsDatabase\Data\D3R_Revised_20220421.json', SINGLE_CLOB) as j
--FROM OPENROWSET (BULK 'C:\ChrisDev\Customer Services Database (C)\RET28092107.JSON', SINGLE_CLOB) as j
INSERT INTO [dbo].[Returns]([d3r_id],[Reference],[PurchaseOrder],[Customer_Reference],[Type],[Notes],[Eori],[Sent_To_Salesforce])
SELECT
-- RETURN
[returns].id
,[returns].Reference
,[returns].po
,[returns].customer_reference
,[returns].[type]
,[returns].[notes]
,[returns].[eori]
,[returns].[sent_to_salesforce]
FROM
-- RETURNS
OPENJSON(@json)
WITH
(
-- Return
id INT '$.return.d3r_id',
reference NVARCHAR(30) '$.return.reference',
po NVARCHAR(30) '$.return.po',
customer_reference NVARCHAR(30) '$.return.customer_reference',
[type] NVARCHAR(30) '$.return.type',
[notes] NVARCHAR(30) '$.return.notes',
[eori] NVARCHAR(30) '$.return.eori',
[sent_to_salesforce] NVARCHAR(30) '$.return.sent_to_salesforce'
) [returns]
;
/******************************************************************************************************************************************/
/* LOAD DATA - RETURNS ITEMS */
/****************************************************************************************************************************************/
INSERT INTO ReturnItems([d3r_id],[Name],[Part_Number],[Serial_Number],[Calibration_Type],[Calibration_Notes],[Fault],[Return_Reason])
SELECT
-- RETURN
[returns].id
-- ITEMS
,cal_channels.Name
,cal_channels.part_number
,cal_channels.serial_number
,cal_channels.calibration_type
,cal_channels.calibration_notes
,cal_channels.fault
,cal_channels.return_reason
FROM
-- RETURNS
OPENJSON(@json)
WITH
(
-- Return
id INT '$.return.d3r_id',
items NVARCHAR(MAX) '$.return.items' AS JSON -- Use this to extract detail out of items
) [returns]
CROSS APPLY
-- ITEMS
OPENJSON(returns.items)
WITH
(
[name] nvarchar(30) ,
part_number nvarchar(30) ,
serial_number nvarchar(30) ,
calibration_type nvarchar(30) ,
calibration_notes nvarchar(30) ,
fault nvarchar(30) ,
return_reason nvarchar(30) ,
calbration_channels NVARCHAR(MAX) AS JSON -- Use this to extract detail out of Calibration Channels
) cal_channels
;
/******************************************************************************************************************************************/
/* LOAD DATA - ReturnItemsCalibration_Channel */
/****************************************************************************************************************************************/
INSERT INTO [ReturnItemsCalibration_Channels]([ReturnItemsID],[RowNo],[Channel],[Channel_Name],[UNIT],[MIN],[MAX],[std_point_1],[std_point_2],[std_point_3] )
SELECT
ri.ReturnItemsID
, ROW_NUMBER() OVER (
PARTITION BY [returns].id,cal_channels.serial_number
ORDER BY [returns].id,cal_channels.serial_number
) RowNo
-- CALLIBRATION CHANNELS
,calc_chan_detail.channel
,calc_chan_detail.name
,calc_chan_detail.UNIT
,calc_chan_detail.[MIN]
,calc_chan_detail.[MAX]
,calc_chan_detail.[std_point_1]
,calc_chan_detail.[std_point_2]
,calc_chan_detail.[std_point_3]
--INTO [ReturnItemsCalibration_Channels]
FROM
-- RETURNS
OPENJSON(@json)
WITH
(
-- Return
id INT '$.return.d3r_id'
) [returns]
CROSS APPLY
-- ITEMS
OPENJSON(@json, '$.return.items')
WITH
(
[name] NVARCHAR(30) ,
part_number NVARCHAR(30) ,
serial_number NVARCHAR(30) ,
calbration_channels NVARCHAR(MAX) AS JSON -- Use this to extract detail out of Calibration Channels
) [cal_channels]
-- Calibration Channels
CROSS APPLY
OPENJSON(cal_channels.calbration_channels)
WITH
(
channel CHAR(1),
[name] VARCHAR(50),
unit VARCHAR(10),
[min] VARCHAR(20),
[max] VARCHAR(20),
[std_point_1] VARCHAR(20),
[std_point_2] VARCHAR(20),
[std_point_3] VARCHAR(20)
) [calc_chan_detail]
JOIN
ReturnItems RI ON [returns].id = RI.[d3r_id] AND cal_channels.serial_number = RI.Serial_Number
;
SELECT
ri.ReturnItemsID
, ROW_NUMBER() OVER (
PARTITION BY [returns].id,cal_channels.serial_number
ORDER BY [returns].id,cal_channels.serial_number
) RowNo
-- CALLIBRATION CHANNELS
,calc_chan_detail.channel
,calc_chan_detail.name
,calc_chan_detail.UNIT
,calc_chan_detail.Points
,select value FROM STRING_SPLIT(calc_chan_detail.Points, ',')
,calc_chan_detail.[MIN]
,calc_chan_detail.[MAX]
,calc_chan_detail.[std_point_1]
,calc_chan_detail.[std_point_2]
,calc_chan_detail.[std_point_3]
--INTO [ReturnItemsCalibration_Channels]
FROM
-- RETURNS
OPENJSON(@json)
WITH
(
-- Return
id INT '$.return.d3r_id'
) [returns]
CROSS APPLY
-- ITEMS
OPENJSON(@json, '$.return.items')
WITH
(
[name] NVARCHAR(30) ,
part_number NVARCHAR(30) ,
serial_number NVARCHAR(30) ,
calbration_channels NVARCHAR(MAX) AS JSON -- Use this to extract detail out of Calibration Channels
) [cal_channels]
-- Calibration Channels
CROSS APPLY
OPENJSON(cal_channels.calbration_channels)
WITH
(
channel CHAR(1),
[name] VARCHAR(50),
unit VARCHAR(10),
Points VARCHAR(30),
[min] VARCHAR(20),
[max] VARCHAR(20),
[std_point_1] VARCHAR(20),
[std_point_2] VARCHAR(20),
[std_point_3] VARCHAR(20)
) [calc_chan_detail]
JOIN
ReturnItems RI ON [returns].id = RI.[d3r_id] AND cal_channels.serial_
April 21, 2022 at 1:18 pm
It's tl;dr. Here's a way to open that piece of JSON tho. At the end of the day JSON is based on key value pairs. If you don't provide OPENJSON with an explicit schema using WITH then it parses the JSON into key value pairs and assigns the psuedo-column names and [value] to the output. Maybe something like this
declare @json nvarchar(max)=N'
{
"calbration_channels": [
{
"channel": "A",
"name": "CO2",
"unit": "ppm",
"unit2": "ppmz",
"points": [
20,
80
]
}
]
}';
select jkvp.[value] as points
from openjson(@json, '$.calbration_channels')
with (channel nvarchar(4000),
[name] nvarchar(4000),
unit nvarchar(4000),
unit2 nvarchar(4000),
points nvarchar(max) as json) jo
cross apply openjson(jo.points) jkvp;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 21, 2022 at 1:23 pm
The word 'key' surrounded by brackets [] is not being displayed. It's there and I see it when I edit the post. It's supposed to say "...psuedo-column names 'key' surrounded by brackets [] and [value] to the output"
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 21, 2022 at 1:30 pm
Hi Steve
thanks for that .
i tried to adapt your code as follows but it didnt work
SELECT
ri.ReturnItemsID
, ROW_NUMBER() OVER (
PARTITION BY [returns].id,cal_channels.serial_number
ORDER BY [returns].id,cal_channels.serial_number
) RowNo
-- CALLIBRATION CHANNELS
,calc_chan_detail.channel
,calc_chan_detail.name
,calc_chan_detail.UNIT
,[calc_chan_ponts].*
--INTO [ReturnItemsCalibration_Channels]
FROM
-- RETURNS
OPENJSON(@json)
WITH
(
-- Return
id INT '$.return.d3r_id'
) [returns]
CROSS APPLY
-- ITEMS
OPENJSON(@json, '$.return.items')
WITH
(
[name] NVARCHAR(30) ,
part_number NVARCHAR(30) ,
serial_number NVARCHAR(30) ,
calbration_channels NVARCHAR(MAX) AS JSON -- Use this to extract detail out of Calibration Channels
) [cal_channels]
-- Calibration Channels
CROSS APPLY
OPENJSON(@json, '$.calbration_channels')
WITH
(
channel CHAR(1),
[name] VARCHAR(50),
unit VARCHAR(10),
points nvarchar(max) as json) jo
) [calc_chan_detail]
-- Calibration Channels
cross apply openjson(jo.points) jkvp
JOIN
ReturnItems RI ON [returns].id = RI.[d3r_id] AND cal_channels.serial_number = RI.Serial_Number
;
April 21, 2022 at 3:16 pm
Ok adapting Steve's code, I have managed to solve this:
My code as follows:
INSERT INTO [dbo].[ReturnItemsCalibration_Points] ([ReturnItemsCalibration_ChannelsID],[Points])
SELECT
RICC.ReturnItemsCalibration_ChannelsID,
jkvp.[Value] as Points
FROM
-- RETURNS
OPENJSON(@json)
WITH
(
-- Return
id INT '$.return.d3r_id'
) [returns]
CROSS APPLY
-- ITEMS
OPENJSON(@json, '$.return.items')
WITH
(
[name] NVARCHAR(30) ,
part_number NVARCHAR(30) ,
serial_number NVARCHAR(30) ,
calbration_channels NVARCHAR(MAX) AS JSON -- Use this to extract detail out of Calibration Channels
) [cal_channels]
-- Calibration Channels
CROSS APPLY
OPENJSON(cal_channels.calbration_channels)
WITH
(
channel CHAR(1),
[name] VARCHAR(50),
unit VARCHAR(10),
[min] VARCHAR(20),
[max] VARCHAR(20),
[std_point_1] VARCHAR(20),
[std_point_2] VARCHAR(20),
[std_point_3] VARCHAR(20),
points NVARCHAR(MAX) AS JSON
) [calc_chan_detail]
CROSS APPLY
OPENJSON([calc_chan_detail].points) jkvp
JOIN
ReturnItems RI ON [returns].id = RI.[d3r_id] AND cal_channels.serial_number = RI.Serial_Number
JOIN
[ReturnItemsCalibration_Channels] RICC ON ri.ReturnItemsID = RICC.ReturnItemsID
;
The key thing is to do the following:
1) Declare Points as NVARCHAR(MAX) AS JSON in the group cal_channels.calbration_channels
2) Then create CROSS APPLY using the following:
CROSS APPLY
OPENJSON([calc_chan_detail].points) jkvp
here [calc_chan_detail] refers to subquery
3) Then I can refer to the alias jkvp further up
jkvp.[Value] as Points
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply