Insert nested json array into multiple tables in sql server

The easy part is the first table, because we're only inserting one row and it has no dependencies:

BEGIN TRANSACTION;

INSERT Table1([Name], [Location], [Region])
SELECT [Name], [Location], [Region]
FROM OPENJSON(@JsonObject, '$.FirstElement')
WITH (
    [Name] VARCHAR(100),
    [Location] VARCHAR(100),
    [Region] VARCHAR(100)
);

DECLARE @Table1Id INT = SCOPE_IDENTITY();

The hard part is the next table. We need to capture all the identities of the inserted rows, but also all the data yet to be inserted into table 3. Because the OUTPUT clause of INSERT is restricted to outputting values in the base table only, we need to use MERGE trickery:

DECLARE @Table3Input TABLE([Table2Id] INT, [InnerMostElement] NVARCHAR(MAX));

MERGE Table2
USING (
    SELECT [Name], [Description], [Type], [InnerMostElement]
    FROM OPENJSON(@JsonObject, '$.FirstElement.InnerElement')
    WITH (
        [Name] VARCHAR(100),
        [Description] VARCHAR(100),
        [Type] VARCHAR(100),
        [InnerMostElement] NVARCHAR(MAX) AS JSON
    )
) AS J
ON 1 = 0    -- Always INSERT
WHEN NOT MATCHED THEN 
    INSERT([Table1Id], [Name], [Description], [Type])
    VALUES (@Table1Id, J.[Name], J.[Description], J.[Type])
    OUTPUT inserted.Id, J.[InnerMostElement]
    INTO @Table3Input([Table2Id], [InnerMostElement]);

If the tables are to be primarily filled using JSON, it may be more convenient to use SEQUENCE objects to generate consecutive values (using sp_sequence_get_range) without the need to capture the whole JSON into a temporary table. That would greatly simplify this and remove the need for MERGE.

The last table is easy again:

INSERT Table3([Table2Id], [Key], [Value])
SELECT [Table2Id], KV.[Key], KV.[Value]
FROM @Table3Input CROSS APPLY (
    SELECT [Key], [Value]
    FROM OPENJSON([InnerMostElement])
    WITH (
        [Key] VARCHAR(100),
        [Value] VARCHAR(100)
    )
) AS KV;

COMMIT;

The transaction is logically necessary to ensure this object is entirely inserted, or not at all.

Final output:

+----+------+----------+---------+
| Id | Name | Location | Region  |
+----+------+----------+---------+
|  1 | ABC  | East US  | West US |
+----+------+----------+---------+

+----+----------+------+-----------------+--------+
| Id | Table1Id | Name |   Description   |  Type  |
+----+----------+------+-----------------+--------+
|  1 |        1 | IE1  | IE1 Description | Small  |
|  2 |        1 | IE2  | IE2 Description | Medium |
+----+----------+------+-----------------+--------+

+----+----------+-----------------+--------+
| Id | Table2Id |       Key       | Value  |
+----+----------+-----------------+--------+
|  1 |        1 | Name            | IME1   |
|  2 |        1 | AnotherProperty | Value1 |
|  3 |        2 | Name            | IME2   |
|  4 |        2 | Address         | Xyz    |
|  5 |        2 | Type            | Simple |
|  6 |        2 | LastProperty    | ValueX |
+----+----------+-----------------+--------+

For completeness, here's how you'd turn that back into JSON:

SELECT 
  [Name] AS 'FirstElement.Name', 
  [Location] AS 'FirstElement.Location', 
  [Region] AS 'FirstElement.Region',
  (
    SELECT 
      [Name], 
      [Description], 
      [Type],
      (
        SELECT 
          [Key], 
          [Value]
        FROM Table3
        WHERE Table3.Table2Id = Table2.Id
        FOR JSON PATH
      ) AS 'InnerMostElement'
    FROM Table2
    WHERE Table2.Table1Id = Table1.Id
    FOR JSON PATH
  ) AS 'FirstElement.InnerElement'
FROM Table1
FOR JSON PATH;