Extract value from double escaped JSON column in SQL

Question

I have ports_list table with ports column below:

ports	intended_output
"[{\"id\":193,\"name\":\"PORT C\"}]"	PORT C
"[{\"id\":204,\"name\":\"PORT D\"}]"	PORT D
"[{\"id\":45,\"name\":\"PORT I\"},{\"id\":193,\"name\":\"PORT C\"},{\"id\":204,\"name\":\"PORT D\"},{\"id\":271,\"name\":\"PORT G\"}]"	PORT I, PORT C, PORT D, PORT G

I use this method and somehow managed to get result when there is only single port value in a row unfortunately it is unable to cater for multiple port values in a row:

select ports,
REPLACE( substring(ports, CHARINDEX('"name\":\', ports, 1)+10, LEN(ports)-10), 
                        '\"}]"', 
                        ''
                       )
from ports_list

ports	output
"[{\"id\":193,\"name\":\"PORT C\"}]"	PORT C
"[{\"id\":204,\"name\":\"PORT D\"}]"	PORT D
"[{\"id\":45,\"name\":\"PORT I\"},{\"id\":193,\"name\":\"PORT C\"},{\"id\":204,\"name\":\"PORT D\"},{\"id\":271,\"name\":\"PORT G\"}]"	PORT I"},{"id":193,"name":"PORT C"},{"id":204,"name":"PORT D"},{"id":271,"name":"PORT G"}]"

Can someone help me to get the intended output as per the first table?

Answer 1

Your ports column is a double-escaped JSON, in other words, it contains a JSON-escaped string that represents a JSON object.

So we just need to get it back into regular JSON to query it. Let's stuff it into a [] JSON array, then pass it to JSON_VALUE:

select ports,
    STRING_AGG(j.name, ', ')
from ports_list
outer apply OPENJSON(JSON_VALUE('[' + ports + ']', '$[0]'))
    with (name nvarchar(100) '$.name') as j
group by ports;

Answer 2

You created a soluntion to resolve only when exists one port.

The function CharIndex is returning the first ocorrency of '"name":' always and the function SUBSTRING always return the string minus the ten last characters.

A form to resolve the problem is creating the user function to return all ocorrency of port.

try create the function below

CREATE FUNCTION uf_findPorts
(
    @string nvarchar(max)
)
RETURNS nvarchar(MAX)
AS
BEGIN
    DECLARE
          @length INT ,
          @aux INT ,
          @return NVARCHAR(MAX)
           

  
  SET @length = LEN(@string)
  SET @aux = 1
  SET @return = ''


  WHILE(CHARINDEX('"name\":\', @string,@aux) != 0)
  BEGIN 
    SET @return = @return + '  ' + REPLACE( substring(@string,
            CHARINDEX('"name\":\', @string,@aux)+10, 
               6), 
                        '\"}]"', 
                        ''
                       )
    
    SET @string = SUBSTRING(@string,CHARINDEX('"name\":\', @string,@aux) + 6,LEN(@string) )
    

  END

  RETURN @return
 

END
GO

To Test:

select ports,dbo.uf_findPorts(ports)
from ports_list

SELECT dbo.uf_findPorts('[{\"id\":45,\"name\":\"PORT I\"},{\"id\":193,\"name\":\"PORT C\"},{\"id\":204,\"name\":\"PORT D\"},{\"id\":271,\"name\":\"PORT G\"}]')

SELECT dbo.uf_findPorts('"[{\"id\":193,\"name\":\"PORT C\"}]"')

OBS: If exists any error of write you can corret me

Answer 3

Thanks to those who tried to help.

Posting my solution here if anyone stumbled into similar problem in the future:

 select ports = replace(replace(replace(dbo.fnRemovePatternFromString(dbo.fnRemovePatternFromString(dbo.fnRemovePatternFromString(dbo.fnRemovePatternFromString(ports,'%[\"{}]%',1),'%name:%',5),'%id:%',3),'%[0-9]%',1),'[,',''),',,',','),']','')
    from ports_list