Simplified table structure is as follows
CREATE TABLE TableA(
id BIGINT IDENTITY NOT NULL,
value nvarchar(max) NOT NULL
);
CREATE TABLE TableB(
id BIGINT IDENTITY NOT NULL,
value nvarchar(max) NOT NULL,
tableARid BIGINT NOT NULL
)
INSERT INTO TableA(value) VALUES('test');
INSERT INTO TableB(value, tableARid) VALUES ('test1', 1);
INSERT INTO TableB(value, tableARid) VALUES ('test2', 1);
INSERT INTO TableB(value, tableARid) VALUES ('test3', 1);
I would hope to get JSON out of it, so that TableB values are as a simple array. So when I try to query like this:
SELECT id,
value,
(SELECT value from TableB FOR JSON PATH) AS 'tableBValues'
FROM TableA
FOR JSON PATH
The result will be
[
{
"id": 1,
"value": "test",
"tableBValues": [
{
"value": "test1"
},
{
"value": "test2"
},
{
"value": "test3"
}
]
}
]
Is it possible to query using JSON PATH so that the result would like this:
[
{
"id": 1,
"value": "test",
"tableBValues": [
"test1",
"test2",
"test3"
]
}
]
Without the inner JSON objects inside the result tableBValues array?