SQL Server 2017 работа с JSON

@Landser · Регистрация: 18.04.2009

Студворк — интернет-сервис помощи студентам

Доброго времени суток!

Использую MS SQL 2017. Имеется каталог с подкаталогами (вложенность - месяц\день, например май\02), в которых находятся .json файлы всегда одинаковой структуры.

Требуется pзанести все существующие файлы в БД и потом опрашивать каталог с подкаталогами и вновь появившиеся файлы заносить в БД. Интересует именно опрос подкаталогов и чтение только новых файлов (с чтением и распихиванием по таблицам .json файлов проблем думаю, не возникнет), можно ли это сделать средствами исключительно MSSQL и агента, не прибегая к помощи какого-либо приложения, написанного на яп.

Наверное python бы с этим справился, но я его к стыду не знаю, хотя разобраться в работе такого скрипта мог бы (знаю php/laravel)

Прошу подсказать. Спасибо

@Landser · 05.05.2021, 21:30 **[ТС]**

Благодарю.

T-SQL
1
cast(file_stream as varchar(max)) collate Cyrillic_General_100_CI_AI_SC_UTF8;

к сожалению не помогло,
пробовал с некоторыми кодировками из списка:

T-SQL
1
2
SELECT Name, Description FROM fn_helpcollations() 
WHERE Name like '%UTF8';

- тоже не помогло
у БД стоит сортировка Cyrillic_General_CI_AS

Добавлено через 8 минут
Даже без привязки к json делаю

T-SQL
1
  select 'РґР°РІР°Р№'  collate   Cyrillic_General_100_CI_AI_SC_UTF8;

и не преобразовывается

@invm · 05.05.2021, 21:44

Вообщето работает и как столбец, и как переменная

T-SQL
1
2
3
4
5
6
7
declare @v varbinary(max) = cast(N'Проверка' collate Cyrillic_General_100_CI_AI_SC_UTF8 as varbinary(max));
 
declare @t table (s nvarchar(max) collate Cyrillic_General_100_CI_AI_SC_UTF8);
insert into @t values (@v);
select * from @t;
 
select @v, cast(@v as nvarchar(max)) collate Cyrillic_General_100_CI_AI_SC_UTF8;

Выясняйте точно кодировку файла.

Добавлено через 2 минуты
А еще лучше, если возможно, выложите сам файл.

@Landser · 05.05.2021, 21:55 **[ТС]**

Выясняйте точно кодировку файла.

понятно, постараюсь узнать.
Кстати, если для фразы, например:

Р° С‚С‹ С‚Р°Рє РЅРµ РјРѕР¶РµС€СЊ СЃРґРµР»Р°С‚СЊ

воспользоваться каким-либо онлайн декодером, он успешно декодирует, и говорит, что исходная кодировка Windows-1251

@Andrey-MSK · 06.05.2021, 08:24

Landser, откройте файл JSON в Notepad++ и внизу будет указана его кодировка

@Landser · 06.05.2021, 16:54 **[ТС]**

откройте файл JSON в Notepad++ и внизу будет указана его кодировка

точно, спасибо. UTF-8 у меня

@invm · 06.05.2021, 17:02

Сообщение от Landser

UTF-8 у меня

BOM там есть?

@Landser · 06.05.2021, 17:10 **[ТС]**

BOM там есть?

Нет

Прикрепил сам файл (json не пропускает - переименовал в txt)
21848544.txt

@invm · 06.05.2021, 17:30

Landser, в общем работает с костылем в виде буферной таблицы

T-SQL
1
2
3
4
5
6
7
8
9
10
declare @b varbinary(max), @s varchar(max);
select @b = t.b from openrowset(bulk 'Путь к файлу\21848544.txt', single_blob) t(b);
 
select @s = cast(@b as varchar(max)) collate Cyrillic_General_100_CI_AI_SC_UTF8;
select @s;
 
declare @t table (s varchar(max) collate Cyrillic_General_100_CI_AI_SC_UTF8);
insert into @t values (@b);
select @s = s from @t;
select @s;

@Landser · 06.05.2021, 19:04 **[ТС]**

