question

MohammadFarook-3243 avatar image
0 Votes"
MohammadFarook-3243 asked MelissaMa-msft answered

Select value only in json column


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
sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
1 Vote"
Viorel-1 answered Viorel-1 edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered pituach edited

Good day,

Before I post this solution, I must clarify that @Viorel-1 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-1 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.








5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @MohammadFarook-3243

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.