March 5, 2025 at 3:14 pm
I have a text string that contains something similar to below :
[{"ChannelName":"LOT4 - Telephone","ChannelRecordId":"42c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false},{"ChannelName":"LOT4 - Video","ChannelRecordId":"46c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false}]
I need to strip out into separate columns or create additional records for the guid after the text "ChannelRecordID":" and also the false at the end (which might say true). The number of iterations of the phrase ChannelRecordID might also be different in this example its 2 but it could easily be 4,5,6 etc.
Any help would be much appreciated.
Thanks
Carl.
March 5, 2025 at 4:09 pm
This help?
DECLARE @code VARCHAR(1000) = '[{"ChannelName":"LOT4 - Telephone","ChannelRecordId":"42c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false},{"ChannelName":"LOT4 - Video","ChannelRecordId":"46c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false}]'
SELECT ISJSON(@code)
SELECT
JSON_VALUE(value, '$.ChannelRecordId') AS ChannelRecordId
FROM OPENJSON(@code) AS oj
March 5, 2025 at 4:43 pm
Hi, thanks for replying. its 50% of the way there. splits out my uniqueidentifier perfectly.
I however, also need the false or true part that relates to each identifier, so in the instance of the first channelrecordid it would be false.
'[{"ChannelName":"LOT4 - Telephone","ChannelRecordId":"42c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false}
Is this possible?
Thanks in advance
Carl.
March 5, 2025 at 5:01 pm
It is possible, by adding the json path for the true / false entry, giving you a second column in the results (or to insert into your table)
March 5, 2025 at 5:13 pm
You can use OPENJSON in SQL Server to parse the JSON string and extract the required fields dynamically.
Solution
DECLARE @json NVARCHAR(MAX) =
'[{"ChannelName":"LOT4 - Telephone","ChannelRecordId":"42c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false},
{"ChannelName":"LOT4 - Video","ChannelRecordId":"46c49edf-9aa1-ec11-b85f-00155d640f76","Removed":true}]'
SELECT [ChannelRecordId],
[Removed]
FROM OPENJSON(@json)
WITH (ChannelRecordId NVARCHAR(50) '$.ChannelRecordId', Removed BIT '$.Removed')
;
Explanation
OPENJSON(@json) allows you to parse the JSON array dynamically.
The WITH clause extracts specific properties: ChannelRecordId (GUID) and Removed (Boolean).
This will dynamically create records based on the number of occurrences.
Output
March 5, 2025 at 5:15 pm
Hi, thanks for replying. its 50% of the way there. splits out my uniqueidentifier perfectly.
I however, also need the false or true part that relates to each identifier, so in the instance of the first channelrecordid it would be false.
'[{"ChannelName":"LOT4 - Telephone","ChannelRecordId":"42c49edf-9aa1-ec11-b85f-00155d640f76","Removed":false}
Is this possible?
Thanks in advance
Carl.
To avoid further confusion and follow-ups, can I suggest that you show the output you are expecting/hoping for, based on the sample data you have provided?
March 5, 2025 at 5:17 pm
That is truly fantastic and going to save me so many headaches,
one last question I promise, can I turn this into a function where I pass it the field from my db, I tried the following for the part without the removed bit and I got an error
ALTER function [dbo].[fn_IRDecisionRoute] (@code varchar)
Returns uniqueidentifier
BEGIN
DECLARE @channelrecordid varchar
set @channelrecordid = (
--SELECT ISJSON(@code)
SELECT
JSON_VALUE(value, '$.ChannelRecordId') AS ChannelRecordId
FROM OPENJSON(@code) AS oj
)
return @channelrecordid
end
GO
March 5, 2025 at 5:42 pm
Ive also tried
SELECT top 1000 [cp_initialreviewId]
,[cp_AssessmentChannelOptions]
,(SELECT [ChannelName]
FROM OPENJSON(cp_AssessmentChannelOptions)
WITH (ChannelName NVARCHAR(50) '$.ChannelName'))
,(SELECT [Removed]
FROM OPENJSON(cp_AssessmentChannelOptions)
WITH (Removed BIT '$.Removed'))
FROM [PIP_MSCRM_MI_DB].[dbo].[cp_initialreviewBase]
But I get the error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Any ideas?
March 5, 2025 at 6:17 pm
Ive also tried
SELECT top 1000 [cp_initialreviewId] ,[cp_AssessmentChannelOptions] ,(SELECT [ChannelName] FROM OPENJSON(cp_AssessmentChannelOptions) WITH (ChannelName NVARCHAR(50) '$.ChannelName')) ,(SELECT [Removed] FROM OPENJSON(cp_AssessmentChannelOptions) WITH (Removed BIT '$.Removed'))
FROM [PIP_MSCRM_MI_DB].[dbo].[cp_initialreviewBase]
But I get the error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Any ideas?
Instead of using subqueries, use CROSS APPLY to extract multiple values per row dynamically.
SELECT TOP 1000
ir.[cp_initialreviewId],
j.[ChannelName],
j.[Removed]
FROM [PIP_MSCRM_MI_DB].[dbo].[cp_initialreviewBase] ir
CROSS APPLY OPENJSON(ir.cp_AssessmentChannelOptions)
WITH (
ChannelName NVARCHAR(50) '$.ChannelName',
Removed BIT '$.Removed'
) j;
March 5, 2025 at 6:23 pm
.
March 6, 2025 at 7:25 am
Thank You - you are all awesome
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply