Insert a FOR JSON result into a table column

All we need is an easy explanation of the problem, so here it is.

I have the a result of a FOR JSON expression, and I’d like to insert it into a table column.

How do I do that?

Here is one of my unsuccessful trials (note that the SELECT hightrees ... FOR JSON PATH part is correct, and can be seen in the answer to my other question, and here’s also the live demo), which gives the error 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))

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Here is a way of doing this:

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
        )

with values way

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

and with CTE version:

;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    

or use temp table #

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply