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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy