Dfyjkt

This link json part 3 open json shows how to shred json into parent child records.

Specifically showing how to generate multiple result sets from one Json Document

[
 
 "CharacterID" : null,
 "CharacterName" : "Egwene Al''Vere",
 "IsFemale" : true,
 "Address" : 
 "AddressLine1" : "Emonds Field",
 "AddressLine2" : "The Two Rivers"
 ,
 "Friends" : ["Rand Al''Thor", "Perrin Aybara", "Matrim Cauthon"],
 "Children" : null
 ,
 
 "CharacterID" : null,
 "CharacterName" : "Second Character",
 "IsFemale" : false,
 "Address" : 
 "AddressLine1" : "Emonds Field 2",
 "AddressLine2" : "The Three Rivers"
 ,
 "Friends" : ["Jeff", "Zeke", "Sid Cauthon"],
 "Children" : null
 
] 

SELECT
 JSON_VALUE(@json, '$.CharacterID') AS CharacterID,
 JSON_VALUE(@json, '$.CharacterName') AS CharacterName,
 JSON_VALUE(@json, '$.IsFemale') AS IsFemale,
 JSON_VALUE(@json, '$.Address.AddressLine1') AS AddressLine1,
 JSON_VALUE(@json, '$.Address.AddressLine2') AS AddressLine2,
 JSON_VALUE(@json, '$.Children') AS Children

SELECT
 JSON_VALUE(@json, '$.CharacterID') AS CharacterID,
 OJ.value AS Friend
FROM OPENJSON(@json, '$.Friends') OJ;

I would like to like to write a procedure to insert this document into a parent Character Table, and a child CharacterFriend table, maintaining FK references.

If the ID was not provided it would be fairly easy to figure out what it was using SCOPE IDENTITY, and then filling that in before you inserted into the CharacterFriend Table.

However, it is less clear to me how you would discover the PK of the Character table in the event that there are multiple characters defined in the json document.

What would be the best way to write an insert procedure that establishes this FK in this case. The only thing that's jumping out at me is a cursor, but this feels wrong.