This uses the 2nd library that I quoted.
I made this routine as flexible as I could for now - it should work for Tags or States.
Sample Output:
-- set the Table to search
declare @tblName varchar(255) = '[MenuItems]'
-- set the Field to search
declare @fieldName varchar(255) = '[CustomTags]'
-- set the JSON name/value pair to return
-- for Tags, they are TN and TV
-- for States, they are SN and S
declare @jsonName varchar(255) = 'TN'
declare @jsonVal varchar(255) = 'TV'
-- if you are looking for a specific JSON element, set it here
-- for example, if you are looking for a Tag named 'HH', specify that TagName here
-- for example, if you are looking for a State named 'Status', specify that StateName here
-- if this is left blank, all name/value pairs are returned
declare @elemName varchar(255) = ''
-- no need to set this variable - it will be overwritten anyway
declare @elemVal varchar(255) = ''
-- set the name-value pair separator
-- for example, if the TagName is HH and the TagValue is 10
-- then the return value will look like this: HH=10
declare @separator varchar(10) = '='
-- set the name/value pair delimiter
-- used when more than 1 name/value pair is returned
-- for example, if there are Tags with names of D1 and HH
-- and their values are 10 and 20
-- then the return value will look like this: D1=10~HH=20
declare @delimiter varchar(10) = '~'
-- if you only want to return the Value portion
-- of the name/value pair, then set this to 1
-- useful only if you are looking for a particular JSON Element
declare @valueOnly int = 0
-- filter for the results
-- specify 1 or all of the following
declare @firstID varchar(10) = 0
declare @lastID varchar(10) = 0
declare @exactID varchar(10) = 0
declare @itemName varchar(255) = ''
-- nothing else to set beyond here
declare @jn varchar(255)=''
declare @jv varchar(255)=''
declare @delim varchar(10) = ''
declare @sep varchar(10) = ''
declare @jsonCount int = 0
declare @rowCount int = 0
declare @r int = 0
declare @j int = 0
declare @sql nvarchar(max) = ''
declare @hasName int = 0
IF @separator='' SET @sep='=' ELSE SET @sep=@separator
IF @delimiter='' SET @delimiter='~'
--------------------------------------------------
-- we will use a #temp table to store results
--------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#jdata', 'U') IS NOT NULL
DROP TABLE #jdata;
create table #jdata (
[Id] int identity(1,1)
,[TableName] varchar(255) not null
,[FieldName] varchar(255) not null
,[ItemId] int null
,[ItemName] varchar(255) null
,[JSONdata] nvarchar(max) null
,[JSONcount] int null
,[JSONpairs] nvarchar(max) null
)
SELECT @hasName=count(COLUMN_NAME)
--TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, ORDINAL_POSITION, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as isIDENTITY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME= replace(replace(@tblName,'[',''),']','')
AND COLUMN_NAME = 'Name'
--------------------------------------------------
-- dynamic creation of query, so we can use this
-- with different tables and field names
--------------------------------------------------
SET @sql = 'INSERT INTO #jdata ([TableName], [FieldName], [ItemId], [ItemName], [JSONdata], [JSONcount], [JSONpairs])'
SET @sql = @sql + ' SELECT '
SET @sql = @sql + ''''+@tblName+''', '
SET @sql = @sql + ''''+@fieldName+''', '
SET @sql = @sql + '[Id], '
IF (@hasName=1) SET @sql = @sql + '[Name], ' ELSE SET @sql = @sql + ''''', '
SET @sql = @sql + @fieldName+', '
SET @sql = @sql + 'isnull((LEN('+@fieldName+') - LEN(REPLACE('+@fieldName+',''{'',''''))),-1), '
SET @sql = @sql + ''''''
SET @sql = @sql + ' FROM '+@tblName
SET @sql = @sql + ' WHERE 1=1'
IF (@firstID!=0 and @exactID=0 and (@itemName='' or @hasName=0)) SET @sql = @sql + ' AND [Id]>=' + @firstID
IF (@lastID!=0 and @exactID=0 and (@itemName='' or @hasName=0)) SET @sql = @sql + ' AND [Id]<=' + @lastID
IF (@exactID!=0) SET @sql = @sql + ' AND [Id]=' + @exactID
IF (@itemName!='' and @hasName=1) SET @sql = @sql + ' AND [Name]=' + ''''+ @itemName + ''''
SET @sql = @sql + ' AND '+@fieldName+'!=''[]'''
SET @sql = @sql + ' AND '+@fieldName+' is not null'
--IF (@elemName!='') SET @sql = @sql + ' AND '+@fieldName+' LIKE ''%'+'{"'+@jsonName+'":"' +@elemName+ '","'+@jsonVal+'":"'+'%'+'"}'+'%'''
IF (@elemName!='') SET @sql = @sql + ' AND '+@fieldName+' LIKE ''%'+'"'+@jsonName+'":"' +@elemName+ '"'+'%'''
IF (@hasName=1) SET @sql = @sql + ' ORDER BY [Name]'
print @sql
-- execute the query
EXEC sp_executesql @sql
SELECT @rowCount = max([Id]) FROM #jdata
print @rowCount
--------------------------------------------------
-- now go to work... we will store results by
-- updating column #jdata.[JSONpairs]
--------------------------------------------------
WHILE @r<=@rowCount -- iterator for rows/records
BEGIN
SET @j=0
SET @jsonCount=0
SELECT @jsonCount=[JSONcount] FROM #jdata WHERE [Id]=@r
WHILE @j<@jsonCount -- iterator for name/value pairs
BEGIN
IF (@j>0) SET @delim=@delimiter ELSE SET @delim=''
-- get the name
SELECT @jn=dbo.json_value([JSONdata],(convert(varchar(5),@j))+'.'+@jsonName) FROM #jdata WHERE [Id]=@r
-- get the value
SELECT @jv=dbo.json_value([JSONdata],(convert(varchar(5),@j))+'.'+@jsonVal) FROM #jdata WHERE [Id]=@r
-- if we are looking for a specific name, and we matched it, write that name/value pair
IF (@elemName!='' and @elemName=@jn)
BEGIN
IF (@valueOnly=0) UPDATE #jdata SET [JSONpairs] = @jn +@sep+ @jv WHERE [Id]=@r
IF (@valueOnly=1) UPDATE #jdata SET [JSONpairs] = @jv WHERE [Id]=@r
END
-- if we are not looking for a specific name, append the name/value pair
IF (@elemName='') UPDATE #jdata SET [JSONpairs] = [JSONpairs] + @delim + @jn +@sep+ @jv WHERE [Id]=@r
SET @j=@j+1
END
SET @r=@r+1
END
SELECT * FROM #jdata
--drop table #jdata