Parsing Json Document in SQL producing nulls
-
Greetings,
I have the following Json document saved to a SQL table and am having issues parsing it as required. When I run isjson it is validated. I need to parse the data piece and get the data to appear with the 4 columns as shown below. When I try openjson with no schema, I get 5 keys as shown below. I've tried a number of different schema examples such as
Select * from openjson(@json)with (id int '$.id', title varchar(200) '$.title', nickname varchar(200) '$.nickname', href varchar(200) '$.href' ) but it produces nulls. Any assistance would be appreciated.
key value type
data json string 4
per_page 50 2
page 1 2
total 406 2
links website 5ID Title Nickname Href
123 webinar1 website1
124 webinar2 website 2{"data": [{"id": "123", "title": "Webinar1", "nickname": "", "href": "website1"}, {"id": "124", "title": "Webinar2", "nickname": "", "href": "website2"},.......
], "per_page": 50, "page": 1, "total": 406, "links": {"self": "https://api.surveymonkey.net/v3/surveys/?page=1&per_page=50", "next": "https://api.surveymonkey.net/v3/surveys/?page=2&per_page=50", "last": "https://api.surveymonkey.net/v3/surveys/?page=9&per_page=50"}} -
This should work for you
Select b.id, b.title, b.nickname, b.href
from openjson(@json)
with ([data] nvarchar(max) '$.data' as json) a
cross apply openjson(a.[data])
with (id int '$.id',
title varchar(200) '$.title',
nickname varchar(200) '$.nickname',
href varchar(200) '$.href' ) b;____________________________________________________
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
-
drop table if exists dbo.test_j;
go
create table dbo.test_j(
[data] nvarchar(max));
go
insert dbo.test_j([data]) values('{"data": [{"id": "123", "title": "Webinar1", "nickname": "", "href": "website1"}, {"id": "124", "title": "Webinar2", "nickname": "", "href": "website2"}], "per_page": 50, "page": 1, "total": 406, "links": {"self": "https://api.surveymonkey.net/v3/surveys/?page=1&per_page=50", "next": "https://api.surveymonkey.net/v3/surveys/?page=2&per_page=50", "last": "https://api.surveymonkey.net/v3/surveys/?page=9&per_page=50"}}');
/* json fields same as SQL column names */
select j_open.*
from
dbo.test_j j
cross apply
openjson(j.[data], '$.data') with (id int,
title varchar(200),
nickname varchar(200),
href varchar(200)) j_open;
/* json field 'nickname' target column 'some_name' */
select j_open.*
from
dbo.test_j j
cross apply
openjson(j.[data], '$.data') with (id int,
title varchar(200),
some_name varchar(200) '$.nickname',
href varchar(200)) j_open;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Thanks everyone for their response. I was able to get it going with your help. The first answer had issues as I was using reserved words evidently for my column titles. After fixing that, I was still getting issues that the json was improperly formatted, incorrect character n at position 1. I thought it might be a case sensitivity issues with json but I didn't see any mismatched cases.
The answer from scdecade worked right from the hop. I appreciate you showing me how to rename columns as well. Thanks again
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply