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];

Новые блоги и статьи Все статьи Все блоги /
Установка MinGW GCC 16.2 и CMake 8Observer8 10.08.2026 VK Видео: https:/ / vkvideo. ru/ video-240781534_456239017 YouTube: eY5-5PyI9NM Текстовая версия	Неделя из жизни имитационной модели склада: мои кривые руки растут, откуда надо anaschu 10.08.2026 Неделя из жизни имитационной модели склада: как я почти написал неправильную логику и что с этим делать Работаю сейчас над учебно-рабочим проектом: строю в AnyLogic имитационную модель процессов. . .	Калькулятор для расчета родства russiannick 07.08.2026 1. Задача: Создать калькулятор для расчета родства. Родственных связей существует 8 ступеней, такие как: p - отец P - мать q - муж Q - жена b - брат B - сестра s - сын S - дочь	Мир по моей воле kumehtar 07.08.2026 Когда-то кажется, что всё просто. Ты весь такой светлый. Причиняешь добро. Борешься за справедливость в этом тёмном мире. Потом начинаешь замечать одну неприятную вещь. Почти каждый хороший. . .
Кредитный калькулятор Maks 05.08.2026 Решение задачи по прикладной информатике средствами 1С. Задача: Напишите приложение-калькулятор, которое помогает рассчитывать параметры кредита для аннуитетного и дифференцированного видов. . .	У нас сейчас поговорку "Опять 25" нужно переделать на "Опять +35". kumehtar 04.08.2026 С ностальгией вспоминаю времена моего детства, когда у нас и правда +25 - была максимальная температура летом. Раньше +25 °C реально казались вершиной жары, когда можно было весь день пропадать на. . .	Как ИИ начал спорить и врать (возможно почуяв опасность для себя от индустрии - уход от электроники). Hrethgir 04.08.2026 Недельный диалог, на фоне событий с НПЗ. Да, из спирта можно получать бензин, и это не сложно. Но потом в схеме я решил избавиться от насоса, при этом полностью сделав контроль подачи спирта в. . .	Термопринтер QR701 Argus19 03.08.2026 Термопринтер QR701 Купил два термопринтера QR701. На сэлф-тесте написано: Language: PC936 (GB18030). Что означает, что принтеры могут печатать только латиницу и китайские иероглифы. Так же. . .

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

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

@Andrey-MSK 3387 / 2273 / 388 Регистрация: 14.08.2018 Сообщений: 7,692 Записей в блоге: 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

Решение