March 21, 2022 at 6:40 pm
Hi there
I have a sample JSON file,.. from which I am attempting to extract data from using OPENJSON in SQL.
Now the file is broken down in the 3 main sections:
1) Return
2) Customer
3) Address
Now I am able to extract the Return and Item information . However Ive got 2 issues:
1) I am struggling to extract the Callibration_Channels data out of the items subgroup
2) Im stuck on relating child sub groups to their parents
In the attached screenshot you can see when the SQL script (Json_CommonQueries.sql (attached) is run,
i get the Return and Return items but not the Callibration Channels
I also want to group the data and relate the groups back..
Ie items is a child of return (Linked via d3r_id) and Callibration_Channels is related back to items (via Serial Number)
Attached are the folllowing:
2. Json_CommonQueries.txt (SQL Query I used to extract data from the JSON file)
3. RET28092107 json.txt (source JSON file)
4. ReturnsFile.xlsx (Excel spreadsheet showing what i want to return by running the script Json_CommonQueries.sql)
March 21, 2022 at 9:18 pm
What I would strongly recommend to get good results on this forum is:
1 - post DDL in a consumable format such as:
CREATE TABLE sample (JSONData NVARCHAR(MAX))
2 - post sample data in a consumable format. We don't need ALL of your data, just a subset of it so we can test our solutions
3 - provide sample queries of what you tried. This lets us see your train of thought and work with you towards the solution rather than just handing you our answer which you may not understand and thus not be able to support.
Most people on the forum (myself included) do NOT trust downloading files from random sources such as a forum; especially files that are potentially malicious. Excel files can contain scripts in them that could compromise a system, so with what you posted, I am definitely not opening the excel file, and thus I have no intention of opening any of them as without the excel file, I can't see what you are expecting from the JSON.
On top of that, my JSON is INCREDIBLY rusty and I'd be doing a lot of googling before I was able to provide you with much of anything...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 22, 2022 at 1:11 am
Hi Brian
OK ...here is my sample JSON
DECLARE @json NVARCHAR(MAX);
SET @json = N'
{
"return": {
"d3r_id": "4723",
"reference":"ccRET28092107",
"po": "4500478023",
"customer_reference": "",
"type": "Calibration",
"notes": "",
"status": "In Transit",
"eori": false,
"sent_to_salesforce": "2021-09-28 18:03:01",
"items": [
{
"name": "TK-0014",
"part_number": null,
"serial_number": "305202",
"calibration_type": "Custom",
"calibration_notes": "20\u00b0C and 45\u00b0C",
"calbration_channels": [
{
"channel": "A",
"name": "Temperature",
"unit": "Degrees C",
"min": -25,
"max": 85,
"std_point_1": 0,
"std_point_2": 30,
"std_point_3": null
},
{
"channel": "B",
"name": "Relative Humidity",
"unit": "Percent RHS",
"min": 0,
"max": 100,
"std_point_1": 20,
"std_point_2": 0,
"std_point_3": null
}
],
"fault": "",
"return_reason": ""
},
{
"name": "TK-0014",
"part_number": null,
"serial_number": "305203",
"calibration_type": "Standard",
"calibration_notes": "20\u00b0C and 45\u00b0C",
"calbration_channels": [
{
"channel": "A",
"name": "Temperature",
"unit": "Degrees C",
"min": -25,
"max": 85,
"std_point_1": 0,
"std_point_2": 30,
"std_point_3": null
},
{
"channel": "B",
"name": "Relative Humidity",
"unit": "Percent RHS",
"min": 0,
"max": 100,
"std_point_1": 20,
"std_point_2": 0,
"std_point_3": null
},
{
"channel": "C",
"name": "Temperature",
"unit": "Degrees C",
"min": -25,
"max": 85,
"std_point_1": 0,
"std_point_2": 30,
"std_point_3": null
},
{
"channel": "D",
"name": "Relative Humidity",
"unit": "Percent RHS",
"min": 0,
"max": 100,
"std_point_1": 20,
"std_point_2": 0,
"std_point_3": null
}
],
"fault": "",
"return_reason": ""
}
]
},
"customer": {
"firstname": "Siobhan",
"lastname": "Lonergan",
"email": "s.lonergan@pinewood.ie",
"phone": "0526186000",
"company": "ACME Limited"
},
"address": {
"return": {
"company": "ABC Ltd",
"line_1": "Pinewood Healthcare",
"line_2": "Ballymacarbry",
"town": "Clonmel",
"state": "",
"county": "",
"country": "IE",
"postcode": "E91 D434 "
},
"delivery": {
"company": "XYZ Plc",
"line_1": "Pinewood Healthcare",
"line_2": "Ballymacarbry",
"town": "Clonmel",
"state": "",
"county": "",
"country": "IE",
"postcode": "E91 D434 "
}
}
}
';
Now this thg query that want to try and extract sub arrays such as the Calibration Channels into a seperate related group using CROSS APPLY
but its not working:
SELECT id, reference, po, customer_reference,type,status,eori,sent_to_salesforce,a.calbration_channels
FROM OPENJSON(@json)
WITH (
id INT '$.return.d3r_id',
reference NVARCHAR(50) '$.return.reference',
po NVARCHAR(50) '$.return.po',
customer_reference NVARCHAR(50) '$.return.customer_reference',
type NVARCHAR(50) '$.return.type',
notes NVARCHAR(50) '$.return.notes',
status NVARCHAR(50) '$.return.status',
eori NVARCHAR(50) '$.return.eori',
sent_to_salesforce NVARCHAR(50) '$.return.sent_to_salesforce'
,
calbration_channels NVARCHAR(MAX) '$.return.items.calbration_channels' AS JSON
)a
OUTER APPLY OPENJSON(calbration_channels)
WITH (calbration_channels NVARCHAR(8) '$');
However im not getting the Callibratioon Channels
March 22, 2022 at 10:17 am
This should help you dig into the hierarchy.
SELECT returns.id
,calc_chan_detail.channel
,calc_chan_detail.name
FROM
OPENJSON(@json)
WITH
(
id INT '$.return.d3r_id'
,items NVARCHAR(MAX) '$.return.items' AS JSON
) returns
CROSS APPLY
OPENJSON(returns.items)
WITH
(
calbration_channels NVARCHAR(MAX) AS JSON
) cal_channels
CROSS APPLY
OPENJSON(cal_channels.calbration_channels)
WITH
(
channel CHAR(1)
,name VARCHAR(50)
) calc_chan_detail;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 22, 2022 at 11:48 am
Hi Phil
Briliiant thank you very much that helps me dig into the hierarchy
Now from what I understand, you declare each section of the JSON you want to access
like this:
items NVARCHAR(MAX) '$.return.items' AS JSON
And then use this to access the individual elements in this items group
OPENJSON(returns.items)
WITH
(
part_number nvarchar(10) ,
serial_number nvarchar(10) ,
calbration_channels NVARCHAR(MAX) AS JSON
) cal_channels
I wanted to also select part_number and serial_number from this list
March 22, 2022 at 11:56 am
That looks good to me.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply