0

I have been trying very hard to get data extracted from a JSON message I am trying to load into some tables. I can get this one to work that has one name/value pair above it, but another message has a second outer name/value pair.

DECLARE @json nvarchar(max) =
'{
"request":
    {
    "requestId" : "3a282d32-4ed4-48e8-a6c0-23cf4921737e",
    "modelType" : "NEW"
    }
}'

select 'request' as title, request.requestId , request.modelType
from openjson(@json)
with
(
   request nvarchar(max) as json 
)
as Projects
cross apply openjson (Projects.request)
with
(
    requestId nvarchar(50),
    modelType nvarchar(50)
)  as request

This returns row as expected, but how do I write the query if there is an additional name/value pair above? Like this:

DECLARE @json nvarchar(max) =
'{
  "request": {
            "request":
                    {
                    "requestId" : "3a282d32-4ed4-48e8-a6c0-23cf4921737e",
                    "modelType" : "NEW"
                    }
            }   
}'

Can anyone help. I know it's probably really simple, but i can't seem to wrap my head around how to do it. I'm the only person at my company working with JSON or I'd literally just ask them.

Thank you in advance!

Ricky S
  • 11
  • 4

1 Answers1

0

One more cross apply is needed to reach the goal.

select 'request' as title, request.requestId , request.modelType
from openjson(@json)
with
(
   request nvarchar(max) as json 
)
as Projects
cross apply openjson (Projects.request)
with
(
    request nvarchar(max) as json 
)  as subrequest
cross apply openjson (subrequest.request)
with
(
    requestId nvarchar(50),
    modelType nvarchar(50)
)  as request

demo in db<>fiddle

Meysam Asadi
  • 6,438
  • 3
  • 7
  • 17
  • That was it! Now I don't know what I was doing but mine looked very similar to yours as I was tinkering, but that was exactly it! Why that was so hard for me, I'll never understand. I think I might have been trying to use CROSS APPLY in wrong spot for second value-pair – Ricky S Aug 11 '21 at 00:57