sqlsql-serveropen-json

Add a column to the result of an OPENJSON query

    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?

Solution

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