JSON в запросах DaJet QL

13.06.20

Разработка - Запросы

Практические примеры работы с JSON непосредственно в языке запросов. Перенос курсов валют между УТ и БП. Требуется SQL Server 2016 и выше.

Данная публикация является продолжением моей статьи про язык запросов 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.Валюта = Валюты.Код;

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

Период

КодВалюты

Кратность

Курс

4020-04-20T00:00:00

840

1

65,1234

4020-04-20T00:00:00

978

1

70,4321

 

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, а управлять ими вообще при помощи третьего.

DaJet QL интеграция обмен данными перенос данных свёртка web сервисы

См. также

Инструментарий разработчика Роли и права Запросы СКД Программист Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

10000 руб.

02.09.2020    156445    849    398    

855

Запросы Программист Бесплатно (free)

Увидел cheatsheet по SQL и захотелось нарисовать подобное, но про запросы.

18.10.2024    8900    sergey279    18    

60

Запросы Программист Платформа 1С v8.3 Запросы Конфигурации 1cv8 Бесплатно (free)

Столкнулся с интересной ситуацией, которую хотел бы разобрать, ввиду её неочевидности. Речь пойдёт про использование функции запроса АВТОНОМЕРЗАПИСИ() и проблемы, которые могут возникнуть.

11.10.2024    4612    XilDen    35    

75

Запросы Программист Запросы Бесплатно (free)

Отлаживая взаимодействие с базой данных, мы регулярно сталкиваемся с зависающими или подозрительно долго выполняющимися обращениями, негативно влияющими на производительность. О том, как в PostgreSQL выявить подозрительные запросы, основываясь на доступной о них информации, расскажем в статье.

16.08.2024    7463    user1840182    5    

28

Математика и алгоритмы Запросы Программист Платформа 1С v8.3 Запросы Бесплатно (free)

Рассмотрим быстрый алгоритм поиска дублей с использованием hash функции по набору полей шапки и табличных частей.

08.07.2024    2292    ivanov660    9    

22

Запросы СКД Программист Стажер Система компоновки данных Россия Бесплатно (free)

Часто при разработке отчетов в СКД возникает ситуация, когда не совсем понятно, почему отчет выводит не те данные, которые нужны, либо не выводит вовсе. Возникает потребность увидеть конечный запрос, который формирует СКД. Как это сделать, рассмотрим в этой статье.

15.05.2024    8057    implecs_team    6    

46

Запросы Программист Стажер Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Часто поступают задачи по произвольному распределению общих сумм. После распределения иногда пропадают копейки. Суть решения добавить АвтоНомерЗаписи() в ВТ распределения, и далее используя функции МАКСИМУМ или МИНИМУМ можем положить разницу копеек в первую или последнюю строку знаменателя распределения.

11.04.2024    3318    andrey_sag    10    

35
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. awk 744 24.04.20 07:50 Сейчас в теме
Следующая статья "XML в запросах 1C#" :)

SELECT *
AS XML

За статью плюс.
2. tanya_charm 03.05.20 10:05 Сейчас в теме
Ознакомился. Толково. Жду дальше.
3. rabid_otter 134 01.06.20 13:41 Сейчас в теме
как оно прикручивается к КД 3.0? имхо, никак.
прикольно конечно, плюс если уиды не те в БП и УТ?
4. zhichkin 1522 01.06.20 14:45 Сейчас в теме
(3)
как оно прикручивается к КД 3.0?

Цели прикрутить к КД пока не было.
если уиды не те в БП и УТ?

Можно по кодам синхронизироваться или другим реквизитам.
Предикат WHERE переписать соответствующим образом.
Плюс для этого есть ещё таблицы соответствий.
Например, регистр сведений "СоответствияОбъектовИнформационныхБаз" в БСП.
5. rabid_otter 134 01.06.20 14:55 Сейчас в теме
(4) если вы приводите в пример 2 типовые конфигурации, то хотелось бы увидеть пример реального кейса и возможную реализацию в КД 3.0.
сам то я понимаю, что ее скорее всего не будет, ибо там переписывать придется много.
в итоге можно свой обмен с преферансом и куртизанками придумать, чтобы достичь универсальности.
кроме того, базы должны быть на 1м сервере MS SQL. мы же про него только говорим?
6. zhichkin 1522 01.06.20 15:15 Сейчас в теме
(5) Я всё делаю только под SQL Server, так как знаний по Postgre нет, но потенциально и это возможно.
Статью написал для себя (на память) и друзей, которые озадачились использовать у себя на практике без КД.
Оставьте свое сообщение