CODEWITHSUNDEEP
sqljsonsql-servert-sql
Dumbledore__
Dumbledore__

Reputation: 107

JSON to SQL variable string

I'm trying to extract a JSON file using SQL server. I can get the first line, but I can't figure out how to auto-increment the string to select all the values in the source file.

I've tried a while loop with union all, recursive queries. The problem is that I can't get a variable inside of the string.

Here's my select statement:

SELECT * 
FROM OPENJSON (@JSONFILE, '$.series')
WITH (
    Series_id VARCHAR(255)  '$.series_id',
    Name VARCHAR(255)   '$.name',
    Units VARCHAR(255)  '$.units',
    Frequency VARCHAR(255)  '$.f',
    Description VARCHAR(255)'$.description',
    Source VARCHAR(255) '$.source',
    Country VARCHAR(255)    '$.geography',
    Date INT        '$.data[**0**][0]',
    count INT       '$.data[**0**][1]'
    )

The bold number needs to auto-increment through the source file, which looks like this:

"series": [
    {
        "series_id": "PET.MCRFPUS2.M",
        "name": "U.S. Field Production of Crude Oil, Monthly",
        "units": "Thousand Barrels per Day",
        "f": "M",
        "unitsshort": "Mbbl/d",
        "description": "U.S. Field Production of Crude Oil",
        "copyright": "None",
        "source": "EIA, U.S. Energy Information Administration",
        "iso3166": "USA",
        "geography": "USA",
        "start": "192001",
        "end": "201901",
        "updated": "2019-03-29T12:45:52-0400",
        "data": [
            [
                "201901",
                11871
            ],
            [
                "201812",
                11961
            ],
            [
                "201811",
                11926
            ],
            [
                "201810",
                11559
            ],
            [
                "201809",
                11470

...

Select statement should auto-increment on "data" select elements

Upvotes: 2

Views: 1910

Answers (1)

Zhorov
Zhorov

Reputation: 29943

You may try with the following statement. You need to add data column in your explicit schema with AS JSON and use additional CROSS APPLY.

JSON:

DECLARE @json nvarchar(max) =
N'{
"series": [
    {
        "series_id": "PET.MCRFPUS2.M",
        "name": "U.S. Field Production of Crude Oil, Monthly",
        "units": "Thousand Barrels per Day",
        "f": "M",
        "unitsshort": "Mbbl/d",
        "description": "U.S. Field Production of Crude Oil",
        "copyright": "None",
        "source": "EIA, U.S. Energy Information Administration",
        "iso3166": "USA",
        "geography": "USA",
        "start": "192001",
        "end": "201901",
        "updated": "2019-03-29T12:45:52-0400",
        "data": [
            [
                "201901",
                11871
            ],
            [
                "201812",
                11961
            ],
            [
                "201811",
                11926
            ],
            [
                "201810",
                11559
            ],
            [
                "201809",
                11470
            ]
       ]
  }
]
}'

SELECT statement:

SELECT 
    j1.Series_id,
    j1.Name,
    j1.Units,
    j1.Frequency,
    j1.Description,
    j1.Source,
    j1.Country,
   j2.*
FROM OPENJSON(@json, '$.series') WITH (
    Series_id VARCHAR(255)  '$.series_id',
    Name VARCHAR(255)   '$.name',
    Units VARCHAR(255)  '$.units',
    Frequency VARCHAR(255)  '$.f',
    Description VARCHAR(255)'$.description',
    Source VARCHAR(255) '$.source',
    Country VARCHAR(255)    '$.geography',
    data NVARCHAR(max) '$.data' AS JSON
) j1
CROSS APPLY OPENJSON(j1.data) WITH (
    Date INT '$[0]',
    count INT '$[1]'
) j2

Output:

Series_id       Name                                        Units                       Frequency   Description                         Source                                      Country Date    count
PET.MCRFPUS2.M  U.S. Field Production of Crude Oil, Monthly Thousand Barrels per Day    M           U.S. Field Production of Crude Oil  EIA, U.S. Energy Information Administration USA     201901  11871
PET.MCRFPUS2.M  U.S. Field Production of Crude Oil, Monthly Thousand Barrels per Day    M           U.S. Field Production of Crude Oil  EIA, U.S. Energy Information Administration USA     201812  11961
PET.MCRFPUS2.M  U.S. Field Production of Crude Oil, Monthly Thousand Barrels per Day    M           U.S. Field Production of Crude Oil  EIA, U.S. Energy Information Administration USA     201811  11926
PET.MCRFPUS2.M  U.S. Field Production of Crude Oil, Monthly Thousand Barrels per Day    M           U.S. Field Production of Crude Oil  EIA, U.S. Energy Information Administration USA     201810  11559
PET.MCRFPUS2.M  U.S. Field Production of Crude Oil, Monthly Thousand Barrels per Day    M           U.S. Field Production of Crude Oil  EIA, U.S. Energy Information Administration USA     201809  11470

Upvotes: 3

Related Questions