2

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?

mpartan
  • 1,296
  • 1
  • 14
  • 30

0 Answers0