CODEWITHSUNDEEP
sqlsql-serveropen-json
Yoav24
Yoav24

Reputation: 363

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?

Upvotes: 1

Views: 296

Answers (1)

griv
griv

Reputation: 2245

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 
)

Upvotes: 1

Related Questions