問題

我遇到了這個查詢的麻煩,我開始認為這根本不可能使用SQL Server.

我有以下非常簡單的測試查詢:

 select 
    t.*
from 
    (values 
        (1, 'foo1', cast('1977-05-19' as date), cast(1 as bit) , null),
        (2, 'foo2', cast('1978-05-19' as date), cast(0 as bit), 'bar2'),
        (3, 'foo3', cast('1979-05-19' as date), null, 'bar3')) t(Id, Foo, Day, Bool, Bar)
 

如果我想將結果轉換為JSON,我只需在末尾新增for json auto,我有很好的JSON結果,如我所料:

 [
    {"Id":1,"Foo":"foo1","Day":"1977-05-19","Bool":true},    
    {"Id":2,"Foo":"foo2","Day":"1978-05-19","Bool":false,"Bar":"bar2"},
    {"Id":3,"Foo":"foo3","Day":"1979-05-19","Bar":"bar3"}
]
 

但是,我不想從伺服器返回整個JSON文字blob,我只想要json或json行的記錄集,其結果如下:

 Rows
-----------------------------------------------------------------------
{"Id":1,"Foo":"foo1","Day":"1977-05-19","Bool":true}
{"Id":2,"Foo":"foo2","Day":"1978-05-19","Bool":false,"Bar":"bar2"}
{"Id":3,"Foo":"foo3","Day":"1979-05-19","Bar":"bar3"}
 

這甚至可能嗎?

我整天都在嘗試做一些微不足道的事情.

  最佳答案

您希望將行序列化為JSON,而不是整個記錄集...

 select [Rows] = (select t.* for json path, without_array_wrapper)
from (values
  (1, 'foo1', cast('1977-05-19' as date), cast(1 as bit) , null),
  (2, 'foo2', cast('1978-05-19' as date), cast(0 as bit), 'bar2'),
  (3, 'foo3', cast('1979-05-19' as date), null, 'bar3')
) t (Id, Foo, Day, Bool, Bar)
 
 Rows
------------------------------------------------------------------
{"Id":1,"Foo":"foo1","Day":"1977-05-19","Bool":true}
{"Id":2,"Foo":"foo2","Day":"1978-05-19","Bool":false,"Bar":"bar2"}
{"Id":3,"Foo":"foo3","Day":"1979-05-19","Bar":"bar3"}
 

  相同標籤的其他問題

sql-servertsql