Convert SQL table to JSON array values

Question

I have a table with the below format.

I use the below query to convert to json:
select e.empId,
(select e1.age from employee e1 where e1.empId = e.empId for json auto) as age
from employee e for json auto

and below is the result.

[
{
"empId": 1,
"age": [
{
"age": 20
}
]
},
{
"empId": 2,
"age": [
{
"age": 17
}
]
},
{
"empId": 3,
"age": [
{
"age": 16
}
]
}
]

====================================

however my desired output should be as below. can you help me?
[
{
"empId": 1,
"age": [
20
]
},
{
"empId": 2,
"age": [
17
]
},
{
"empId": 3,
"age": [
16
]
}
]

i.e. 'age' should be come as an array of values and nota as an array of objects.

Answer 1

If empID is unique, then try something like this:

select empId, json_query( concat('[', age, ']')) as age  
from employee for json auto  

In SQL 2022 you can use the new JSON_ARRAY function.

Answer 2

Hi @Devashish Parmar (BLR GSS)

If your SQL version is before 2022, you can try the methods provided by Viorel-1. If your SQL version is 2022, you can give this query a try.

select empId,json_array(Age) as age from employee for json auto;  

Hope this helps with the problem you are experiencing.

Best regards,
Percy Tang

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.