Каталог решений - JSON в запросах DaJet QL

Данная публикация является продолжением моей статьи про язык запросов DaJet QL. Пишу я её больше для себя и всех тех, кто заинтересовался технологией DaJet QL. Отвечаю таким образом на их вопросы.

Кроме этого, в этой статье я хочу рассказать о новых возможностях SQL Server 2016, которые позволяют эффективно работать с данными в формате JSON непосредственно в языке запросов. Совмещаю приятное с полезным =)

Дано:

1. Платформа 1С:Предприятие 8.3.15.1534

2. Microsoft SQL Server 2016 Express Edition

3. Управление торговлей, редакция 11.2 (11.2.3.159)

4. Бухгалтерия предприятия, редакция 3.0 (3.0.67.63)

5. Web сервер DaJet QL

Примеры:

1. Получение курсов валют из УТ в формате JSON.

2. Чтение курсов валют в формате JSON в БП.

3. Перенос курсов валют из УТ в БП одним запросом.

Обе задачи выполняются средствами языка запросов DaJet QL.

Ссылка где взять web сервер DaJet QL дана выше.

Синхронизация справочников "Валюты" выполняется по коду.

1. Получение курсов валют в формате JSON.

Запрос для DaJet QL:

DECLARE @period datetime = '4020-04-20';

SELECT
(
	SELECT
		DATEADD(YEAR, -2000, КурсыВалют.Период) AS [Period],
		Валюты.Код                              AS [Currency],
		КурсыВалют.Кратность                    AS [Factor],
		КурсыВалют.Курс                         AS [Rate]
	FROM
		РегистрСведений.КурсыВалют AS КурсыВалют
		INNER JOIN Справочник.Валюты AS Валюты
		ON КурсыВалют.Валюта.uuid = Валюты.Ссылка.uuid
	WHERE
		КурсыВалют.Период = @period
	FOR JSON PATH
) AS json

Результат в формате JSON:

[
  {
    "Period": "2020-04-20T00:00:00",
    "Currency": "840",
    "Factor": 1,
    "Rate": 65.1234
  },
  {
    "Period": "2020-04-20T00:00:00",
    "Currency": "978",
    "Factor": 1,
    "Rate": 70.4321
  }
]

2. Чтение курсов валют в формате JSON.

Запрос для DaJet QL:

DECLARE @json nvarchar(max) = N'
[
	{
		"Period" : "2020-04-20T00:00:00",
		"Currency" : "840",
		"Factor" : 1,
		"Rate" : 65.1234
	},
	{
		"Period" : "2020-04-20T00:00:00",
		"Currency" : "978",
		"Factor" : 1,
		"Rate" : 70.4321
	}
]';

SELECT
	DATEADD(YEAR, 2000, JsonTable.[Период]) AS [Период],
	JsonTable.[Валюта]                      AS [КодВалюты],
	JsonTable.[Кратность]                   AS [Кратность],
	JsonTable.[Курс]                        AS [Курс]
FROM
	OPENJSON(@json)
WITH (
	[Период]    datetime      '$.Period',
	[Валюта]    nvarchar(3)   '$.Currency',
	[Кратность] numeric(10)   '$.Factor',
	[Курс]      numeric(10,4) '$.Rate'
) AS JsonTable
INNER JOIN Справочник.Валюты AS Валюты
ON JsonTable.Валюта = Валюты.Код;

Результат в табличном виде:

3. Перенос курсов валют из УТ в БП одним запросом.

Запрос для DaJet QL:

DECLARE @period datetime = '4020-04-20';

INSERT [accounting_3_0_72_72_demo].РегистрСведений.КурсыВалют
(
	Период,
	Валюта.uuid,
	Кратность,
	Курс,
	ОбластьДанныхОсновныеДанные
)
SELECT
	КурсыВалют.Период    AS [Период],
	БПВалюты.Ссылка.uuid AS [Валюта],
	КурсыВалют.Кратность AS [Кратность],
	КурсыВалют.Курс      AS [Курс],
	0                    AS [ОбластьДанныхОсновныеДанные]
FROM
	[trade_11_2_3_159_demo].РегистрСведений.КурсыВалют AS КурсыВалют

	INNER JOIN [trade_11_2_3_159_demo].Справочник.Валюты AS УТВалюты
	ON КурсыВалют.Валюта.uuid = УТВалюты.Ссылка.uuid

	INNER JOIN [accounting_3_0_72_72_demo].Справочник.Валюты AS БПВалюты
	ON УТВалюты.Код = БПВалюты.Код

WHERE
	КурсыВалют.Период = @period

Результат:

Курсы валют перенесены из УТ в регистр сведений "КурсыВалют" БП.

Примечания:

В этих примерах используется два способа передачи параметров в запросы DaJet QL. Для этого можно использовать возможности оператора DECLARE, а также функционал SQL Server 2016 и выше для передачи табличных данных в запрос в формате JSON.

При этом стоит отметить, что технику получения табличных данных из JSON во 2-ом примере можно успешно использовать в 3-ем примере для вставки этих данных в таблицу СУБД.

Для организации взаимодействия между удалёнными серверами SQL Server, которые не являются связанными (linked), можно использовать технику получения данных в формате JSON на одном сервере, с последующей передачей и обработкой этих данных на втором сервере. Для этого можно установить на этих серверах по одному web серверу DaJet QL, а управлять ими вообще при помощи третьего.