JSON from SQL query with child level

Question

I have a simple query in my database:

SELECT id, name FROM users FOR JSON AUTO, ROOT('users')

This returns the following JSON:

{
    "users": [
        {"id": "1", "name": "John"}
        {"id": "2", "name": "Mike"}
    ]
}

I want to have the return with the following format:

{
    "users": {
        "list": [
            {"id": "1", "name": "John"}
            {"id": "2", "name": "Mike"}
        ]
    }
}

Can I do this on SQL level by simply changing the query?

Zhorov · Accepted Answer · 2019-10-17 20:07:53Z

5

You may try with this:

Table:

CREATE TABLE users (
   id varchar(1),
   [name] varchar(50)
)
INSERT INTO users
   (id, [name])
VALUES
   ('1', 'John'),
   ('2', 'Mike')

Statement:

SELECT users = (SELECT id, name FROM users FOR JSON AUTO, ROOT('list'))
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Result:

{"users":{"list":[{"id":"1","name":"John"},{"id":"2","name":"Mike"}]}}

answered Oct 17, 2019 at 20:07

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

1 Answer 1