Início / dba / Perguntas / 297326
Accepted
HeyJude
Asked: 2021-07-29 23:28:42 +0800 CST

Inserir um resultado FOR JSON em uma coluna da tabela

  • 772

Eu tenho o resultado de uma FOR JSONexpressão e gostaria de inseri-lo em uma coluna da tabela.

Como faço isso?

Aqui está uma das minhas tentativas malsucedidas (observe que a SELECT hightrees ... FOR JSON PATHparte está correta, e pode ser vista na resposta à minha outra pergunta , e aqui também a demonstração ao vivo ), que dá o erro 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 respostas

  1. Best Answer

    Aqui está uma maneira de fazer isso: 

    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
        )

    com valores caminho

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

    e com versão 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

    ou use a tabela temporária #

    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

relate perguntas