Importing JSON

  • Mark Harley

    SSCrazy

    Points: 2259

    I am attempting to import JSON into my db and I'm getting stuck on a few nested objects.  In this particular JSON fields are omitted when there isn't a value for that particular record, and that includes the nested objects.  However, in some cases there is more than one nested object and I only want to grab a specific one.  How do I accomplish this?

    This is a sample of the file with the multiple nested objects.  I am only interested in the outer layer of data and the PrimaryObject from the secondary data.

    {
    "odata.id": "0001",
    "MediaId": "0001",
    "SecondaryData": {
    {
    "Fieldname": "PrimaryObject",
    "FieldValue": "ade0001"
    },
    {
    "Fieldname": "SecondaryObject",
    "FieldValue": "vdf0001"
    }
    },
    ....

    This is how I'm handling the import:

    declare @json nvarchar(max)
    declare @command nvarchar(1000)
    set @command = N'select @json1 = BulkColumn from openrowset (bulk ''' + @json_file + ''', single_clob) as j'
    exec sp_executesql @command, N'@json1 nvarchar(max) output', @json1 = @json output

    insert into ProjectVicData(MediaId, PrimaryObjectValue)
    select MediaID, PrimaryObjectValue
    from openjson(@json, '$.value')
    with (MediaID int '$.MediaID', SecondaryData nvarchar(max) as JSON) x
    cross apply openjson (x.SecondaryData)
    with (PrimaryObjectValue nvarchar(max) '$.FieldValue')

    The import works but a second record gets inserted when the second object is present in the SecondaryData section.

  • scdecade

    Mr or Mrs. 500

    Points: 561

    What you posted is not valid json.  The elements of SecondaryData should be in array brackets.

    {
    "odata.id": "0001",
    "MediaId": "0001",
    "SecondaryData": [
    {
    "Fieldname": "PrimaryObject",
    "FieldValue": "ade0001"
    },
    {
    "Fieldname": "SecondaryObject",
    "FieldValue": "vdf0001"
    }]
    }

    • This reply was modified 1 month, 2 weeks ago by  scdecade.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Mark Harley

    SSCrazy

    Points: 2259

    That’s an error on my part. The original Json is valid and gets processed correctly except for the nested data. I can’t share the original, unfortunately.

    I guess my question boils down to how we conditionally process nested objects?

  • scdecade

    Mr or Mrs. 500

    Points: 561

    Json_value() is used to access a single value of up to nvarchar(4000).  Openjson() is used to access arrays and can be nvarchar(max).  In the example json (with array []'s added) something like this works:

    declare
    @some_json nvarchar(max)=
    '{
    "odata.id": "0001",
    "MediaId": "0001",
    "SecondaryData": [
    {
    "Fieldname": "PrimaryObject",
    "FieldValue": "ade0001"
    },
    {
    "Fieldname": "SecondaryObject",
    "FieldValue": "vdf0001"
    }]
    }';

    select
    json_value(@some_json, N'strict $.MediaId') MediaId,
    x.*
    from
    openjson (@some_json, N'strict $.SecondaryData') with (Fieldname nvarchar(max), FieldValue nvarchar(max)) x;

    To create nested arrays requires another level.  Here the outer json elements aren't encompassed by brackets.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • scdecade

    Mr or Mrs. 500

    Points: 561

    By the way, I did have a script published here on SSC that includes an example of nested json arrays.  Please see:

    https://www.sqlservercentral.com/scripts/function-and-queries-to-convert-hierarchical-adjacency-to-nested-json-arrays

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply