SQL Server Converting multiple JOINS to json using JSON AUTO

rsaldnfx 于 2023-03-17 发布在其他

关注(0)|答案(2)|浏览(103)

I currently have the following SQL query.

SELECT quote.id,
       lines.id
        
FROM Quote quote

INNER JOIN Quote_Line as lines
    ON quote.id = lines.quote_id

FOR JSON AUTO

As expected it correctly returns the following JSON:

[
  {
    "id": 1,
    "lines": [
      {
        "id": 1
      }
    ]
  }
]

Now i am trying to extend this output with another 1-to-1 JOIN (Quote to Quote_Customer). How do i write this JOIN to get the following output:

[
  {
    "id": 1,
    "customer": {
      "id": 1
    },
    "lines": [
      {
        "id": 1
      }
    ]
  }
]

I have tried adding a simple LEFT JOIN Quote_Customer customer ON quote.id = customer.quote_id , but this adds a nested customer object inside every lines -object.

Any ideas?

sql-server

来源：https://stackoverflow.com/questions/75744025/converting-multiple-joins-to-json-using-json-auto

2条答案

按热度按时间

hxzsmxv21#

One method would be to use a correlated subquery in the SELECT :

CREATE TABLE dbo.Quote (ID int);
CREATE TABLE dbo.QuoteLine (ID int,
                            QuoteID int);
CREATE TABLE dbo.Customer (ID int,
                           QuoteID int);
GO

INSERT INTO dbo.Quote (ID)
VALUES(1);
INSERT INTO dbo.QuoteLine (ID,QuoteID)
VALUES(1,1),(2,1);
INSERT INTO dbo.Customer (ID,QuoteID)
VALUES(1,1);
GO

SELECT Q.ID,
       (SELECT ID
        FROM dbo.Customer C
        WHERE C.QuoteID = Q.ID
        FOR JSON AUTO) AS Customer,
       Lines.ID
FROM dbo.Quote Q --Aliasing a table called quote as quote doesn't make any sense
     INNER JOIN dbo.QuoteLine AS Lines ON Q.ID = Lines.QuoteID
FOR JSON AUTO;

GO
DROP TABLE dbo.Customer;
DROP TABLE dbo.QuoteLine;
DROP TABLE dbo.Quote;

赞(0）回复(0）举报 2023-03-17

nkoocmlb2#

try to run below query

SELECT quote.quote_id,
       (select quote_id from Quote_Customer where quote.quote_id = quote_id  For json auto)customer,
       lines.quote_id
       
FROM Quote quote
INNER JOIN Quote_Line as lines
    ON quote.quote_id = lines.quote_id
FOR JSON AUTO

you can get below output:

[
  {
    "quote_id": 1,
    "customer": [
      {
        "quote_id": 1
      }
    ],
    "lines": [
      {
        "quote_id": 1
      }
    ]
  },
  {
    "quote_id": 2,
    "lines": [
      {
        "quote_id": 2
      }
    ]
  }
]

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server Converting multiple JOINS to json using JSON AUTO

2条答案

相关问题

热门标签

最新问答