Accessing OPENJSON parsed JSON multiple times in a stored procedure?

Question

Consider this JSON:

{
    "Name": "Alice",
    "Relations": [
        {
            "RelationId": 1,
            "Value": "one"
        },
        {
            "RelationId": 2,
            "Value": "two"
        }
    ]
}

I pass this JSON to a stored procedure where it is parsed and the names are inserted:

-- parse JSON
WITH [source]
AS (SELECT *
    FROM
        OPENJSON(@json)
        WITH
        (
            [Name] VARCHAR(50),
            [Relations] VARCHAR(MAX)
        ) -- end json WITH
) -- end WITH [source] AS

-- insert Name
INSERT INTO dbo.names
(
    [Name]
)
SELECT [s].[Name]
FROM [source] s;

Next, I want to insert the relations, so first I have to OPENJSON the [Relations] part:

WITH [relationsSource]
AS (SELECT *
    FROM
        -- now, here is the problem: the CTE (common table expression)
        -- named [source] isn't available anymore
        OPENJSON(<how to access [source].[Relations] here?)
        WITH
        (
            [RelationId] INT,
            [Value] VARCHAR(50)
        ) -- end json WITH
) -- end WITH [relationsSource]

I know I could do something like OPENJSON(@json, '$Relations'). But this would parse the whole @json again to search for the $Relations path instead of only parsing the previously extracted [source].[Relations].

Is there any solution that allows me using something like

OPENJSON([source].[Relations]) -- pass only the Relations subset of @json

so that OPENJSON doesn't have to parse the complete @json again?

Preben Huybrechts · Answer 1 · 2020-12-31T13:07:21.883

Try this:

DECLARE @Json NVARCHAR(MAX) = N'
{
    "Name": "Alice",
    "Relations": [
        {
            "RelationId": 1,
            "Value": "one"
        },
        {
            "RelationId": 2,
            "Value": "two"
        }
    ]
}
'

SELECT persons.Id PersonId
    , persons.Name
    , relations.RelationId
    , relations.Value
INTO #JsonToFlatTable
FROM (
    -- sub-query to retrieve the root person.Name and the array of relations
    SELECT *
    , Row_Number() OVER (ORDER BY Name) Id -- Add a fake ID or use some kind of mapping with an existing table. 
    FROM OPENJSON(@json, N'lax $') 
    WITH (
        [Name] VARCHAR(255) N'lax $.Name'
        , Relations nvarchar(MAX) N'$.Relations' AS JSON  
    )
) persons
-- Use openjson on the subset of relations to retrieve the RelationId and Value
CROSS APPLY OPENJSON(persons.Relations, N'lax $')
    WITH(
        RelationId INT N'lax $.RelationId'
        , Value VARCHAR(255) N'lax $.Value'
    ) relations 

-- Maybe set IDENTITY_INSERT ON
INSERT INTO Person(Id, Name)
SELECT DISTINCT PersonId
    , Name
FROM #JsonToFlatTable
-- Maybe set IDENTITY_INSERT OFF

INSERT INTO Relation(PersonId, RelationId, Value)
SELECT PersonId
    , RelationId
    , Value
FROM #JsonToFlatTable

Output

PersonId	Name	RelationId	Value
1	Alice	1	one
1	Alice	2	two

You specify a schema using the WITH clause. And you will parse only the subset relations with OPENJSON a second time.

The AS JSON on the line Relations nvarchar(MAX) N'$.Relations' AS JSON is critical for this to work. If AS JSON is not specified, you'll get NULL instead of the array.

From the microsoft docs above:

Notice how the AS JSON clause causes values to be returned as JSON objects instead of scalar values in col5 and array_element.

Parsing everything in one go isn't possible if the array size is dynamic. If it's a fixed size you could create columns for every known index, but this isn't maintainable. When the array size changes, you'll need to change your code.

To then put the extracted data in a corresponding table you can use a temp table and fill the corresponding tables. You may need Identity_Insert. Or you can use an output clause to retrieve the generated ID from the persons table.

Thank you for that detailed reply! Unfortunately it shows me, that I might have reduced my question too much. I need to insert the data in two different tables, so `Name`goes in `dbo.names` as given, but for the relations there's a second table `dbo.relations` and every row there needs to be (,,). Not sure if I should ask a new question though or modify my existing one ): Meanwhile, I am wondering if `Relations nvarchar(MAX) N'$.Relations' AS JSON` doesn't already parse the Relations (as they are returned as a JSON object) ? — stefan.at.kotlin, Dec 31 '20 at 12:19
Also, do you know why `AS JSON` is needed for the Relations and what's the difference between JSON as a string and JSON with `AS JSON`? — stefan.at.kotlin, Dec 31 '20 at 12:20

score 0 · Answer 2 · answered Dec 31 '20 at 14:11

You could try something like this. The JSON_VALUE function selects the single instance of 'Name'. The OPENJSON tvf specifies the path to the 'Relations' object and provides the column definitions.

declare @json nvarchar(max) = N'
{
    "Name": "Alice",
    "Relations": [
        {
            "RelationId": 1,
            "Value": "one"
        },
        {
            "RelationId": 2,
            "Value": "two"
        }
    ]
}'


select json_value(@json, '$.Name') as [Name], j.*
from openjson(@json, '$.Relations') 
              with (RelationId          int,
                    [Value]             nvarchar(4000)) j;

Output

Name    RelationId  Value
Alice   1           one
Alice   2           two

Accessing OPENJSON parsed JSON multiple times in a stored procedure?

2 Answers2