Json String to Multiple Rows
-
Greetings,
I have a json string in the following format and I need to split a small portion out to multiple Rows. My json string is shown below. My goal is to split a small part of this into separate rows as shown below. I've been able to get this into a single row but I'm not sure if I can split this as indicated. I am doing this in SQL 2019. Thanks
ID Text
194 Disagree
195 Somewhat DisAgree
196 Somewhat Agree
197 Agree{
"id": "9999",
"position": 1,
"visible": true,
"sorting": null,
"required": {
"text": "Please provide a response.",
"type": "all"
},
"validation": null,
"forced_ranking": true,
"headings": [
{
"heading": "The Resume is Complete."
}
],
"Website": "www.test.com": {
"rows": [
{
"position": 1,
"id": "1941360426"
}
],
"choices": [
{
"position": 1,
"text": "Disagree",
"id": "194",
"description": ""
},
{
"position": 2,
"text": "Somewhat Disagree",
"id": "195",
"description": ""
},
{
"position": 3,
"text": "Somewhat Agree",
"id": "196",
"description": ""
},
{
"position": 4,
"text": "Agree",
"id": "197",
"description": ""
}
]
}
} -
https://www.sqlshack.com/importexport-json-data-using-sql-server-2016/
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses -
The json isn't valid around "Website": "www.test.com": {
declare @json nvarchar(max) = '
{
"id": "9999",
"position": 1,
"visible": true,
"sorting": null,
"required": {
"text": "Please provide a response.",
"type": "all"
},
"validation": null,
"forced_ranking": true,
"headings": [
{
"heading": "The Resume is Complete."
}
],
"Website": {
"rows": [
{
"position": 1,
"id": "1941360426"
}
],
"choices": [
{
"position": 1,
"text": "Disagree",
"id": "194",
"description": ""
},
{
"position": 2,
"text": "Somewhat Disagree",
"id": "195",
"description": ""
},
{
"position": 3,
"text": "Somewhat Agree",
"id": "196",
"description": ""
},
{
"position": 4,
"text": "Agree",
"id": "197",
"description": ""
}
]
}
}
';
select id,text
from openjson(@json ,'$.Website.choices')
with (id int '$.id',
text varchar(100) '$.text');____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
That was it. Thanks for the pointer
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply