How to query Json into SQL Server-CodePudding

I have a problem. I want to populate a JSON string into a table, however, it only writes me the first row and not the rest. I get the JSON from a rest API.

Below is my code and an excerpt from the JSON file

DECLARE @token INT;
DECLARE @ret INT;

DECLARE @url nvarchar(max);
DECLARE @Json2 nvarchar(max);
DECLARE @authheader nvarchar(max);
DECLARE @contentType nvarchar(max);

DECLARE @apiKey_lat nvarchar(max);
DECLARE @apiKey_lot nvarchar(max);
DECLARE @apiKey_start nvarchar(max);
DECLARE @apiKey_end nvarchar(max);

DECLARE @apikey nvarchar(max);


DECLARE @json AS TABLE (Json_Table nvarchar(max))
 

SET @authheader = ''
SET @contentType = 'application/json'




SET @url = ''

EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR ('Unable to open HTTP Connection', 10, 1);


EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'authorization', @authheader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send'

EXEC sp_OAGetProperty @token, 'responseText', @Json2 OUTPUT;



INSERT INTO @json (Json_Table) EXEC sp_OAGetProperty @token, 'responseText'

SELECT * FROM @json

SELECT * FROM OPENJSON((SELECT * FROM @json))


WITH (
    [parameter] NVARCHAR(MAX) '$.parameter',
    [lat] NVARCHAR(MAX) '$.lat',
    [lon] NVARCHAR(MAX) '$.lon',
    [dates] NVARCHAR(MAX) '$.date',
    [value] NVARCHAR(MAX) '$.value'
) AS MetaData

Here is the JSON Snippet:

{"data": [
            {
                "parameter": "t_2m:C",
                "coordinates": [
                    {
                        "lat": 51.123456,
                        "lon": -0.123456,
                        "dates": [
                            {
                                "date": "2021-11-17T12:05:00Z",
                                "value": 10.6
                            },
                            {
                                "date": "2021-11-17T13:05:00Z",
                                "value": 11.4
                            }
                        ]
                    }
                ]
            }
        ]
}

My Output looks like this:

My Output

The result should be a table with the values of LAT, LON, DATE, and VALUE from the JSON.

Here is an Example:

enter image description here

Edit: I also tiredit it with Cross Apply. Didnt worked. Looks like this:

CROSS_APPLY

Here is the codewith Cross Apply:

DECLARE @token INT;
DECLARE @ret INT;

DECLARE @url nvarchar(max);
DECLARE @Json2 nvarchar(max);
DECLARE @authheader nvarchar(max);
DECLARE @contentType nvarchar(max);

--Set Parameters
DECLARE @apiKey_lat nvarchar(max);
DECLARE @apiKey_lot nvarchar(max);
DECLARE @apiKey_start nvarchar(max);
DECLARE @apiKey_end nvarchar(max);

DECLARE @apikey nvarchar(max);


DECLARE @json AS TABLE (Json_Table nvarchar(max))

--Set Authentications 

SET @authheader = ''
SET @contentType = 'application/json'




SET @url = ''


EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR ('Unable to open HTTP Connection', 10, 1);

EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'authorization', @authheader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send'

EXEC sp_OAGetProperty @token, 'responseText', @Json2 OUTPUT;



INSERT INTO @json (Json_Table) EXEC sp_OAGetProperty @token, 'responseText'

SELECT * FROM @json

SELECT * FROM OPENJSON((SELECT * FROM @json))


WITH (
        [data] NVARCHAR(MAX) AS JSON
) AS MetaData



CROSS APPLY OPENJSON([MetaData].[data])
WITH(
    [parameter] NVARCHAR(MAX),
    [coordinates] NVARCHAR(MAX),
    [lat] NVARCHAR(MAX),
    [lon] NVARCHAR(MAX),
    [dates] NVARCHAR(MAX),
    [value] NVARCHAR(MAX)
) AS Test_Metadata

In the SQL query the URL of the rest API and the Authorization header are missing.

CodePudding user response：

You should not use the sp_OA procedures, as they are full of bugs, difficult to use, and for compatibility only. SQL Server is not a generalized scripting language, do not use it as such.

Instead use something like Powershell's Invoke-WebRequest to download the data, then feed it into SQL Server using Invoke-SqlCmd or Invoke-DbaQuery.

If you pass it in as a parameter @json you can do the following to get your desired result:

SELECT
  d.parameter,
  coord.lat,
  coord.lon,
  dates.date,
  dates.value
FROM OPENJSON(@json, '$.data[0]')
  WITH (
    parameter nvarchar(100),
    coordinates nvarchar(max) AS JSON
  ) d
CROSS APPLY OPENJSON(d.coordinates)
  WITH (
    lat decimal(9,7),
    lon decimal(9,7),
    dates nvarchar(max) AS JSON
  ) coord
CROSS APPLY OPENJSON(coord.dates)
  WITH (
    date datetimeoffset,
    value decimal(18,2)
  ) dates;

db<>fiddle

Note how coordinates and dates is declared AS JSON, and then passed into OPENJSON again using CROSS APPLY.