json - 在存储过程中多次访问 OPENJSON 解析的 JSON？

Question

考虑这个 JSON:

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

我将此 JSON 传递给一个存储过程，在该存储过程中对其进行解析并插入名称:

-- 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;

接下来，我想插入关系，所以首先我必须OPENJSON [Relations] 部分:

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]

我知道我可以做类似 OPENJSON(@json, '$Relations') 的事情。但这将再次解析整个 @json 以搜索 $Relations 路径，而不是仅解析先前提取的 [source].[Relations].

是否有任何解决方案可以让我使用类似的东西

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

这样 OPENJSON 就不必再次解析完整的 @json 了吗？

Answer 1

试试这个:

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

输出

<表类="s-表"><头>PersonId姓名RelationId值(value)<正文>1爱丽丝1一个1爱丽丝2两个

您指定一个 schema使用 WITH条款。您将仅解析与 OPENJSON 的子集关系第二次。

AS JSON在线Relations nvarchar(MAX) N'$.Relations' AS JSON对此工作至关重要。如果AS JSON未指定，您将得到 NULL而不是数组。

来自上面的微软文档:

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

如果数组大小是动态的，一次性解析所有内容是不可能的。如果它是固定大小，您可以为每个已知索引创建列，但这是不可维护的。当数组大小发生变化时，您需要更改代码。

然后将提取的数据放入相应的表中，您可以使用临时表并填充相应的表。您可能需要 Identity_Insert .或者你可以使用 output clause从人员表中检索生成的 ID。