Importing JSON

Question

Importing JSON

Steve Collins

SSCrazy

Points: 2238
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 4 posts - 1 through 5 (of 5 total)

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

Steve Collins SSCrazy Points: 2238 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, 11 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: 2238 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