Landser, в общем работает с костылем в виде буферной таблицы
declare @b varbinary(max), @s varchar(max);
select @b = t.b from openrowset(bulk 'Путь к файлу\21848544.txt', single_blob) t(b);

select @s = cast(@b as varchar(max)) collate Cyrillic_General_100_CI_AI_SC_UTF8;
select @s;

declare @t table (s varchar(max) collate Cyrillic_General_100_CI_AI_SC_UTF8);
insert into @t values (@b);
select @s = s from @t;
select @s;

Благодарю, работает для всех файлов!
Но мне нужно обрабатывать каждое значение file_stream в строке. Надо применять табличную функцию, которая работает, как Ваш скрипт с буферной таблицей к каждой ячейке file_stream?

@invm · 06.05.2021, 20:12

Landser, скалярную функцию, а не табличную.

@Landser · 06.05.2021, 20:39 **[ТС]**

invm

скалярную функцию, а не табличную.

да, конечно

Добавлено через 7 минут
UPD: bulk не может обратиться к файлу в директории файловой таблицы, причём с самого же сервера:

Массовая загрузка невозможна, так как файл "\\WIN10\MSSQLSERVER\FileTable\Documents \2021-04-30\457675.json" не удалось открыть. Код ошибки операционной системы 50(Такой запрос не поддерживается.).

@invm · 06.05.2021, 20:43

Landser, не нужно файл открывать. Нужно file_stream конвертировать этим способом.
В коде чтение файло просто для примера.

@Landser · 06.05.2021, 22:00 **[ТС]**

invm, спасибо за ответ.
возможно ли передать в openrowset параметр?

T-SQL
1
select @b = t.b from openrowset(bulk @file_s, single_blob) t(b);

@invm · 07.05.2021, 10:40

Сообщение от Landser

возможно ли передать в openrowset параметр?

Нет.

@Аватар · 07.05.2021, 11:19

можно динамически сформировать запрос в строковою переменную и выполнить exec (@sql)

@Landser · 07.05.2021, 13:38 **[ТС]**

Аватар, спасибо за ответ. Пытаюсь оформить запрос invm

T-SQL
1
2
3
4
5
6
7
8
9
10
declare @b varbinary(max), @s varchar(max);
select @b = t.b from openrowset(bulk 'Путь к файлу\21848544.txt', single_blob) t(b);
 
select @s = cast(@b as varchar(max)) collate Cyrillic_General_100_CI_AI_SC_UTF8;
select @s;
 
declare @t table (s varchar(max) collate Cyrillic_General_100_CI_AI_SC_UTF8);
insert into @t values (@b);
select @s = s from @t;
select @s;

в функцию, возвращающую скалярный результат, передав туда file_stream переменную, используя Ваш совет по динамическому формированию запроса

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE FUNCTION encode_json (@stream varbinary(max))
RETURNS varchar(max)
AS
BEGIN
  declare @b varbinary(max),@sql nvarchar(max), @s varchar(max);
  set @sql='select  t.b FROM OPENROWSET(bulk' + @stream + ', single_blob) t(b)'
  exec sp_executesql @sql, N'@t.b varbinary(max) out', @t.b out-- вот здесь пытаюсь передать выходной параметр, 
  set @b =select @t.b   --чтобы дальше с ним работать, но делаю это неправильно
  select @s = cast(@b as varchar(max)) collate Cyrillic_General_100_CI_AI_SC_UTF8;
  declare @t table (s varchar(max) collate Cyrillic_General_100_CI_AI_SC_UTF8);
  insert into @t values (@b);
  select @s = s from @t;
  select @s;
  return @s;
END

Прошу подсказать, как при выполнении динамического запроса правильно передать выходной параметр для дальнейшей работы с ним

@Аватар · 07.05.2021, 14:03

Скалярный запрос на примере моей таблицы

SQL
1
2
3
4
5
6
7
DECLARE @s nvarchar(1000),
        @ParmOut nvarchar(1000),
        @VALUE SMALLINT
SET @s = N'SELECT @Value=KodEdIzm FROM Product WHERE Prod_Id=1894'
SET @ParmOut = N'@Value smallint OUTPUT';
EXECUTE sp_executesql @s, @ParmOut, @VALUE=@VALUE OUTPUT
SELECT @VALUE

