將 FOR JSON 結果插入表列

我有一個FOR JSON表達式的結果，我想將它插入到一個表格列中。

我怎麼做？

這是我不成功的試驗之一（請注意，該SELECT hightrees ... FOR JSON PATH部分是正確的，可以在我的另一個問題的答案中看到，這也是現場展示），它給出了錯誤Invalid object name 'TreesJson'：

SELECT * FROM (
 SELECT
 highTrees = JSON_QUERY(
               (
                 SELECT 
                 Id as id,
                 Type as type,
                 Height as height
                 FROM Trees WHERE [Height] > 5
                 FOR JSON PATH
               )
             ),

 lowTrees  = JSON_QUERY(
               (
                 SELECT 
                 Id as id,
                 Type as type,
                 Height as height
                 FROM Trees WHERE [Height] < 1
                 FOR JSON PATH
               )
             )
 FOR JSON
   PATH, WITHOUT_ARRAY_WRAPPER
) AS TreesJson;

INSERT INTO TreesGrowthLog ([Day], [TreesGrowth])
   VALUES (CAST(GETDATE() AS Date, (SELECT * FROM TreesJson FOR JSON AUTO))

這是一種方法：

INSERT INTO TreesGrowthLog ([Day], [TreesGrowth])
SELECT 
       CAST(GETDATE() AS Date),
       (
           SELECT  *
           FROM
           (
                   SELECT TreesJson.TreesJson 
                   FROM (
                               SELECT 
                               highTrees = JSON_QUERY(
                                           (
                                               SELECT     Id as id,    Type as type,   Height as height
                                               FROM Trees WHERE [Height] > 5
                                               FOR JSON PATH
                                           )
                                           ),
                               lowTrees  = JSON_QUERY(
                                           (
                                               SELECT         Id as id,    Type as type,             Height as height
                                               FROM Trees WHERE [Height] < 1
                                               FOR JSON PATH
                                           )
                                           )
                               FOR JSON  PATH, WITHOUT_ARRAY_WRAPPER
                   ) AS TreesJson(TreesJson)
           ) as a FOR JSON AUTO
       )

以價值觀的方式

INSERT INTO TreesGrowthLog ([Day], [TreesGrowth])
VALUES(
       CAST(GETDATE() AS Date),
           (
                   SELECT TreesJson.TreesJson 
                   FROM (
                               SELECT 
                               highTrees = JSON_QUERY(
                                           (
                                               SELECT     Id as id,    Type as type,   Height as height
                                               FROM Trees WHERE [Height] > 5
                                               FOR JSON PATH
                                           )
                                           ),
                               lowTrees  = JSON_QUERY(
                                           (
                                               SELECT         Id as id,    Type as type,             Height as height
                                               FROM Trees WHERE [Height] < 1
                                               FOR JSON PATH
                                           )
                                           )
                               FOR JSON  PATH, WITHOUT_ARRAY_WRAPPER
                       ) AS TreesJson(TreesJson)
           ) 
   );

並使用 CTE 版本：

;with cte as 
(
       SELECT TreesJson.TreesJson 
                   FROM (
                               SELECT 
                               highTrees = JSON_QUERY(
                                           (
                                               SELECT     Id as id,    Type as type,   Height as height
                                               FROM Trees WHERE [Height] > 5
                                               FOR JSON PATH
                                           )
                                           ),
                               lowTrees  = JSON_QUERY(
                                           (
                                               SELECT         Id as id,    Type as type,             Height as height
                                               FROM Trees WHERE [Height] < 1
                                               FOR JSON PATH
                                           )
                                           )
                               FOR JSON  PATH, WITHOUT_ARRAY_WRAPPER
                       ) AS TreesJson(TreesJson)
)
INSERT INTO TreesGrowthLog ([Day], [TreesGrowth])
SELECT 
       CAST(GETDATE() AS Date), TreesJson
FROM cte    

或使用臨時表#

SELECT * 
INTO #tmp
FROM (
 SELECT
 highTrees = JSON_QUERY(
               (
                 SELECT 
                 Id as id,
                 Type as type,
                 Height as height
                 FROM Trees WHERE [Height] > 5
                 FOR JSON PATH
               )
             ),

 lowTrees  = JSON_QUERY(
               (
                 SELECT 
                 Id as id,
                 Type as type,
                 Height as height
                 FROM Trees WHERE [Height] < 1
                 FOR JSON PATH
               )
             )
 FOR JSON
   PATH, WITHOUT_ARRAY_WRAPPER
) AS TreesJson(TreesJson);

SELECT * FROM #tmp;


INSERT INTO TreesGrowthLog ([Day], [TreesGrowth])
VALUES(
       CAST(GETDATE() AS Date),(SELECT * FROM #tmp /*FOR JSON AUTO*/) )

引用自：https://dba.stackexchange.com/questions/297326