How to Parse JSON Data from a Table into Tabular Format
In modern applications, data is often stored or exchanged in JSON (JavaScript Object Notation) format. While JSON is excellent for data transfer, it is not always easy to read or analyze directly. Converting JSON data stored in a table into a tabular format makes the information more structured, readable, and useful for reporting and visualization.
Let's understand
I have a table with having single column with JSON data.
Create Table #temp
(
JSON_Data Nvarchar(Max)
)
Now Insert raw JSON for Testing purpose only
Insert into #temp values('[
{
"multimediaTypeId": 3,
"newsReleaseId": 98201,
"uploadedFile": "https://www.test.com/Uploads/images/a1c2d3e4-1111-4b9a-8f21-abc123456789.jpg",
"youtubeScript": "",
"captionName": "DigitalWave Wins Martech Excellence Award 2025 for Data-Driven SEO Campaign",
"summary": "<p>DigitalWave, a fast-growing digital marketing agency, has won the
<b>Martech Excellence Award 2025</b> for its data-driven SEO campaign delivered for
<b>FinGrow Solutions</b>. The campaign significantly improved organic visibility and conversions.
</p>\n",
"summarysort": "DigitalWave has won the Martech Excellence Award 2025 for its data-driven SEO
campaign
for FinGrow Solutions, delivering strong growth in organic visibility and conversions...",
"newsURL": "https://www.test.com/digitalwave-wins-martech-excellence-award-2025-for-data-driven-seo-
campaign-98201.html",
"dateAndTime": "02/05/2025 11:30:00 AM"
},
{
"multimediaTypeId": 2,
"newsReleaseId": 98215,
"uploadedFile": "https://www.test.com/Uploads/images/b2d4e6f8-2222-4c8b-9a33-def987654321.jpg",
"youtubeScript": "",
"captionName": "InnoTech Secures Silver at National IT Awards 2025 for Cloud Innovation",
"summary": "<p>InnoTech, a technology services company, has been recognised at the <b>
National IT Awards 2025</b> with a <b>Silver Award</b> for its innovative cloud migration solution
implemented for
<b>RetailMax</b>.</p>\n",
"summarysort": "InnoTech received Silver at the National IT Awards 2025 for its cloud migration
solution for RetailMax,
showcasing innovation and scalability...",
"newsURL": "https://www.test.com/innotech-secures-silver-at-national-it-awards-2025-for-
cloud-innovation-98215.html",
"dateAndTime": "02/12/2025 09:45:00 AM"
},
{
"multimediaTypeId": 3,
"newsReleaseId": 98232,
"uploadedFile": "https://test.com/Uploads/mages/c3e5g7h9-3333-4d7c-8b44-ghi456789012.jpg",
"youtubeScript": "",
"captionName": "MarketEdge Honoured at Brand Leadership Awards 2025 for Content Marketing
Excellence",
"summary": "<p>MarketEdge, a brand strategy and content marketing firm, has been honoured at the
<b>Brand Leadership Awards 2025</b> for delivering high-impact content marketing campaigns for
<b>UrbanStyle</b>, driving strong brand engagement.</p>\n",
"summarysort": "MarketEdge was honoured at the Brand Leadership Awards 2025 for its content
marketing excellence for UrbanStyle, boosting brand engagement and visibility...",
"newsURL": "https://www.test.com/marketedge-honoured-at-brand-leadership-awards-2025-for-
content-marketing-excellence-98232.html",
"dateAndTime": "03/03/2025 02:10:00 PM"
},
{
"multimediaTypeId": 1,
"newsReleaseId": 98247,
"uploadedFile": "https://test.com//images/d4f6h8j0-4444-4e6d-9c55-jkl890123456.jpg",
"youtubeScript": "",
"captionName": "FinSecure Receives Recognition at FinTech Awards 2025 for Cybersecurity Platform",
"summary": "<p>FinSecure, a fintech cybersecurity company, has received recognition at the
<b>FinTech Awards 2025</b> for its advanced fraud detection and cybersecurity platform designed for
<b>NextGen Bank</b>.</p>\n",
"summarysort": "FinSecure received recognition at the FinTech Awards 2025 for its cybersecurity
platform for NextGen Bank,
strengthening digital transaction security...",
"newsURL": "https://test.com/finsecure-receives-recognition-at-fintech-awards-2025-for-
cybersecurity-platform-98247.html",
"dateAndTime": "03/18/2025 04:20:00 PM"
},
{
"multimediaTypeId": 3,
"newsReleaseId": 98263,
"uploadedFile": "https://test.com/Uploads/images/e5g7i9k1-5555-4f5e-8d66-mno345678901.jpg",
"youtubeScript": "",
"captionName": "GreenTech Solutions Wins Sustainability Award 2025 for Renewable Energy Initiative",
"summary": "<p>GreenTech Solutions has won the <b>Sustainability Award 2025</b> for its renewable
energy
initiative that helped <b>EcoPower Industries</b> reduce its carbon footprint and improve energy
efficiency.</p>\n",
"summarysort": "GreenTech Solutions won the Sustainability Award 2025 for its renewable energy
initiative
for EcoPower Industries, supporting environmental responsibility...",
"newsURL": "https://www.test.com/greentech-solutions-wins-sustainability-award-2025-for-renewable
-energy-initiative-98263.html",
"dateAndTime": "04/01/2025 12:00:00 PM"
}
]
')
Now I have data inside table in single string format as JSON.
Now try to parse above stored JSON into tabular data. for this we user OpenJSON
Select t.MediaId,t.BlogId,t.uploadedFile,t.youtubeScript,t.captionName,t.summary
from #temp
Outer Apply OpenJSON(JSON_data)
With(
MediaId int '$.multimediaTypeId',
BlogId Int '$.newsReleaseId',
uploadedFile varchar(100) '$.uploadedFile',
youtubeScript nvarchar(Max) '$.youtubeScript',
captionName nvarchar(Max) '$.captionName',
summary nvarchar(Max) '$.summary',
summarysort nvarchar(Max) '$.summarysort',
newsURL nvarchar(Max) '$.newsURL',
dateAndTime nvarchar(50) '$.dateAndTime'
)t
When you run above SQL query then you got the tabular data .
š Thank You for Reading!
Thank you for taking the time to read this blog!
If you have any questions or need help with something, feel free to drop a message in the comments or contact section. Iāll get back to you as soon as possible.
Happy Learning! š