July 11, 2016 at 2:19 am
Morning everyone.
I am trying to extract multiple authors from the google books api however when I try to return the array I only return one. On other api's I dont have the problem could it be because of the line feed?
The api returns:
{
"kind": "books#volumes",
"totalItems": 1,
"items": [
{
"kind": "books#volume",
"id": "_lMPDAAAQBAJ",
"etag": "+WL3c0a7Lfw",
"selfLink": "https://www.googleapis.com/books/v1/volumes/_lMPDAAAQBAJ",
"volumeInfo": {
"title": "Superforecasting",
"subtitle": "The Art and Science of Prediction",
"authors": [
"Philip Tetlock",
"Dan Gardner"
],
"publisher": "Random House",
"publishedDate": "2016-04-07",
"description": "What if we could improve our ability to predict the future? Everything we do involves forecasts about how the future will unfold. Whether buying a new house or changing job, designing a new product or getting married, our decisions are governed by implicit predictions of how things are likely to turn out. The problem is, we're not very good at it. In a landmark, twenty-year study, Wharton professor Philip Tetlock showed that the average expert was only slightly better at predicting the future than a layperson using random guesswork. Tetlock's latest project âe\" an unprecedented, government-funded forecasting tournament involving over a million individual predictions âe\" has since shown that there are, however, some people with real, demonstrable foresight. These are ordinary people, from former ballroom dancers to retired computer programmers, who have an extraordinary ability to predict the future with a degree of accuracy 60% greater than average. They are superforecasters. In Superforecasting, Tetlock and his co-author Dan Gardner offer a fascinating insight into what we can learn from this elite group. They show the methods used by these superforecasters which enable them to outperform even professional intelligence analysts with access to classified data. And they offer practical advice on how we can all use these methods for our own benefit âe\" whether in business, in international affairs, or in everyday life.",
"industryIdentifiers": [
{
"type": "ISBN_13",
"identifier": "9781847947154"
},
{
"type": "ISBN_10",
"identifier": "1847947158"
}
],
"readingModes": {
"text": false,
"image": false
},
"pageCount": 352,
"printType": "BOOK",
"averageRating": 4.0,
"ratingsCount": 36,
"maturityRating": "NOT_MATURE",
"allowAnonLogging": false,
"contentVersion": "preview-1.0.0",
"imageLinks": {
"smallThumbnail": "http://books.google.co.uk/books/content?id=_lMPDAAAQBAJ&printsec=frontcover&img=1&zoom=5&edge=curl&source=gbs_api",
"thumbnail": "http://books.google.co.uk/books/content?id=_lMPDAAAQBAJ&printsec=frontcover&img=1&zoom=1&edge=curl&source=gbs_api"
},
"language": "en",
"previewLink": "http://books.google.co.uk/books?id=_lMPDAAAQBAJ&printsec=frontcover&dq=isbn:1847947158&hl=&cd=1&source=gbs_api",
"infoLink": "http://books.google.co.uk/books?id=_lMPDAAAQBAJ&dq=isbn:1847947158&hl=&source=gbs_api",
"canonicalVolumeLink": "http://books.google.co.uk/books/about/Superforecasting.html?hl=&id=_lMPDAAAQBAJ"
},
"saleInfo": {
"country": "GB",
"saleability": "NOT_FOR_SALE",
"isEbook": false
},
"accessInfo": {
"country": "GB",
"viewability": "PARTIAL",
"embeddable": true,
"publicDomain": false,
"textToSpeechPermission": "ALLOWED_FOR_ACCESSIBILITY",
"epub": {
"isAvailable": false
},
"pdf": {
"isAvailable": false
},
"webReaderLink": "http://books.google.co.uk/books/reader?id=_lMPDAAAQBAJ&hl=&printsec=frontcover&output=reader&source=gbs_api",
"accessViewStatus": "SAMPLE",
"quoteSharingAllowed": false
},
"searchInfo": {
"textSnippet": "These are ordinary people, from former ballroom dancers to retired computer programmers, who have an extraordinary ability to predict the future with a degree of accuracy 60% greater than average. They are superforecasters."
}
}
]
}
I am using the following SQL
SET @returnedCitation = REPLACE(REPLACE(@returnedCitation , CHAR(13), ''), CHAR(10), '')
SELECT
1 as ID, * FROM OPENJSON (JSON_QUERY(@returnedCitation, '$.items[0].volumeInfo.authors'))
SELECT *
FROM OPENJSON(JSON_QUERY(@returnedCitation, '$.items'));
Neither of these return more than Philip Tetlock
Any help on this would be greatly appreciated.
Many Thanks,
Oliver
July 11, 2016 at 2:26 am
some progress, if I only take a portion of the json it works fine?!? only problem is that I cannot take just a portion
declare @json varchar(max)
SET @json = '{
"items": [
{
"kind": "books#volume",
"id": "_lMPDAAAQBAJ",
"etag": "+WL3c0a7Lfw",
"selfLink": "https://www.googleapis.com/books/v1/volumes/_lMPDAAAQBAJ",
"volumeInfo": {
"title": "Superforecasting",
"subtitle": "The Art and Science of Prediction",
"authors": [
"Philip Tetlock",
"Dan Gardner"
]}
}
]
}';
SELECT *
FROM OPENJSON(JSON_QUERY(@json, '$.items[0].volumeInfo.authors'));
returns
keyvaluetype
0Philip Tetlock1
1Dan Gardner1
July 11, 2016 at 2:55 am
Quick suggestion, simply use OPENJSON with the path name, no need to use JSON_QUERY
😎
USE TEEST;
GO
SET NOCOUNT ON;
--
DECLARE @returnedCitation NVARCHAR(MAX) = N'{
"kind": "books#volumes",
"totalItems": 1,
"items": [
{
"kind": "books#volume",
"id": "_lMPDAAAQBAJ",
"etag": "+WL3c0a7Lfw",
"selfLink": "https://www.googleapis.com/books/v1/volumes/_lMPDAAAQBAJ",
"volumeInfo": {
"title": "Superforecasting",
"subtitle": "The Art and Science of Prediction",
"authors": [
"Philip Tetlock",
"Dan Gardner"
],
"publisher": "Random House",
"publishedDate": "2016-04-07",
"description": "What if we could improve our ability to predict the future? Everything we do involves forecasts about how the future will unfold. Whether buying a new house or changing job, designing a new product or getting married, our decisions are governed by implicit predictions of how things are likely to turn out. The problem is, we''re not very good at it. In a landmark, twenty-year study, Wharton professor Philip Tetlock showed that the average expert was only slightly better at predicting the future than a layperson using random guesswork. Tetlock''s latest project âe\" an unprecedented, government-funded forecasting tournament involving over a million individual predictions âe\" has since shown that there are, however, some people with real, demonstrable foresight. These are ordinary people, from former ballroom dancers to retired computer programmers, who have an extraordinary ability to predict the future with a degree of accuracy 60% greater than average. They are superforecasters. In Superforecasting, Tetlock and his co-author Dan Gardner offer a fascinating insight into what we can learn from this elite group. They show the methods used by these superforecasters which enable them to outperform even professional intelligence analysts with access to classified data. And they offer practical advice on how we can all use these methods for our own benefit âe\" whether in business, in international affairs, or in everyday life.",
"industryIdentifiers": [
{
"type": "ISBN_13",
"identifier": "9781847947154"
},
{
"type": "ISBN_10",
"identifier": "1847947158"
}
],
"readingModes": {
"text": false,
"image": false
},
"pageCount": 352,
"printType": "BOOK",
"averageRating": 4.0,
"ratingsCount": 36,
"maturityRating": "NOT_MATURE",
"allowAnonLogging": false,
"contentVersion": "preview-1.0.0",
"imageLinks": {
"smallThumbnail": "http://books.google.co.uk/books/content?id=_lMPDAAAQBAJ&printsec=frontcover&img=1&zoom=5&edge=curl&source=gbs_api",
"thumbnail": "http://books.google.co.uk/books/content?id=_lMPDAAAQBAJ&printsec=frontcover&img=1&zoom=1&edge=curl&source=gbs_api"
},
"language": "en",
"previewLink": "http://books.google.co.uk/books?id=_lMPDAAAQBAJ&printsec=frontcover&dq=isbn:1847947158&hl=&cd=1&source=gbs_api",
"infoLink": "http://books.google.co.uk/books?id=_lMPDAAAQBAJ&dq=isbn:1847947158&hl=&source=gbs_api",
"canonicalVolumeLink": "http://books.google.co.uk/books/about/Superforecasting.html?hl=&id=_lMPDAAAQBAJ"
},
"saleInfo": {
"country": "GB",
"saleability": "NOT_FOR_SALE",
"isEbook": false
},
"accessInfo": {
"country": "GB",
"viewability": "PARTIAL",
"embeddable": true,
"publicDomain": false,
"textToSpeechPermission": "ALLOWED_FOR_ACCESSIBILITY",
"epub": {
"isAvailable": false
},
"pdf": {
"isAvailable": false
},
"webReaderLink": "http://books.google.co.uk/books/reader?id=_lMPDAAAQBAJ&hl=&printsec=frontcover&output=reader&source=gbs_api",
"accessViewStatus": "SAMPLE",
"quoteSharingAllowed": false
},
"searchInfo": {
"textSnippet": "These are ordinary people, from former ballroom dancers to retired computer programmers, who have an extraordinary ability to predict the future with a degree of accuracy 60% greater than average. They are superforecasters."
}
}
]
}'
SELECT
OJV.
,OJV.value
,OJV.type
FROM OPENJSON(@returnedCitation,'$.items[0].volumeInfo.authors') OJV;
Output
key value type
------ ---------------- ----
0 Philip Tetlock 1
1 Dan Gardner 1
July 11, 2016 at 3:11 am
Thank you, this works in your example but when I pull it from google it doesnt. Please try with the full script below. (I will replace the api key later).
CREATE function [dbo].[GetHttp]
(
@url varchar(8000)
)
returns varchar(8000)
WITH EXECUTE AS OWNER
as
BEGIN
DECLARE @win int
DECLARE @hr int
DECLARE @text varchar(8000)
EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win
EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win
EXEC @hr=sp_OAMethod @win,'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win
EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win
EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win
RETURN @text
END
GO
DECLARE@ISBN varchar(150) = '9781847947154'
DECLARE
@urlPrefix varchar(300) = 'https://www.googleapis.com/books/v1/volumes?q=isbn:'
,@urlComplete varchar(2000)
,@returnedCitation nvarchar(4000)
SET @urlComplete = @urlPrefix + @ISBN + @urlKEY;
select @returnedCitation = dbo.GetHttp(@urlComplete) ;
-----AUTHORS
SELECT
OJV.
,OJV.value
,OJV.type
FROM OPENJSON(@returnedCitation,'$.items[0].volumeInfo.authors') OJV;
This only returns one result
keyvaluetype
0Philip Tetlock1
July 11, 2016 at 3:34 am
it's strange, when I try with a different ISBN it works fine e.g. 0273767062
3 authors are returned?
really odd
July 11, 2016 at 3:34 am
olibbhq (7/11/2016)
Thank you, this works in your example but when I pull it from google it doesnt. Please try with the full script below. (I will replace the api key later).This only returns one result
keyvaluetype
0Philip Tetlock1
This is because that is the only author returned by the API
😎
... "authors": [ "Philip Tetlock" ],...
July 11, 2016 at 3:41 am
Thank you, I have been so caught up with the issue I must have mixed up the isbn's! Thank you so much for helping me with the fix, tested it now on a few others and it works perfectly.
Thank you again!
July 11, 2016 at 3:44 am
maybe some mix up with google - amazon said there were two 🙂
July 11, 2016 at 3:45 am
olibbhq (7/11/2016)
Thank you, I have been so caught up with the issue I must have mixed up the isbn's! Thank you so much for helping me with the fix, tested it now on a few others and it works perfectly.Thank you again!
You are very welcome
😎
BTW if you use the ISBN-13 value of 9780771070525 you will get both authors;-)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply