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?

griv · Accepted Answer · 2022-09-05 04:57:24Z

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 
)

answered Sep 5, 2022 at 4:57

griv

2,2852 gold badges16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add a column to the result of an OPENJSON query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related