@Landser · 07.05.2021, 23:50 **[ТС]**

Аватар, благодарю.

Скалярный запрос на примере моей таблицы

T-SQL
1
2
3
4
5
6
7
DECLARE @s nvarchar(1000),
        @ParmOut nvarchar(1000),
        @VALUE SMALLINT
SET @s = N'SELECT @Value=KodEdIzm FROM Product WHERE Prod_Id=1894'
SET @ParmOut = N'@Value smallint OUTPUT';
EXECUTE sp_executesql @s, @ParmOut, @VALUE=@VALUE OUTPUT
SELECT @VALUE

сделал так, на параметр не ругается, но ругается видимо на execute внутри скалярной функции:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
USE [FileSearchTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[encode](@stream varbinary(max))
RETURNS varchar(max)
AS
BEGIN
    declare @b varbinary(max), @sql nvarchar(max), @s varchar(max), @Value varbinary(max),@ParmOut varchar(max);
    --===================
    set @sql=N'select @value=t.b
        FROM OPENROWSET(bulk ' + convert(varchar(max),@stream) + ', single_blob) t(b)'
    SET @ParmOut = N'@Value varbinary(max) OUTPUT';
    EXEC sp_executesql @s, @ParmOut, @VALUE=@VALUE OUTPUT
    set @b = @value
    select @s = cast(@b as varchar(max)) collate Cyrillic_General_100_CI_AI_SC_UTF8;
    declare @t table (s varchar(max) collate Cyrillic_General_100_CI_AI_SC_UTF8);
    insert into @t values (@b);
    select @s = s from @t;;
    return @s;
END

Внутри функции можно вызывать только функции и некоторые расширенные хранимые процедуры.

@Landser · 08.05.2021, 14:12 **[ТС]**

invm,

T-SQL
1
2
3
4
5
6
7
8
9
10
declare @b varbinary(max), @s varchar(max);
select @b = t.b from openrowset(bulk 'Путь к файлу\21848544.txt', single_blob) t(b);
 
select @s = cast(@b as varchar(max)) collate Cyrillic_General_100_CI_AI_SC_UTF8;
select @s;
 
declare @t table (s varchar(max) collate Cyrillic_General_100_CI_AI_SC_UTF8);
insert into @t values (@b);
select @s = s from @t;
select @s;

сделал в виде процедуры с буферной таблицей, которая будет обрабатывать все мои строки:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetJson
AS
BEGIN
    SET NOCOUNT ON;
    declare @t table (
                 file_cast varchar(max) collate Cyrillic_General_100_CI_AI_SC_UTF8 null
                 )
    insert into @t(file_cast)       
                 
   SELECT cast(file_stream as varchar(max)) collate Cyrillic_General_100_CI_AI_SC_UTF8
   FROM [FileSearchTest].[dbo].[DocumentSemantics]
   select * from @t
END

Почему-то кодировка снова сбилась..

@invm · 08.05.2021, 15:26

Сообщение от Landser

Почему-то кодировка снова сбилась..

Потому что

T-SQL
1
cast(file_stream as varchar(max)) collate Cyrillic_General_100_CI_AI_SC_UTF8

Я разве так в примере писал?

1. Функция

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create or alter function dbo.fnUTF8toVarchar
(
 @utf8data varbinary(max)
)
returns varchar(max)
as
begin
 declare @result varchar(max);
 
 declare @t table (s varchar(max) collate Cyrillic_General_100_CI_AI_SC_UTF8);
 
 insert into @t (s) values (@utf8data);
 select @result = s from @t;
 
 return @result;
end;
go

2. Запрос

T-SQL
1
select dbo.fnUTF8toVarchar(file_stream) from [FileSearchTest].[dbo].[DocumentSemantics];

Новые блоги и статьи Все статьи Все блоги /
интеграция AnyLogic с самописным REST API и переход на Odoo anaschu 03.07.2026 Успешная интеграция AnyLogic с самописным REST API и переход на промышленную Odoo WMS Сегодня проделал огромный путь от простой симуляции физических процессов до построения полноценной. . .	Поиск всех путей на ориентированном графе. Linux dcc0 02.07.2026 Переработка старого кода из моей статьи. Через несколько переработок от PHP кода к C89 (надеюсь, 89). Но довольно запутанно получилось. Код для Linux. Но если убрать time и то, что с ним. . .	Сам себя обучал rest api anaschu 02.07.2026 Педагогический лайфхак: Почему чистый REST API для ученика намного круче, чем готовые библиотеки Когда мы отказались от капризного JAR-файла AnyLogic и переписали код на стандартный HttpClient,. . .	rest api anylogic - выполнение модели на своём русском сайте anaschu 02.07.2026 Как подружиться с AnyLogic Cloud API, победить провайдеров и развернуться Java-бэкенд в Docker на бесплатном хостинге: Двухдневный лог борьбы Всем привет! Хочу поделиться свежим (и довольно. . .
Где деньги лежат kumehtar 02.07.2026 Это - японская подводная лодка I-52 (тип C2, кодовое имя Momi) вышла из Японии в марте 1944 года с миссией в оккупированную немцами Францию (Лорьян). Это была одна из «Янаги»-миссий по обмену. . .	Krabik для WoW 3.3.5a, многоязычный AmbA 02.07.2026 Допилил бота, думаю что окончательно. Изменения: - добавлена многоязычность - добавлено снятие скриншотов - добавлено поддержание бафов хождения по воде (для жреца, дк и шамана) - и так, по. . .	Алиса нашла кучу ошибок компиляции и запуска в проекте, который без проблем компилировался и запускался))) anaschu 30.06.2026 Я пока посмеюся, но завтра проверю. А вообще интерсно. Дал алисе файл, в котором точно нет ошибок компиляции и запуска, и попросил их найти. Нашла кучу))) Критические ошибки, мешающие компиляции и. . .	сукцессия 16. Общий обзор, в основном что бы другие ии поняли anaschu 29.06.2026 # Передаточный документ: модель микоризной сукцессии (для нового чата) Этот документ предназначен для того, чтобы новый чат Claude мог продолжить работу без необходимости заново разбираться в. . .

