Nesting JSON query result in SQL server 2016 -

i have following test data:

create table #t (meterid int,[description] varchar(255),[status] varchar(55),date datetime2(0),reading int) insert #t select 4201544,'home test','active','2016/03/23 06:13',105678 insert #t select 4201544,'home test','active','2016/03/23 06:14',105682 insert #t select 4201544,'home test','active','2016/03/23 06:15',105684 insert #t select 4201544,'home test','active','2016/03/23 06:16',105685 insert #t select 4201544,'home test','active','2016/03/23 06:17',105686 insert #t select 4201544,'home test','active','2016/03/23 06:18',105688 insert #t select 4201544,'home test','active','2016/03/23 06:19',105688 insert #t select 4201544,'home test','active','2016/03/23 06:20',105689 insert #t select 4201544,'home test','active','2016/03/23 06:21',105690 

which extracted desired json follows:

select     [meterid],     [description],     [status],     (select         [date],         [reading]             #t     json path) [readings]     #t group     meterid,     [description],     [status] json path, root ('data') 

in case there few rows , query executes fast. however, in production applied on many more rows. seems inefficient query #t twice.

is there better way achieve same result? tried unsuccessfully apply aliases on single query of #t.

you cannot avoid 2 scans. maybe query work better:

with groups ( select     [meterid],     [description]      #t)  select groups.[meterid], groups.[description], [status], [date], [reading]     groups join #t readings on groups.meterid = readings.meterid json auto, root ('data') 

with json auto have nested results (still 2 joins), avoid costly udx , sort (i see cost of sort operator in plan ~60% compared 20% table scans).