ДЛЯ JSON PATH против JSON AUTO SQL Server

У меня проблема с созданием вложенного JSON в SQL Server. Я пытаюсь создать вывод, который выглядит следующим образом:

[
  {
    "websiteURL": "www.test.edu",
    "email": "hello@test.edu",
    "phone": 123456798,
    "address": {
        "address1": "1 Oak Grove",
        "address2": "London",
        "address3": "UK"
    },
    "accreditations": [
      {
        "name": "Indicator1",
        "value": "True"
      },
      {
        "name": "Indicator2",
        "value": "False"
      },
      {
        "name": "Indicator3",
        "value": "False"
      }
    ]
  }
]

Я пробовал как для JSON AUTO, так и для JSON PATH:

SELECT
  d.SCHOOL_WEBSITE AS websiteURL
  ,d.SCHOOL_EMAIL AS email 
 ,d.SCHOOL_TELEPHONE AS phone
 ,d.[Address 1] AS 'address.address1'
 ,d.[Address 2] AS 'address.address2'
 ,d.[Address 3] AS 'address.address3'
 ,accreditations.[IndiUID] as name   
 ,accreditations.Value as value 
 FROM [TESTDB].[dbo].[DataValues] as d,[TESTDB].[dbo].[accreditations] as accreditations
 WHERE d.Code = accreditations.SchoolCode
 FOR JSON AUTO --PATH

FOR JSON AUTO создает это (раздел адреса не является вложенным (но аккредитация):

[
  {
    "websiteURL": "www.test.edu",
    "email": "hello@test.edu",
    "phone": 123456798,
    "address.address1": "1 Oak Grove",
    "address.address2": "London",
    "address.address3": "UK",
    "accreditations": [
      {
        "name": "Indicator1",
        "value": "True"
      },
      {
        "name": "Indicator2",
        "value": "False"
      },
      {
        "name": "Indicator3",
        "value": "False"
      }
    ]
  }
]

FOR JSON PATH создает это (адресная часть вложена, но аккредитации нет - весь блок повторяется):

[
  {
    "websiteURL": "www.test.edu",
    "email": "hello@test.edu",
    "phone": 123456798,
    "address": {
      "address1": "1 Oak Grove",
      "address2": "London",
      "address3": "UK"
    },
    "name": "Indicator1",
    "value": "True"
  },
  {
    "websiteURL": "www.test.edu",
    "email": "hello@test.edu",
    "phone": 123456798,
    "address": {
      "address1": "1 Oak Grove",
      "address2": "London",
      "address3": "UK"
    },
    "name": "Indicator2",
    "value": "False"
  },
  {
    "websiteURL": "www.test.edu",
    "email": "hello@test.edu",
    "phone": 123456798,
    "address": {
      "address1": "1 Oak Grove",
      "address2": "London",
      "address3": "UK"
    },
    "name": "Indicator3",
    "value": "False"
    }
]

Я думаю, что ключом к этому является какой-то подзапрос FOR JSON вокруг аккредитаций, но у меня не было никакого успеха с этим.

Создайте пример данных со следующим:

    /****** Object:  Table [dbo].[accreditations]    Script Date: 11/09/2018 22:29:43 ******/
CREATE TABLE [dbo].[accreditations](
    [SchoolCode] [nvarchar](255) NULL,
    [IndiUID] [nvarchar](255) NULL,
    [Value] [nvarchar](255) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DataValues]    Script Date: 11/09/2018 22:29:44 ******/
CREATE TABLE [dbo].[DataValues](
    [Code] [nvarchar](255) NULL,
    [SCHOOL_NAME_FORMAL] [nvarchar](255) NULL,
    [SCHOOL_WEBSITE] [nvarchar](255) NULL,
    [SCHOOL_EMAIL] [nvarchar](255) NULL,
    [SCHOOL_TELEPHONE] [float] NULL,
    [Address 1] [nvarchar](255) NULL,
    [Address 2] [nvarchar](255) NULL,
    [Address 3] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'ABC', N'Indicator1', N'True')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'ABC', N'Indicator2', N'False')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'ABC', N'Indicator3', N'False')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'DEF', N'Indicator1', N'True')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'DEF', N'Indicator2', N'False')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'DEF', N'Indicator3', N'False')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'GHI', N'Indicator1', N'True')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'GHI', N'Indicator2', N'True')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'GHI', N'Indicator3', N'True')
GO
INSERT [dbo].[DataValues] ([Code], [SCHOOL_NAME_FORMAL], [SCHOOL_WEBSITE], [SCHOOL_EMAIL], [SCHOOL_TELEPHONE], [Address 1], [Address 2], [Address 3]) VALUES (N'ABC', N'test', N'www.test.edu', N'hello@test.edu', 123456798, N'1 Oak Grove', N'London', N'UK')
GO
INSERT [dbo].[DataValues] ([Code], [SCHOOL_NAME_FORMAL], [SCHOOL_WEBSITE], [SCHOOL_EMAIL], [SCHOOL_TELEPHONE], [Address 1], [Address 2], [Address 3]) VALUES (N'DEF', N'something', N'https://something.edu/fulltime', N'hello@something.edu', 987654321, N'23 Tree Road', N'Paris', N'France')
GO
INSERT [dbo].[DataValues] ([Code], [SCHOOL_NAME_FORMAL], [SCHOOL_WEBSITE], [SCHOOL_EMAIL], [SCHOOL_TELEPHONE], [Address 1], [Address 2], [Address 3]) VALUES (N'GHI', N'university', N'http://www.university.ac.uk/', N'hello@university.ac.uk/', 123123123, N'57 Bonsai Lane', N'London', N'UK')
GO
Источник

1 ответ

Решение

Вам необходимо использовать подзапрос, чтобы создать свойство со списком значений. Используйте псевдоним для подзапроса, который является именем свойства в результирующем объекте JSON.

Это должно сделать это для вас:

SELECT
    d.SCHOOL_WEBSITE AS 'websiteURL',
    d.SCHOOL_EMAIL AS 'email ',
    d.SCHOOL_TELEPHONE AS 'phone',
    d.[Address 1] AS 'address.address1',
    d.[Address 2] AS 'address.address2',
    d.[Address 3] AS 'address.address3',
    (
        SELECT 
            [IndiUID] as 'name',
            Value as 'value' 
        FROM [dbo].accreditations as ac
        WHERE ac.SchoolCode = d.Code
        FOR JSON PATH
    ) AS accreditations
FROM dbo.DataValues d
FOR JSON PATH;

(В качестве примечания следует прекратить использование старого неявного синтаксиса JOIN.)

Источник
Другие вопросы по тегам