How to create a multi-level json output using sql query in SQL Server 2016?

Question

Is that possible create a multi-level json string in SQL Server 2016 ? I have a table (patient_data) like this:

And what i want to do is to create json string output in SQL Server like this:

{
  "patient":[
    {
      "key":"A",
      "data":[
        {
          "name":"Amy Farha",
          "colored":"darkred",
          "avatar_url":"https://s3.amazonaws.com/uifaces/faces/twitter/ladylexy/128.jpg",
          "subtitle":"Vice President",
          "patientid":"qweqweqeqeq"
        },
        {
          "name":"Anies",
          "colored":"darkblue",
          "avatar_url":"https://s3.amazonaws.com/uifaces/faces/twitter/adhamdannaway/128.jpg",
          "subtitle":"Vice Chairman",
          "patientid":"avasdasdad"
        }
      ]
    },
    {
      "key":"B",
      "data":[
        {
          "name":"Bryan Adams",
          "colored":"darkgreen",
          "avatar_url":"https://randomuser.me/api/portraits/med/women/91.jpg",
          "subtitle":"Reggae Man",
          "patientid":"avasdasdad"
        }
      ]
    },
      {
        "key":"D",
        "data":[
          {
            "name":"David dummy",
            "colored":"darkgreen",
            "avatar_url":"https://randomuser.me/api/portraits/med/women/91.jpg",
            "subtitle":"Reggae Man",
            "patientid":"avasdasdad"
          }
        ]
      },
      {
        "key":"M",
        "data":[
          {
            "name":"Muhammad Adams",
            "colored":"darkgreen",
            "avatar_url":"https://randomuser.me/api/portraits/med/women/91.jpg",
            "subtitle":"Reggae Man",
            "patientid":"avasdasdad"
          }
        ]
      },
      {
        "key":"T",
        "data":[
          {
            "name":"Tere",
            "colored":"darkgreen",
            "avatar_url":"https://randomuser.me/api/portraits/med/women/91.jpg",
            "subtitle":"Reggae Man",
            "patientid":"avasdasdad"
          },
          {
            "name":"Tifanny",
            "colored":"darkblue",
            "avatar_url":"https://s3.amazonaws.com/uifaces/faces/twitter/adhamdannaway/128.jpg",
            "subtitle":"Vice Chairman",
            "patientid":"avasdasdad"
          }
        ]
      },
      {
        "key":"X",
        "data":[
          {
            "name":"Xavier",
            "colored":"darkgreen",
            "avatar_url":"https://randomuser.me/api/portraits/med/women/91.jpg",
            "subtitle":"Reggae Man",
            "patientid":"avasdasdad"
          }
        ]
      }
  ]
}

**NOTE "key" is a grouping by the first letter of patient's name

I try use json path but couldn't figure out in multi level case .I hope someone can help me

Sergey Menshov · Accepted Answer · 2018-04-13 07:10:12Z

Try the following queries with FOR JSON PATH

-- test data
CREATE TABLE Patients(
  id int,
  name varchar(100)
)

INSERT Patients(id,name)VALUES
(11,'A Patient 11'),(12,'A Patient 12'),
(21,'B Patient 21'),
(31,'C Patient 31'),(32,'C Patient 32')

-- query 1
SELECT
  (
    SELECT
      k.[key],
      (SELECT p.id,p.[name] FROM Patients p WHERE LEFT(p.[name],1)=k.[key] FOR JSON PATH) [data]
    FROM
      (
        SELECT DISTINCT LEFT([name],1) [key]
        FROM Patients
      ) k
    ORDER BY k.[key]
    FOR JSON PATH
  ) patient
FOR JSON PATH

/*
[
    {"patient":[
            {"key":"A","data":[{"id":11,"name":"A Patient 11"},{"id":12,"name":"A Patient 12"}]},
            {"key":"B","data":[{"id":21,"name":"B Patient 21"}]},
            {"key":"C","data":[{"id":31,"name":"C Patient 31"},{"id":32,"name":"C Patient 32"}]}
        ]
    }
]
*/

-- query 2
SELECT
  k.[key] [patient.key],
  (SELECT p.id,p.[name] FROM Patients p WHERE LEFT(p.[name],1)=k.[key] FOR JSON PATH) [patient.data]
FROM
  (
    SELECT DISTINCT LEFT([name],1) [key]
    FROM Patients
  ) k
ORDER BY k.[key]
FOR JSON PATH    

/*
[
    {"patient":{"key":"A","data":[{"id":11,"name":"A Patient 11"},{"id":12,"name":"A Patient 12"}]}},
    {"patient":{"key":"B","data":[{"id":21,"name":"B Patient 21"}]}},
    {"patient":{"key":"C","data":[{"id":31,"name":"C Patient 31"},{"id":32,"name":"C Patient 32"}]}}
]
*/

You can append WITHOUT_ARRAY_WRAPPER to remove the brackets [ and ]

SELECT
  (
    SELECT
      k.[key],
      (SELECT p.id,p.[name] FROM Patients p WHERE LEFT(p.[name],1)=k.[key] FOR JSON PATH) [data]
    FROM
      (
        SELECT DISTINCT LEFT([name],1) [key]
        FROM Patients
      ) k
    ORDER BY k.[key]
    FOR JSON PATH
  ) patient
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

/*
{
  "patient":[
          {"key":"A","data":[{"id":11,"name":"A Patient 11"},{"id":12,"name":"A Patient 12"}]},
          {"key":"B","data":[{"id":21,"name":"B Patient 21"}]},
          {"key":"C","data":[{"id":31,"name":"C Patient 31"},{"id":32,"name":"C Patient 32"}]}
      ]
}
*/

I found another more shorter variant. You can use ROOT('patient') option here

SELECT
  k.[key] [key],
  (SELECT p.id,p.[name] FROM Patients p WHERE LEFT(p.[name],1)=k.[key] FOR JSON PATH) [data]
FROM
  (
    SELECT DISTINCT LEFT([name],1) [key]
    FROM Patients
  ) k
ORDER BY k.[key]
FOR JSON PATH, ROOT('patient')

/*
{
  "patient":[
          {"key":"A","data":[{"id":11,"name":"A Patient 11"},{"id":12,"name":"A Patient 12"}]},
          {"key":"B","data":[{"id":21,"name":"B Patient 21"}]},
          {"key":"C","data":[{"id":31,"name":"C Patient 31"},{"id":32,"name":"C Patient 32"}]}
      ]
}
*/

Collectives™ on Stack Overflow

How to create a multi-level json output using sql query in SQL Server 2016?

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