SQL-Query to get nested JSON Array

Question

I have the following sample data in a MS-SQL database: (Microsoft SQL Server Standard Version 13; Microsoft SQL Server Management Studio 18)

+----------+-----------+-----+--------+---------+---------+
| LastName | Firstname | Age | Weight | Sallery | Married |
+----------+-----------+-----+--------+---------+---------+
| Smith    | Stan      |  58 |     87 |  59.000 | true    |
| Smith    | Maria     |  53 |     57 |  45.000 | true    |
| Brown    | Chris     |  48 |     77 | 159.000 | true    |
| Brown    | Stepahnie |  39 |     67 |  95.000 | true    |
| Brown    | Angela    |  12 |     37 |     0.0 | false   |
+----------+-----------+-----+--------+---------+---------+

I want to get a nested JSON array from it that looks like this:

 [
        {
            "Smith": [
                {
                    "Stan": [
                        {
                            "Age": 58,
                            "Weight": 87,
                            "Sallery": 59.000,
                            "Married": true
                        }
                    ],
                    "Maria": [
                        {
                            "Age": 53,
                            "Weight": 57,
                            "Sallery": 45.000,
                            "Married": true
                        }
                    ]
                }
            ],
            "Brown": [
                {
                    "Chris": [
                        {
                            "Age": 48,
                            "Weight": 77,
                            "Sallery": 159.000,
                            "Married": true
                        }
                    ],
                    "Stepahnie": [
                        {
                            "Age": 39,
                            "Weight": 67,
                            "Sallery": 95.000,
                            "Married": true
                        }
                    ],
                    "Angela": [
                        {
                            "Age": 12,
                            "Weight": 37,
                            "Sallery": 0.0,
                            "Married": false
                        }
                    ]
                }
            ]
        }
    ]

How do I have to build the SQL query?

I have tried different ways but I don't get to dynamize the root or the root keeps repeating itself....

For example, I tried the following query:

I get one Level with:

WITH cte AS
(
       SELECT FirstName
              js = json_query(
              (
                     SELECT Age,
                            Weight,
                            Sallery,
                Married
                             FOR json path,
                            without_array_wrapper ) )
       FROM   Table1)
SELECT '[' + stuff(
       (
                SELECT   '},{"' + FirstName + '":' + '[' + js + ']'
                FROM     cte
                 FOR xml path ('')), 1, 2, '') + '}]'

But I need one more nested level with LastName

Another try:

SELECT
  LastName ,json  
FROM Table1 as a
OUTER APPLY (
  SELECT
    FirstName
  FROM Table1 as b
  WHERE a.LastName = b.LastName
  FOR JSON PATH
) child(json)
FOR JSON PATH

SELECT LastName ,json FROM Table1 as a OUTER APPLY ( SELECT FirstName FROM Table1 as b WHERE a.LastName = b.LastName FOR JSON PATH ) child(json) FOR JSON PATH — Mec-Eng
– Mec-Eng, Commented Jun 3, 2022 at 9:55
That would not be a real-world JSON presentation. Is this a homework question? — AlwaysLearning
– AlwaysLearning, Commented Jun 3, 2022 at 10:24

Charlieface · Accepted Answer · 2022-06-03 15:59:18Z

Unfortunately, SQL Server does not support JSON_AGG nor JSON_OBJECT_AGG, which would have helped here. But we can hack it with STRING_AGG and STRING_ESCAPE

