SQL Server query with group by a field error

Question

I have the following table MyTable:

 id │ value_two │ value_three │ value_four 
────┼───────────┼─────────────┼────────────
  1 │ a         │ A           │ AA
  2 │ a         │ A2          │ AA2
  3 │ b         │ A3          │ AA3
  4 │ a         │ A4          │ AA4
  5 │ b         │ A5          │ AA5

I want to query an array of objects { value_three, value_four } grouped by value_two. value_two should be present on its own in the result. The result should look like this:

 value_two │                                                                                    value_four                                                                                 
───────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 a         │ [{"value_three":"A","value_four":"AA"}, {"value_three":"A2","value_four":"AA2"}, {"value_three":"A4","value_four":"AA4"}]
 b         │ [{"value_three":"A3","value_four":"AA3"}, {"value_three":"A5","value_four":"AA5"}]

In postgress I can do this:

SELECT value_two
     , json_agg(row_to_json((value_three, value_four)::foo)) AS value_four
FROM   mytable
GROUP  BY value_two

But in SQL SERVER I get 'json_array_elements' is not a recognized built-in function name.

What is the equivalent to this on SQL SERVER. Can someone help me, please?

Answer 1

If you are using SQL Server 2016+, you may try to use FOR JSON:

Table:

SELECT *
INTO Data
FROM (VALUES
   (1, 'a', 'A',  'AA'),
   (2, 'a', 'A2', 'AA2'),
   (3, 'b', 'A3', 'AA3'),
   (4, 'a', 'A4', 'AA4'),
   (5, 'b', 'A5', 'AA5')
) v (id, value_two, value_three, value_four)

Statement:

SELECT DISTINCT d.value_two, j.value_four
FROM Data d
OUTER APPLY (
   SELECT value_three, value_four
   FROM Data
   WHERE value_two = d.value_two
   FOR JSON AUTO
) j (value_four)

Result:

value_two value_four
a         [{"value_three":"A","value_four":"AA"},{"value_three":"A2","value_four":"AA2"},{"value_three":"A4","value_four":"AA4"}]
b         [{"value_three":"A3","value_four":"AA3"},{"value_three":"A5","value_four":"AA5"}]