JSON to SQL variable string

Question

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

yes. I'm trying to select both values from each item in data. The problem is I can't get a parameter inside of the string value '$.data[0][0]' — Dumbledore__
– Dumbledore__, Commented Jun 7, 2019 at 20:43

Zhorov · Accepted Answer · 2019-06-07 20:52:24Z

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

Collectives™ on Stack Overflow

JSON to SQL variable string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related