WITH ByFirstName AS
(
    SELECT
      p.LastName,
      p.FirstName,
      json = STRING_AGG(j.json, ',')
    FROM Person p
    CROSS APPLY (
        SELECT
          p.Age,
          p.Weight,
          p.Sallery,
          p.Married
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS j(json)
    GROUP BY
      p.LastName,
      p.FirstName
),
ByLastName AS
(
    SELECT
      p.LastName,
      json = STRING_AGG(CONCAT(
        '"',
        STRING_ESCAPE(p.FirstName, 'json'),
        '":[',
        p.json,
        ']'
      ), ',')
    FROM ByFirstName p
    GROUP BY
      p.LastName
)
SELECT '[{' +
  STRING_AGG(CONCAT(
        '"',
        STRING_ESCAPE(p.LastName, 'json'),
        '":{',
        p.json,
        '}'
  ), ',') + '}]'
FROM ByLastName p

db<>fiddle

This gets you

[
  {
    "Brown": {
      "Angela": [
        {
          "Age": 12,
          "Weight": 37,
          "Sallery": 0,
          "Married": false
        }
      ],
      "Chris": [
        {
          "Age": 48,
          "Weight": 77,
          "Sallery": 159000,
          "Married": true
        }
      ],
      "Stepahnie": [
        {
          "Age": 39,
          "Weight": 67,
          "Sallery": 95000,
          "Married": true
        }
      ]
    },
    "Smith": {
      "Maria": [
        {
          "Age": 53,
          "Weight": 57,
          "Sallery": 45000,
          "Married": true
        }
      ],
      "Stan": [
        {
          "Age": 58,
          "Weight": 87,
          "Sallery": 59000,
          "Married": true
        }
      ]
    }
  }
]

Thank you very much for your answer. It looks good and could be the solution. but unfortunately SQL SERVER 2013 does not know STRING_AGG function yet. I think I can substitute it with stuff.

AlwaysLearning · Accepted Answer · 2022-06-03 11:21:24Z

0

It's certainly possible to get your desired JSON output but, as you can see below, the code is rather convoluted...

/*
 * Data setup...
 */
create table dbo.Person (
  LastName varchar(10),
  FirstName varchar(10),
  Age int,
  Weight int,
  Sallery int,
  Married bit
);

insert dbo.Person (LastName, FirstName, Age, Weight, Sallery, Married)
values
  ('Smith', 'Stan', 58, 87, 59000, 1),
  ('Smith', 'Maria', 53, 57, 45000, 1),
  ('Brown', 'Chris', 48, 77, 159000, 1),
  ('Brown', 'Stepahnie', 39, 67, 95000, 1),
  ('Brown', 'Angela', 12, 37, 0, 0);

/*
 * Example JSON query...
 */
with Persons as (
  select LastName, Stan, Maria, Chris, Stepahnie, Angela
  from (
    select
      LastName,
      FirstName,
      (
        select Age, Weight, Sallery, Married
        for json path
      ) as data
    from dbo.Person
  ) src
  pivot (max(data) for FirstName in (Stan, Maria, Chris, Stepahnie, Angela)) pvt
)
select
  json_query((
    select
      json_query(Stan) as Stan,
      json_query(Maria) as Maria
    from Persons
    where LastName = 'Smith'
    for json path
  )) as Smith,
  json_query((
    select
      json_query(Chris) as Chris,
      json_query(Stepahnie) as Stepahnie,
      json_query(Angela) as Angela
    from Persons
    where LastName = 'Brown'
    for json path
  )) as Brown
for json path;

Which yields the output...

[
    {
        "Smith": [
            {
                "Stan": [
                    {
                        "Age": 58,
                        "Weight": 87,
                        "Sallery": 59000,
                        "Married": true
                    }
                ],
                "Maria": [
                    {
                        "Age": 53,
                        "Weight": 57,
                        "Sallery": 45000,
                        "Married": true
                    }
                ]
            }
        ],
        "Brown": [
            {
                "Chris": [
                    {
                        "Age": 48,
                        "Weight": 77,
                        "Sallery": 159000,
                        "Married": true
                    }
                ],
                "Stepahnie": [
                    {
                        "Age": 39,
                        "Weight": 67,
                        "Sallery": 95000,
                        "Married": true
                    }
                ],
                "Angela": [
                    {
                        "Age": 12,
                        "Weight": 37,
                        "Sallery": 0,
                        "Married": false
                    }
                ]
            }
        ]
    }
]

answered Jun 3, 2022 at 11:21

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

2 Comments

Mec-Eng Over a year ago

Thank you very much for your answer. Sorry, I forgot to say that in reality the table has several thousand data. This means that the ROOT must be dynamic.

Mec-Eng Over a year ago

I get one Level with: WITH cte AS ( SELECT FirstName js = json_query( ( SELECT Age, Weight, Sallery, Married FOR json path, without_array_wrapper ) ) FROM Table1) SELECT '[' + stuff( ( SELECT '},{"' + FirstName + '":' + '[' + js + ']' FROM cte FOR xml path ('')), 1, 2, '') + '}]'

Collectives™ on Stack Overflow

SQL-Query to get nested JSON Array

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related