CODEWITHSUNDEEP
sql-servert-sql
vbilopav
vbilopav

Reputation: 156

How to convert results to a rowset of jsons with T-SQL

I'm having trouble with this query and I'm starting to think that this isn't possible at all with SQL Server.

I have following very simple test query:

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)

And if I want to convert the results to JSON, I simply add for json auto at the end and I have nice JSON results as I expected:

[
    {"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"}
]

However, I don't want to return entire JSON text blob from server, I just want the recordset of json or rows of json, where results would be something like this:

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"}

Is this even possible?

I've lost entire day trying to do something trivial as this.

Upvotes: 1

Views: 195

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

You have got an answer already, but I'd like to add an alternative.

To be honest: My code is not very different to the accepted answer. It just transfers the sub-select into the APPLY. The engine will work this down roughly in the same way probably... It's more a matter of taste actually ;-)

You can use APPLY for any row-wise-action. In this case we can add a column reflecting the entire row as json simply with this:

A mockup-scenario to simulate a table

DECLARE @mockupTable TABLE(Id INT, Foo VARCHAR(100), [Day] DATE, Bool BIT, Bar VARCHAR(100));
INSERT INTO @mockupTable(Id, Foo, Day, Bool, Bar) VALUES 
        (1, 'foo1', '19770519', 1, null),
        (2, 'foo2', '19780519', 0, 'bar2'),
        (3, 'foo3', '19790519', null, 'bar3');

--The query will generate one JSON per row:

SELECT t.*
      ,TheRowAsJson
FROM @mockupTable t
CROSS APPLY (SELECT t.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) A(TheRowAsJson);

The result

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

Upvotes: 0

AlwaysLearning
AlwaysLearning

Reputation: 8809

You're wanting to serialize rows as JSON, not a whole record set...

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"}

Upvotes: 2

Related Questions