Importing JSON

Question

Post reply

Importing JSON

Mark Harley

SSCrazy

Points: 2259

December 4, 2019 at 7:00 pm

Go to Answer

#3704457

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.

Steve Collins

SSCrazy

Points: 2184
More actions
December 4, 2019 at 9:04 pm
Answer
#3704522
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_jsonnvarchar(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

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

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

Steve Collins SSCrazy Points: 2184 More actions · Answer 1

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 5 years, 2 months ago by Steve Collins.

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

Mark Harley SSCrazy Points: 2259 More actions · Answer 2

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?

Steve Collins SSCrazy Points: 2184 More actions · Answer 3

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