Add a column to the result of an OPENJSON query

Question

    DECLARE @json NVARCHAR(4000) = N'{ 
        "pets" : {
                "cats" : [
                { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
                { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
                { "id" : 3, "name" : "Scratch", "sex" : "Male" }
            ]
        }
    }'

SELECT * FROM OPENJSON(@json, '$.pets.cats')  WITH --we have the "main" json address here
(
id INT '$.id', --sub-address
name varchar(10) '$.name', --sub-address
sex varchar(10) '$.sex' --sub-address 
)

The results are:

id	name	sex
1	Fluffy	Female
2	Long Tail	Female
3	Scratch	Male

I want to include another column which will not depend on the JSON, but be a custom specified string ("mammal"), so that the output is like this:

id	name	sex	Type
1	Fluffy	Female	mammal
2	Long Tail	Female	mammal
3	Scratch	Male	mammal

How would I achieve this?

Answer 1

You can simply add a constant in your SELECT statement:

    DECLARE @json NVARCHAR(4000) = N'{ 
        "pets" : {
                "cats" : [
                { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
                { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
                { "id" : 3, "name" : "Scratch", "sex" : "Male" }
            ]
        }
    }'

SELECT *, 'mammal' AS Type FROM OPENJSON(@json, '$.pets.cats')  WITH --we have the "main" json address here
(
id INT '$.id', --sub-address
name varchar(10) '$.name', --sub-address
sex varchar(10) '$.sex' --sub-address 
)