Converting nested Json document to SQL data

Question

Post reply

Converting nested Json document to SQL data

mandavli

SSCrazy

Points: 2122
More actions
February 7, 2023 at 1:47 pm

#4146530

I have response from a API that look like
{
"responseCode": 200,
"message": "success",
"content": [
{
"id": "1234578",
"form_id": "9v5648148",
"created_at": "2023-01-31 14:54:37",
"status": "ACTIVE",
"answers": {
"1": {
"name": "name",
"order": "2",
"sublabels": "{\"prefix\":\"Prefix\",\"first\":\"First Name\",\"middle\":\"Middle Name\",\"last\":\"Last Name\",\"suffix\":\"Suffix\"}",
"text": "Name",
"type": "control_fullname",
"answer": {
"first": "First",
"last": "LastTest"
},
"prettyFormat": "First LastTest"
},
"2": {
"name": "10Digit1234",
"order": "3",
"text": "Enter Control number",
"type": "control_textbox",
"answer": "1234567809"
}
},
{
"id": "5510025870127903",
"form_id": "9v56481481",
"created_at": "2023-01-31 14:29:47",
"status": "ACTIVE",
"updated_at": null,
"answers": {
"1": {
"name": "name",
"order": "2",
"sublabels": "{\"prefix\":\"Prefix\",\"first\":\"First Name\",\"middle\":\"Middle Name\",\"last\":\"Last Name\",\"suffix\":\"Suffix\"}",
"text": "Name",
"type": "control_fullname",
"answer": {
"first": "Test1",
"last": "Test2"
},
"prettyFormat": "Test Test2"
},
"2": {
"name": "10Digit1234",
"order": "3",
"text": "Enter Control number",
"type": "control_textbox",
"answer": "11111111"
}
}
],
"duration": "235.75ms",
"resultSet": {
"offset": 0,
"limit": 20,
"count": 20
},
"limit-left": 9990
}

how do i convert this to SQL table data..
February 8, 2023 at 9:17 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80342 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

aitorzur@hotmail.com SSC Rookie Points: 32 More actions · Answer 2

Look at the OPENJSON (Transact-SQL) function.

https://www.sqlservercentral.com/forums/topic/converting-nested-json-document-to-sql-data

OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns.

Converting nested Json document to SQL data

Cookies on SQLServerCentral