CODEWITHSUNDEEP
sqljsonsql-server
user1083828
user1083828

Reputation: 43

Select from json data is resulting in null values. How to select from this simple json data

I have the following JSON data:

declare @json nvarchar(max)=
'
[{
        "images": [{
            "key": "c:\\stapler\\booktest\\gray00024.jp2",
            "imageNumber": 1
        }],
        "instrumentNumber": 94109416
    },
    {
        "images": [{
                "key": "c:\\stapler\\booktest\\gray00025.jp2",
                "imageNumber": 1
            },
            {
                "key": "c:\\stapler\\booktest\\gray00026.jp2",
                "imageNumber": 2
            }
        ],
        "instrumentNumber": 94109417
    },
    {
        "images": [{
                "key": "c:\\stapler\\booktest\\gray00027.jp2",
                "imageNumber": 1
            },
            {
                "key": "c:\\stapler\\booktest\\gray00028.jp2",
                "imageNumber": 2
            }
        ],
        "instrumentNumber": 94109418
    }
]'

I am trying to pull the key and imageNumber from each of these records.

I have tried this query which will give me the instrument number, but gives NULL skey and imageNumber values:

select * from openjson (@json)
with(
instrumentNumber nvarchar(100)  '$.instrumentNumber',
skey nvarchar(500) '$.images.key',
imageNumber nvarchar(500) '$.images.imageNumber')

Can anyone tell me what I am doing wrong?

Upvotes: 1

Views: 39

Answers (1)

Nick
Nick

Reputation: 147146

Because you have a nested array (images) you need to extract those values in another openjson which you can then cross apply to the original:

select a.instrumentNumber, b.skey, b.imageNumber
from openjson (@json)
with (
  instrumentNumber nvarchar(100)  '$.instrumentNumber',
  images nvarchar(max) as json
) a
cross apply openjson (a.images)
with (
  skey nvarchar(500) '$.key',
  imageNumber nvarchar(500) '$.imageNumber'
) b

Output for your sample data

instrumentNumber    skey                                imageNumber
94109416            c:\stapler\booktest\gray00024.jp2   1
94109417            c:\stapler\booktest\gray00025.jp2   1
94109417            c:\stapler\booktest\gray00026.jp2   2
94109418            c:\stapler\booktest\gray00027.jp2   1
94109418            c:\stapler\booktest\gray00028.jp2   2

Demo on dbfiddle.uk

Upvotes: 2

Related Questions