主页 / dba / 问题 / 297326
Accepted
HeyJude
Asked: 2021-07-29 23:28:42 +0800 CST

将 FOR JSON 结果插入表列

  • 772

我有一个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))
sql-server insert

1 个回答

  1. Best Answer

    这是一种方法： 

    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*/) )
    • 2

相关问题