Select value only in json column

Question

Hi, i Need select value only.

create table #temp (ID int,Emplist varchar(200))
insert into #temp (ID,Emplist) VALUES (1,'[{"EmpID":1}]')
,(2,'[{"EmpID":18},{"EmpID":23}]')
,(2,'[{"EmpID":19}]')
select * from #temp
drop table #temp

I want result like below

ID  Emplist
1   1
2   18,23
2   19

Answer 1

Check two approaches:

select ID, (select string_agg(EmpId, ',') from openjson (EmpList) with ( EmpId int '$.EmpID' )) as EmpList
from #temp

If STRING_AGG is not available:

select ID, 
    stuff(
        (select ',' + EmpId
        from openjson (EmpList) with ( EmpId varchar(max) '$.EmpID' )
        for xml path('')),
        1, 1, ''
    ) as EmpList
from #temp

Answer 2

Good day,

Before I post this solution, I must clarify that @Viorel provided the solution which should work in all such cases and it is fully flexible +1

gain a better performance

With that being said, if we want to gain a better performance and we know that the number of nodes (Emplist) in the JSON is limited and small, then there is a better solution. The use of openjson +string_agg means that we split the JSON into tabular structure and then we scan the set and re-merge the values - this is expensive task !

Let's assume that we know by 100% sure that we have less than three nodes for each ID, so we can use this solution:

SELECT   
 ID,  
 CONCAT (JSON_VALUE(Emplist, '$[0].EmpID'),',' + JSON_VALUE(Emplist, '$[1].EmpID'),',' + JSON_VALUE(Emplist, '$[2].EmpID'))  
FROM #temp  

Note: if the max nodes is X then we only need add x times ,',' + JSON_VALUE(Emplist, '$[1].EmpID'),' to the code.

Performance should be much better according to my tests

For the sake of the test since I am lazy, I created a new table and insert the rows from the OP table multiple times

-- Preperation: new table with 100,000 rows  
DROP TABLE IF EXISTS T  
GO  
CREATE TABLE T(ID int,Emplist varchar(200))  
GO  
INSERT T(ID,Emplist)  
 SELECT top 100000 t1.ID,t1.Emplist  
 FROM #temp t1  
 CROSS JOIN sys.all_objects t2  
 CROSS JOIN sys.all_objects t3  
GO  

Testing both solution after SET STATiSTICS TIME ON

-- My trick:  
SELECT   
 ID,  
 CONCAT (JSON_VALUE(Emplist, '$[0].EmpID'),',' + JSON_VALUE(Emplist, '$[1].EmpID'),',' + JSON_VALUE(Emplist, '$[2].EmpID'))  
FROM T  
GO  
-- SQL Server Execution Times:  
--   CPU time = 78 ms,  elapsed time = 1991 ms.  
  
-- @Viorel-1 flexible solution  
select ID,   
 (  
 select string_agg(EmpId, ',')   
 from openjson (EmpList)   
 with ( EmpId int '$.EmpID' )  
 ) as EmpList  
from T  
GO  
-- SQL Server Execution Times:  
--   CPU time = 94 ms,  elapsed time = 2636 ms.  

Moreover!!!! My trick fit for SQL Server 2016 as well and if you use SQL Server 2016 then STRING_AGG is not available and you need to use @Viorel second solution, which I recommend NOT TO USE for most cases as you can see in the following times:

select ID,   
    stuff(  
        (select ',' + EmpId  
        from openjson (EmpList) with ( EmpId varchar(max) '$.EmpID' )  
        for xml path('')),  
        1, 1, ''  
    ) as EmpList  
from T  
GO  
-- SQL Server Execution Times:  
--   CPU time = 78 ms,  elapsed time = 8746 ms.  

Note: in some case the differences in time is less or more. this is just the times in one execution for example but my solution always return faster in tests.

Answer 3

Hi @Mohammad Farook ，

You could refer Viorel's query.

Please also refer below:

 select id,string_agg(JSON_VALUE(value, '$.EmpID'),',') Emplist  
 from (select row_number() over (order by (select null)) rn,id, Emplist from #temp) a  
 CROSS APPLY STRING_SPLIT(substring(Emplist,2,len(Emplist)-2), ',')  
 group by id,rn  

Output:

id	Emplist  
1	1  
2	18,23  
2	19  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.