CODEWITHSUNDEEP
jsonsql-server-2016
Joe Samraj
Joe Samraj

Reputation: 331

Convert Json from SQL Table

I'm using SQL Server 2016. I want to return the Json in the given format.

Select statement

SELECT Country,[Arm Export],[Food Export] FROM Table

Expected Json Format

[
  {
    "India":{
             "Arm Export": 30,
             "Food Export": 50
            }
  },
  {
   "USA":{
          "Arm Export": 90,
          "Food Export": 5
         }
  },
  {
   "Russia":{
             "Arm Export": 90,
             "Food Export": 5
            }
  }
]

Any help will be appreciated....

Upvotes: 0

Views: 107

Answers (2)

Niels Berglund
Niels Berglund

Reputation: 1698

If you want your output exactly as defined in the question, then Nithin's answer is probably the best.

If the output can be slightly different, you could try FOR JSON PATH, something like so:

SELECT Country, 
  [Arm Export] AS 'Export.Arm Export',
  [Food Export] AS 'Export.Food Export'
  FROM dbo.tb_CountryExport
  FOR JSON PATH;

That would give you an output like so:

[
  {
    "Country": "Russia",
    "Export": {
      "Arm Export": 90,
      "Food Export": 5
    }
  },
  {
    "Country": "India",
    "Export": {
      "Arm Export": 30,
      "Food Export": 50
    }
  },
  {
    "Country": "USA",
    "Export": {
      "Arm Export": 90,
      "Food Export": 5
    }
  }
]

But as I said, that output might not be what you want.

Niels

Upvotes: 0

Nithin
Nithin

Reputation: 1424

Try this...

SELECT'['+stuff(
                (SELECT ',{"'+Country+'":{"Arm Export": '+convert(varchar,[Arm Export])+',"Food Export": '+convert(varchar,[Food Export])+'}}'
                   FROM TABLE
                   FOR XML Path('')),1,1,'')+']'[Detail]

output will be...

[
  {
    "India":{
             "Arm Export": 30,
             "Food Export": 50
            }
  },
  {
   "USA":{
          "Arm Export": 90,
          "Food Export": 5
         }
  },
  {
   "Russia":{
             "Arm Export": 90,
             "Food Export": 5
            }
  }
]

Upvotes: 1

Related Questions