Parsing JSON from RESTful API with T-SQL

Question

I want to consume RESTful api from SurveyHero into SQL Server, i have 2 concerns:

How would i consume the RESTful API which needs BasicAuth directly in SQL SERVER.
I tried to consume the API in C# and passed on the JSON data to SQL Server, and tried to use OPENJSON() function to parse JSON data.

The json returned from the API is as below:

{
    "surveys": [{
        "survey_id": 94242,
        "title": "title here",
        "created_on": "2018-10-19T00:05:28+00:00",
        "number_of_questions": 47,
        "number_of_responses": 1403
    }, {
        "survey_id": 125865,
        "title": "title 2 here",
        "created_on": "2019-03-15T00:38:11+00:00",
        "number_of_questions": 45,
        "number_of_responses": 9109
    }]
}

Now i tried below OPENJSON function:

SELECT *  
FROM OPENJSON(@json)  
  WITH (id int 'strict $.surveys.survey_id', title nvarchar(100) '$.surveys.title', createddate datetime '$.surveys.created_on')

The above query fails throwing below error:

JSON path is not properly formatted. Unexpected character '0' is found at position 0.

However if i remove {"surveys":[ from root it works fine, but when i call the API, it always returns that way only, could anyone suggest how could i parse the jSON appropriately?

If you can use c#, why not parse the json in it and store the properties as columns in a table in SQL Server? — Zohar Peled, Aug 25 '19 at 03:42

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

I cannot reproduce your error...

DECLARE @YourJson NVARCHAR(MAX)=
N'{
    "surveys": [{
        "survey_id": 94242,
        "title": "title here",
        "created_on": "2018-10-19T00:05:28+00:00",
        "number_of_questions": 47,
        "number_of_responses": 1403
    }, {
        "survey_id": 125865,
        "title": "title 2 here",
        "created_on": "2019-03-15T00:38:11+00:00",
        "number_of_questions": 45,
        "number_of_responses": 9109
    }]
}';

--it seems to be valid JSON

SELECT ISJSON(@YourJson);

--As your object is an array, you need a slightly changed path and datetime2 to retrieve one element (index "0" is the first one):

SELECT *  
FROM OPENJSON(@YourJson)  
  WITH (id int 'strict $.surveys[0].survey_id', title nvarchar(100) '$.surveys[0].title', createddate datetime2 '$.surveys[0].created_on')

In this case the usage of OPENJSON is wrong. You would reach exactly the same with SELECT JSON_VALUE(@YourJson, 'strict $.surveys[0].survey_id')...

What you really need, seems to be this

SELECT TheSurvey.*  
FROM OPENJSON(@YourJson,'$.surveys')  
  WITH (id INT '$.survey_id'
       ,title NVARCHAR(MAX)
       ,createddate DATETIME2 '$.created_on') TheSurvey

This last sample will read the surveys-array row-by-row. You can test the intermediate result with SELECT * FROM OPENJSON(@YourJson,'$.surveys');. The WITH-clause - as OPENJSON() returns fragments of the original JSON - needs relative paths now.

Parsing JSON from RESTful API with T-SQL

1 Answers1

What you really need, seems to be this