{"id":37,"date":"2025-04-30T09:29:48","date_gmt":"2025-04-30T13:29:48","guid":{"rendered":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/?p=37"},"modified":"2025-04-30T12:32:02","modified_gmt":"2025-04-30T16:32:02","slug":"get-sql-table-as-json","status":"publish","type":"post","link":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/2025\/04\/30\/get-sql-table-as-json\/","title":{"rendered":"Generate JSON Data from SQL with FOR JSON AUTO"},"content":{"rendered":"<p>So I was recently working on a test project and I needed some JSON data. Instead of hooking my application to the SQL Server environment, I created the data from the tables that already existed in SQL.<br \/>\nUsing SSMS, I simply appended the following command to my SELECT<\/p>\n<pre><code>\nFOR JSON AUTO\n<\/code><\/pre>\n<p>For example,<\/p>\n<p><code><br \/>\nSELECT TOP (1000) [VehicleId]<br \/>\n,[Make]<br \/>\n,[Model]<br \/>\n,[Year]<br \/>\n,[Color]<br \/>\n,[Available]<br \/>\n,[CustomerId]<br \/>\n,[Miles]<br \/>\n,[InvoiceId]<br \/>\n,[SalespersonId]<br \/>\nFROM [StithAutoGroupDb].[dbo].[Vehicles]<br \/>\nFOR JSON AUTO<\/code><\/p>\n<p>The result<\/p>\n<div>\n<pre>JSON_F52E2B61-18A1-11d1-B105-00805F49916B\u2028[\n\n\u00a0 \u00a0 {\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"VehicleId\": 1,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Make\": \"Toyota\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Model\": \"Camry\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Year\": 2021,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Color\": \"Blue\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Available\": true,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"CustomerId\": 3,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Miles\": \"\"\n\n\u00a0 \u00a0 },\n\n\u00a0 \u00a0 {\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"VehicleId\": 2,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Make\": \"Honda\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Model\": \"Civic\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Year\": 2020,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Color\": \"Black\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Available\": false,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"CustomerId\": 1,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Miles\": \"55826\"\n\n\u00a0 \u00a0 },\n\n\u00a0 \u00a0 {\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"VehicleId\": 3,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Make\": \"Ford\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Model\": \"F-150\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Year\": 2022,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Color\": \"Red\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Available\": true,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"CustomerId\": 4,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Miles\": \"\"\n\n\u00a0 \u00a0 },\n\n\u00a0 \u00a0 {\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"VehicleId\": 4,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Make\": \"Chevrolet\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Model\": \"Malibu\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Year\": 2019,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Color\": \"White\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Available\": false,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"CustomerId\": 2,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Miles\": \"\"\n\n\u00a0 \u00a0 },\n\n\u00a0 \u00a0 {\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"VehicleId\": 5,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Make\": \"Tesla\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Model\": \"Model 3\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Year\": 2023,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Color\": \"Silver\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Available\": true,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"CustomerId\": 5,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Miles\": \"\"\n\n\u00a0 \u00a0 },\n\n\u00a0 \u00a0 {\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"VehicleId\": 6,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Make\": \"BMW\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Model\": \"iX M70\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Year\": 2025,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Color\": \"Black\",\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Available\": true,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"CustomerId\": 1,\n\n\u00a0 \u00a0 \u00a0 \u00a0 \"Miles\": \"\"\n\n\u00a0 \u00a0 }\n\n]<\/pre>\n<\/div>\n<pre><code>\n<\/code><\/pre>\n<p>That&#8217;s it for now. Hope this helps you in your coding journey. Until next time!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>So I was recently working on a test project and I needed some JSON data. Instead of hooking my application to the SQL Server environment, I created the data from the tables that already existed in SQL. Using SSMS, I simply appended the following command to my SELECT FOR JSON AUTO For example, SELECT TOP [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":40,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[6,13,15,14],"tags":[],"class_list":["post-37","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programming","category-sql","category-sql-server-management-studio","category-ssms"],"jetpack_featured_media_url":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/wp-content\/uploads\/2025\/04\/IMG_3349.jpg.webp","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/wp-json\/wp\/v2\/posts\/37","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/wp-json\/wp\/v2\/comments?post=37"}],"version-history":[{"count":9,"href":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/wp-json\/wp\/v2\/posts\/37\/revisions"}],"predecessor-version":[{"id":51,"href":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/wp-json\/wp\/v2\/posts\/37\/revisions\/51"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/wp-json\/wp\/v2\/media\/40"}],"wp:attachment":[{"href":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/wp-json\/wp\/v2\/media?parent=37"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/wp-json\/wp\/v2\/categories?post=37"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ceejaysmedia.com\/dotCeeJayS\/wp-json\/wp\/v2\/tags?post=37"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}