How to convert multiple table output as child json in Sql

Question

I have been trying to create a json object in sql from three tables

The main table/entity is "order" that have a id as addressId

and further address table has countryId and stateId which refers to

state table and country table

orderTable

    Id  AddressId   Status
    1     1         Processed

AddressTable

Id countryId  stateId FirstName  LastName
1    5            7     John       cena


countryTable

Id   Name
5     usa


StateTable

Id   Name
7     DC

The output should show as below

{
  "firstName": "John",
  "lastName": "cena",
  "Country" : {
                 "name": "usa",
                 "id" : "5"
              },
  "States" :  {
                    "name": "DC",
                    "id" : "7"
            }

}

I have tried using this query and get similar results but I want to remove [] [] array object containers from json

[      // I want to remove this 
{

  "FirstName": "Steve",

  "LastName": "Gates",



     "country": 
 [       // I want to remove this 
      {

                  "name": "usa",
                 "id" : "5"
              }
       ],  // I want to remove this 

"states" :
         [  // I want to remove this 
           {
                    "name": "DC",
                    "id" : "7"
            }


     ]  // I want to remove this 

As per microsoft's blog we can use

FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

but if I use this , then it does not include country and state as separate child objects so I have used "FOR JSON AUTO" which gives me desired output but it additionally adds square bractes to each json object

This is my query

 Select ord.*, (Select *  from Address ad Left outer join Country country on country.Id = ad.CountryId
 Left outer join State sp on sp.Id = ad.StateId  where ad.Id = ord.AddressId  FOR JSON AUTO) as AddressJson
 , (Select *  from Address ad Left outer join Country country on country.Id = ad.CountryId
 Left outer join State sp on sp.Id = ad.StateId  where ad.Id = ord.AddressId1  FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) as AddressJson2
 from [order] ord ) 

Answer 1

I don't see a need to use all the sub-queries in your example. I'd simply just use joins and dot-separated alias to get your desired output.

Format Query Results as JSON with FOR JSON

In PATH mode, you can use the dot syntax - for example, 'Item.UnitPrice' - to format nested output.

Then add the WITHOUT_ARRAY_WRAPPER option to remove the brackets.

Here's a working example:

DECLARE @AddressTable TABLE
    (
        [Id] INT
      , [countryId] INT
      , [stateId] INT
      , [FirstName] NVARCHAR(100)
      , [LastName] NVARCHAR(100)
    );

DECLARE @countryTable TABLE
    (
        [Id] INT
      , [name] NVARCHAR(100)
    );

DECLARE @stateTable TABLE
    (
        [Id] INT
      , [name] NVARCHAR(100)
    );

INSERT INTO @AddressTable (
                              [Id]
                            , [countryId]
                            , [stateId]
                            , [FirstName]
                            , [LastName]
                          )
VALUES ( 1, 5, 7, N'John', N'cena' );

INSERT INTO @countryTable (
                              [Id]
                            , [name]
                          )
VALUES ( 5, N'usa' );

INSERT INTO @stateTable (
                            [Id]
                          , [name]
                        )
VALUES ( 7, N'DC' );

SELECT     [a].[FirstName]
         , [a].[LastName]
         , [ct].[name] AS 'Country.name' --dot-separated alias
         , [ct].[Id] AS 'Country.Id'
         , [st].[name] AS 'States.name'
         , [st].[Id] AS 'States.Id'
FROM       @AddressTable [a]
INNER JOIN @stateTable [st]
    ON [st].[Id] = [a].[stateId]
INNER JOIN @countryTable [ct]
    ON [ct].[Id] = [a].[countryId]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Which gives you the following output:

{
  "FirstName": "John",
  "LastName": "cena",
  "Country": {
    "name": "usa",
    "Id": 5
  },
  "States": {
    "name": "DC",
    "Id": 7
  }
}