Combining two tables result into JSON - SQL server

Question

I am having two tables STUDENT and MASTER as follows

CREATE TABLE student(sid int,sname varchar(10),saddress varchar(30))

CREATE TABLE master(mid int,mname varchar(10),maddress varchar(30))

Now I want the JSON Structure as

{
    "student": [
        {
            "sid": "value",
            "sname": "value",
            "saddress": "value"
        }
    ],
    "master": [
        {
            "mid": "value",
            "mname": "value",
            "maddress": "value"
        }
    ]
}

In short I can say:

{
"table1":[],
"table2":[]
}

I have tried this but result is not as expected


    SELECT * FROM student,master FOR JSON AUTO

Can any one help me to form the required JSON Structure from the query ??

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jul 6, 2021 at 16:41

Thom A · Accepted Answer · 2021-07-06 16:43:46Z

4

Couple ideas:

SELECT student,
       [master] --This is a poor choice for an object, as master is the name of a system database
FROM (VALUES((SELECT * FROM dbo.student FOR JSON AUTO),
             (SELECT * FROM dbo.[master] FOR JSON AUTO)))V(student,[master])
FOR JSON AUTO;

SELECT (SELECT *
        FROM dbo.student
        FOR JSON AUTO) AS student,
       (SELECT *
        FROM dbo.[master] --This is a poor choice for an object, as master is the name of a system database
        FOR JSON AUTO) AS [master]
FOR JSON PATH;

answered Jul 6, 2021 at 16:43

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

Combining two tables result into JSON - SQL server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related