SQL Server return JSON array of ids for rows that match

Question

I want to be able to return a JSON array of ids as part of a JSON object from SQL Server.

I have tried to get this to work and have come close but it is not the way I want the JSON to be structered.

This SQL I have outputs this

SELECT c.Pk_Company_Id AS [id],
       c.Name AS [name],
       r.Pk_Rig_Id AS [rigId]
FROM Company c
LEFT JOIN Rig r
ON c.Pk_Company_Id = r.Fk_Company_Id
FOR JSON PATH, ROOT('companies')

{"companies":[{"id":1,"name":"Company 1","rigId":100},{"id":1,"name":"Company 1","rigId":101},{"id":2,"name":"Company 2"}]}

This produces JSON that has everything I want kind of but I would like to structure rigIds as a JSON Array to produce something like this so i am not duplicating an object for no reason.

{"companies":[{"id":1,"name":"Company 1","rigIds":[100, 101]},{"id":2,"name":"Company 2"}]}

Sergey Menshov · Accepted Answer · 2019-04-25 04:25:10Z

2

A variant with REPLACE:

SELECT
  c.Pk_Company_Id AS [id],
  c.Name AS [name],
  JSON_QUERY(REPLACE(REPLACE((SELECT r.Pk_Rig_Id id FROM Rig r WHERE c.Pk_Company_Id = r.Fk_Company_Id FOR JSON PATH),'{"id":',''),'}','')) [rigIds]
FROM Company c
FOR JSON PATH, ROOT('companies')

-- {"companies":[{"id":1,"name":"Company 1","rigIds":[100,101]},{"id":2,"name":"Company 2"}]}

A variant with STRING_AGG:

SELECT
  c.Pk_Company_Id AS [id],
  c.Name AS [name],
  JSON_QUERY((SELECT '['+STRING_AGG(r.Pk_Rig_Id,',')+']' FROM Rig r WHERE c.Pk_Company_Id = r.Fk_Company_Id)) [rigIds]
FROM Company c
FOR JSON PATH, ROOT('companies')

edited Apr 25, 2019 at 4:25

answered Apr 25, 2019 at 3:43

Sergey Menshov

3,9062 gold badges11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Greg Stewart Over a year ago

Thank you, both work! I like the STRING_AGG solution.

Sergey Menshov Over a year ago

Good luck! I also like the solution with STRING_AGG better. ))

Collectives™ on Stack Overflow

SQL Server return JSON array of ids for rows that match

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related