Having trouble insert rows from an API payload that have an empty array, well it's not really empty it comes out as [ ] - usually len=20. Now this is a batch insert. Have an HTTP request that pulls 1k records that gets picked up via a stored proc. Which grabs the bulk and does an insert or tries to in this case.
Example JSON payload (took out all the actual data):
{
"data": [
{
"id": 0,
"display_name": "X",
"title": null,
"profile_photo_url": "x",
"can_manage": true,
"role_name": "x",
"team_name": "x",
"about_me": null,
"facebook": null,
"twitter": null,
"website": null,
"on_hold": null,
"appear_in_directory": true,
"appear_in_near_me": true,
"is_volunteer": true,
"is_non_volunteer": false,
"is_system": false,
"anonymised": null,
"volunteer_for": "x",
"motivation": null,
"first_name": "x",
"last_name": "x",
"username": "x",
"dob": "2099-12-31",
"preferred_name": null,
"ex_directory": false,
"on_hold_start": null,
"on_hold_end": null,
"start_date": "",
"status": {
"id": 1,
"name": "Active",
"contactable": 1
},
"is_minor": false,
"2fa_enabled": 0,
"2fa_disabled": false,
"task_due_today": false,
"internal_email": null,
"accessibility_info": "",
"can_receive_public_enquiries": false,
"finance_ref": null,
"int_ref": null,
"sso_enabled": false,
"roleProfiles": [
{
"id": 0,
"name": "x",
"display_name": "x",
"primary": false,
"team": {
"id": 0,
"parent_id": 0,
"name": "x",
"display_name": "x",
"description": "",
"geo_location": null,
"catchment_area_id": null,
"icon": "fa fa-users",
"show_on_public_site": false,
"show_on_near_me_map": false,
"ceiling_enabled": true,
"public_url": null,
"hidden": false,
"disabled": false,
"timezone": "Europe/London",
"website_url": null,
"external_reference": ""
},
"type": "x",
"role_profile_id": 0,
"leaving_form_required": true,
"review_submitted_at": null,
"left_org": null,
"would_you_like_to_have_back": null,
"would_you_recommend_to_organisation": null,
"reference": null,
"leaving_reason": {
"id": 0,
"reason": "x",
"volunteer_selectable": true
},
"feedback": "",
"recommend_organisation": null,
"receive_team_notifications": null,
"custom_title": "x",
"start": "2099-12-31",
"end": "2099-12-31",
"risks_pdf": "x",
"role_pdf": "x",
"manager": {
"id": 0,
"display_name": "x",
"title": "x",
"profile_photo_url": "x",
"can_manage": true,
"role_name": "x",
"team_name": "x",
"status": {
"id": 1,
"name": "Active",
"contactable": 1
}
},
"recruitment_category": {
"id": 0,
"name": "Simple",
"apply_show_diversity": true,
"diversity_compulsory": true,
"apply_show_address": false,
"apply_show_about_you": false,
"phone_number_compulsory": true,
"apply_right_to_work": false,
"stages": [
{
"id": 0,
"name": "x",
"display_name": "x",
"sla_ideal": null,
"sla_max": null,
"automatic": 1,
"named_manager_required": 0
},
{
"id": 0,
"name": "x",
"display_name": "x",
"sla_ideal": null,
"sla_max": null,
"automatic": 0,
"named_manager_required": 0
},
{
"id": 0,
"name": "z",
"display_name": "z",
"sla_ideal": null,
"sla_max": null,
"automatic": 0,
"named_manager_required": 0
}
]
},
"risks": [
],
"permission_group": {
"id": 0,
"name": "z",
"description": "z"
}
},
{
"id": 0,
"name": "z",
"display_name": "z",
"primary": true,
"team": {
"id": 0,
"parent_id": 0,
"name": "x",
"display_name": "x",
"description": "",
"geo_location": null,
"catchment_area_id": null,
"icon": "fa fa-users",
"show_on_public_site": false,
"show_on_near_me_map": false,
"ceiling_enabled": true,
"public_url": null,
"hidden": false,
"disabled": false,
"timezone": "Europe/London",
"website_url": null,
"external_reference": ""
},
"type": "x",
"role_profile_id": 0,
"leaving_form_required": true,
"review_submitted_at": null,
"left_org": null,
"would_you_like_to_have_back": null,
"would_you_recommend_to_organisation": null,
"reference": null,
"leaving_reason": null,
"feedback": "",
"recommend_organisation": null,
"receive_team_notifications": null,
"custom_title": "",
"start": "2099-12-31",
"end": null,
"risks_pdf": "x",
"role_pdf": "x",
"manager": {
"id": 0,
"display_name": "x",
"title": "",
"profile_photo_url": "x",
"can_manage": true,
"role_name": "x",
"team_name": "x",
"status": {
"id": 1,
"name": "Active",
"contactable": 1
}
},
"recruitment_category": {
"id": 0,
"name": "Simple",
"apply_show_diversity": true,
"diversity_compulsory": true,
"apply_show_address": false,
"apply_show_about_you": false,
"phone_number_compulsory": true,
"apply_right_to_work": false,
"stages": [
{
"id": 0,
"name": "z",
"display_name": "z",
"sla_ideal": null,
"sla_max": null,
"automatic": 1,
"named_manager_required": 0
},
{
"id": 0,
"name": "z",
"display_name": "z",
"sla_ideal": null,
"sla_max": null,
"automatic": 0,
"named_manager_required": 0
},
{
"id": 0,
"name": "z",
"display_name": "z",
"sla_ideal": null,
"sla_max": null,
"automatic": 0,
"named_manager_required": 0
}
]
},
"risks": [
],
"permission_group": {
"id": 0,
"name": "z",
"description": "z"
}
}
],
"emailAddresses": [
]
}
],
"meta": {
"pagination": {
"total": 1180,
"count": 180,
"per_page": 1000,
"current_page": 2,
"total_pages": 2,
"links": {
"previous": "https://page=1"
}
}
}
}
Only interested in roleProfiles and emailAddresses arrays. Either one can be empty like mentioned above.
SQL code that is used:
CREATE PROCEDURE [dbo].[TestJson]
@json NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
-- Ensure the JSON is properly formatted
DECLARE @ParsedJson NVARCHAR(MAX);
-- Attempt to parse the JSON if it's escaped
IF ISJSON(@json) = 1
SET @ParsedJson = @json;
ELSE
SET @ParsedJson = JSON_QUERY(JSON_VALUE(@json, '$'));
-- If JSON is still invalid, exit to avoid errors
IF ISJSON(@ParsedJson) = 0
BEGIN
PRINT 'Invalid JSON format';
RETURN;
END;
-- Extract the arrays from JSON
DECLARE @RoleProfiles NVARCHAR(MAX) = JSON_QUERY(@ParsedJson, '$.data.roleProfiles');
DECLARE @EmailAddresses NVARCHAR(MAX) = JSON_QUERY(@ParsedJson, '$.data.emailAddresses');
-- Debugging: Check if EmailAddresses is valid or empty
PRINT 'EmailAddresses before check: ' + ISNULL(@EmailAddresses, 'NULL');
-- If EmailAddresses is NULL or empty, set it to '[]'
IF @EmailAddresses IS NULL OR @EmailAddresses = ''
BEGIN
SET @EmailAddresses = '[]';
PRINT 'EmailAddresses set to empty array';
END
-- Debugging: Check EmailAddresses after ensuring it’s not NULL
PRINT 'EmailAddresses after check: ' + @EmailAddresses;
-- Check the length of the arrays and replace them with "[]" if less than 25 elements or empty
SET @RoleProfiles = CASE
WHEN LEN(REPLACE(REPLACE(REPLACE(@RoleProfiles, CHAR(13), ''), CHAR(10), ''), ' ', '')) < 25 OR
TRIM(REPLACE(REPLACE(REPLACE(@RoleProfiles, ' ', ''), CHAR(13), ''), CHAR(10), '')) = '[]' OR
TRIM(@RoleProfiles) = '' THEN '[]' -- Treat arrays with spaces or empty as '[]'
ELSE @RoleProfiles
END;
-- If RoleProfiles is NULL or empty, set it to '[]'
IF @RoleProfiles IS NULL OR @RoleProfiles = ''
BEGIN
SET @RoleProfiles = '[]';
PRINT 'RoleProfiles set to empty array';
END;
WITH j AS (
SELECT
-- Basic fields
j.id AS UserId,
j.display_name AS DisplayName,
j.title AS Title,
TRY_CAST(j.dob AS DATE) AS DOB, -- Use TRY_CAST for safe date conversion
TRY_CAST(j.start_date AS DATE) AS StartDate, -- Use TRY_CAST for safe date conversion
s.id as StatusId,
s.name AS StatusName,
s.contactable as StatusContactable,
j.role_name AS RoleName,
j.team_name as TeamName,
--j.about_me AS AboutMe,
--j.facebook AS Facebook,
--j.twitter AS Twitter,
--j.website AS Website,
-- Convert string values to BIT explicitly (for example, 'true' or 'false' to 1 or 0)
CASE
WHEN j.on_hold = 'true' THEN 1
WHEN j.on_hold = 'false' THEN 0
ELSE NULL
END AS OnHold,
CASE
WHEN j.appear_in_directory = 'true' THEN 1
WHEN j.appear_in_directory = 'false' THEN 0
ELSE NULL
END AS AppearInDirectory,
CASE
WHEN j.appear_in_near_me = 'true' THEN 1
WHEN j.appear_in_near_me = 'false' THEN 0
ELSE NULL
END AS AppearInNearMe,
CASE
WHEN j.is_volunteer = 'true' THEN 1
WHEN j.is_volunteer = 'false' THEN 0
ELSE NULL
END AS IsVolunteer,
CASE
WHEN j.is_non_volunteer = 'true' THEN 1
WHEN j.is_non_volunteer = 'false' THEN 0
ELSE NULL
END AS IsNonVolunteer,
CASE
WHEN j.is_system = 'true' THEN 1
WHEN j.is_system = 'false' THEN 0
ELSE NULL
END AS IsSystem,
CASE
WHEN j.ex_directory = 'true' THEN 1
WHEN j.ex_directory = 'false' THEN 0
ELSE NULL
END AS ExDirectory,
-- Personal Details
j.first_name AS FirstName,
j.last_name AS LastName,
j.username AS Username,
j.preferred_name AS PreferredName,
-- Use TRY_CAST for safe date conversion
TRY_CAST(j.on_hold_start AS DATETIME) AS OnHoldStart,
TRY_CAST(j.on_hold_end AS DATETIME) AS OnHoldEnd,
j.internal_email AS InternalEmail,
--j.accessibility_info AS AccessibilityInfo,
--j.can_receive_public_enquiries AS CanReceivePublicEnquiries,
--j.finance_ref AS FinanceRef,
--j.int_ref AS IntRef,
--j.sso_enabled AS SsoEnabled,
-- Extract RoleProfiles from the JSON array
rp.RoleProfileId,
rp.RoleProfileName,
rp.RoleProfileDisplayName,
rp.RoleType,
rp.RoleProfileIdReference,
TRY_CAST(rp.StartDate AS DATETIME) AS RoleProfileStartDate, -- Use TRY_CAST for safe date conversion
TRY_CAST(rp.EndDate AS DATETIME) AS RoleProfileEndDate, -- Use TRY_CAST for safe date conversion
-- Extract RoleProfiles and EmailAddresses (Aliases for TeamName, IsPrimary, and TeamID)
rp.TeamName AS RoleProfileTeamName,
rp.TeamId AS RoleProfileTeamId,
rp.IsPrimary AS RoleProfileIsPrimary,
rp.TeamParentId,
rp.TeamDisplayName,
rp.LeavingFormRequired,
rp.ReviewSubmittedAt,
rp.LeftOrg,
rp.WouldYouLikeToHaveBack,
rp.WouldYouRecommendToOrganisation,
rp.Reference AS RoleProfileReference,
rp.LeavingReason,
rp.Feedback,
rp.RecommendOrganisation,
--rp.ReceiveTeamNotifications,
rp.CustomTitle,
--rp.RisksPDF,
--rp.RolePDF,
rp.ManagerId,
rp.ManagerTitle,
rp.ManagerName,
rp.ManagerRoleName,
rp.ManagerTeamName,
rp.ManagerStatusId,
rp.ManagerStatusName,
rp.ManagerStatusContactable,
-- Extract EmailAddresses from the JSON array
ea.EmailId,
ea.EmailUserId,
ea.EmailCreatedAt,
ea.EmailUpdatedAt,
ea.Email,
ea.IsPrimary AS EmailIsPrimary
--FROM OPENJSON(@json, '$.body.data')
FROM OPENJSON(@ParsedJson, '$.data')
WITH (
id INT '$.id',
display_name NVARCHAR(255) '$.display_name',
title NVARCHAR(50) '$.title',
dob NVARCHAR(50) '$.dob', -- Change to NVARCHAR for safe conversion
start_date NVARCHAR(50) '$.start_date', -- Change to NVARCHAR for safe conversion
role_name NVARCHAR(255) '$.role_name',
team_name NVARCHAR(255) '$.team_name',
--about_me NVARCHAR(MAX) '$.about_me',
--facebook NVARCHAR(255) '$.facebook',
--twitter NVARCHAR(255) '$.twitter',
--website NVARCHAR(255) '$.website',
on_hold NVARCHAR(10) '$.on_hold',
appear_in_directory NVARCHAR(10) '$.appear_in_directory',
appear_in_near_me NVARCHAR(10) '$.appear_in_near_me',
is_volunteer NVARCHAR(10) '$.is_volunteer',
is_non_volunteer NVARCHAR(10) '$.is_non_volunteer',
is_system NVARCHAR(10) '$.is_system',
anonymised NVARCHAR(MAX) '$.anonymised',
volunteer_for NVARCHAR(MAX) '$.volunteer_for',
motivation NVARCHAR(MAX) '$.motivation',
first_name NVARCHAR(255) '$.first_name',
last_name NVARCHAR(255) '$.last_name',
username NVARCHAR(255) '$.username',
preferred_name NVARCHAR(255) '$.preferred_name',
ex_directory NVARCHAR(10) '$.ex_directory',
on_hold_start NVARCHAR(50) '$.on_hold_start', -- Change to NVARCHAR for safe conversion
on_hold_end NVARCHAR(50) '$.on_hold_end', -- Change to NVARCHAR for safe conversion
internal_email NVARCHAR(255) '$.internal_email',
--accessibility_info NVARCHAR(MAX) '$.accessibility_info',
--can_receive_public_enquiries NVARCHAR(10) '$.can_receive_public_enquiries',
--finance_ref NVARCHAR(MAX) '$.finance_ref',
--int_ref NVARCHAR(MAX) '$.int_ref',
--sso_enabled NVARCHAR(10) '$.sso_enabled',
-- Extract Status as JSON
status NVARCHAR(MAX) AS JSON,
-- Extract Nested Arrays as JSON strings
roleProfiles NVARCHAR(MAX) AS JSON,
emailAddresses NVARCHAR(MAX) AS JSON
--,profile_photo_url NVARCHAR(500) '$.profile_photo_url'
) AS j
-- Extract Status Name
CROSS APPLY OPENJSON(j.status) WITH (
name NVARCHAR(50),
id int,
contactable bit
) AS s
-- Extract RoleProfiles array
CROSS APPLY OPENJSON(j.roleProfiles)
WITH (
RoleProfileId INT '$.id',
RoleProfileName NVARCHAR(255) '$.name',
RoleProfileDisplayName NVARCHAR(255) '$.display_name',
IsPrimary BIT '$.primary',
RoleType NVARCHAR(50) '$.type',
RoleProfileIdReference INT '$.role_profile_id',
StartDate NVARCHAR(50) '$.start', -- Change to NVARCHAR for safe conversion
EndDate NVARCHAR(50) '$.end', -- Change to NVARCHAR for safe conversion
LeavingFormRequired BIT '$.leaving_form_required',
ReviewSubmittedAt NVARCHAR(50) '$.review_submitted_at', -- Change to NVARCHAR for safe conversion
LeftOrg BIT '$.left_org',
WouldYouLikeToHaveBack BIT '$.would_you_like_to_have_back',
WouldYouRecommendToOrganisation BIT '$.would_you_recommend_to_organisation',
Reference NVARCHAR(255) '$.reference',
LeavingReason NVARCHAR(255) '$.leaving_reason',
Feedback NVARCHAR(MAX) '$.feedback',
RecommendOrganisation BIT '$.recommend_organisation',
--ReceiveTeamNotifications BIT '$.receive_team_notifications',
CustomTitle NVARCHAR(255) '$.custom_title',
--RisksPDF NVARCHAR(MAX) '$.risks_pdf',
--RolePDF NVARCHAR(MAX) '$.role_pdf',
ManagerId INT '$.manager.id',
ManagerName NVARCHAR(255) '$.manager.display_name',
ManagerTitle NVARCHAR(255) '$.manager.title',
ManagerRoleName NVARCHAR(255) '$.manager.role_name',
ManagerTeamName NVARCHAR(255) '$.manager.team_name',
ManagerStatusId int '$.manager.status.id',
ManagerStatusName NVARCHAR(255) '$.manager.status.name',
ManagerStatusContactable bit '$.manager.status.contactable',
-- Adding Team information as JSON properties
TeamName NVARCHAR(255) '$.team.name',
TeamId INT '$.team.id',
TeamParentId INT '$.team.parent_id',
TeamDisplayName NVARCHAR(255) '$.team.display_name',
IsPrimary BIT '$.primary'
) AS rp
-- Extract EmailAddresses array
CROSS APPLY OPENJSON(j.emailAddresses)
WITH (
EmailId INT '$.id',
Email NVARCHAR(255) '$.email',
IsPrimary BIT '$.primary',
EmailUserId INT '$.user_id',
EmailCreatedAt datetime '$.created_at',
EmailUpdatedAt datetime '$.updated_at'
) AS ea
)
insert into dbo.testtable
SELECT * FROM j;
END;
Might be late or maybe I've spent too much looking at this, can't seem to find a way to replace that empty array with something like [""]. Help is greatly appreciated. Thanks in advance!
I've scrap the logic at the top and adjusted the joins. Cross Apply changed to Outer Apply - for the arrays. That fixed it. Nothing works than some sleep!