@Landser 57 / 7 / 4 Регистрация: 18.04.2009 Сообщений: 429

	SQL Server 2017 работа с JSON 02.05.2021, 18:57. Показов 9117. Ответов 40 Метки нет (Все метки) Доброго времени суток! Использую MS SQL 2017. Имеется каталог с подкаталогами (вложенность - месяц\день, например май\02), в которых находятся .json файлы всегда одинаковой структуры. Требуется pзанести все существующие файлы в БД и потом опрашивать каталог с подкаталогами и вновь появившиеся файлы заносить в БД. Интересует именно опрос подкаталогов и чтение только новых файлов (с чтением и распихиванием по таблицам .json файлов проблем думаю, не возникнет), можно ли это сделать средствами исключительно MSSQL и агента, не прибегая к помощи какого-либо приложения, написанного на яп. Наверное python бы с этим справился, но я его к стыду не знаю, хотя разобраться в работе такого скрипта мог бы (знаю php/laravel) Прошу подсказать. Спасибо 0

@Andrey-MSK 3371 / 2257 / 388 Регистрация: 14.08.2018 Сообщений: 7,646 Записей в блоге: 4
	06.05.2021, 08:24
	Landser, откройте файл JSON в Notepad++ и внизу будет указана его кодировка Миниатюры 1

@invm 3614 / 2135 / 756 Регистрация: 02.06.2013 Сообщений: 5,169
	06.05.2021, 20:12
	Landser, скалярную функцию, а не табличную. 0

@invm 3614 / 2135 / 756 Регистрация: 02.06.2013 Сообщений: 5,169
	06.05.2021, 20:43
	Landser, не нужно файл открывать. Нужно file_stream конвертировать этим способом. В коде чтение файло просто для примера. 0

@Аватар 5393 / 1465 / 513 Регистрация: 31.05.2012 Сообщений: 5,153
	07.05.2021, 11:19
	можно динамически сформировать запрос в строковою переменную и выполнить exec (@sql) 1

SQL Server 2017 работа с JSON

Решение