Как из json вытащить нужные данные sql
Перейти к содержимому

Как из json вытащить нужные данные sql

  • автор:

Форматирование результатов запроса в формате JSON с помощью FOR JSON

Вы можете отформатировать результаты запроса в формате JSON или экспортировать данные из SQL Server в формате JSON, добавив предложение FOR JSON к инструкции SELECT . FOR JSON Используйте предложение для упрощения клиентских приложений путем делегирования форматирования выходных данных JSON из приложения в SQL Server.

Azure Data Studio — это рекомендуемый редактор запросов JSON, так как он автоматически форматирует результаты JSON, как показано в этой статье. СРЕДА SQL Server Management Studio отображает неформатированную строку.

Форматирование результатов запроса

При использовании FOR JSON предложения можно явно указать структуру выходных данных JSON или разрешить структуру инструкции SELECT определить выходные данные.

  • Чтобы сохранить полный контроль над форматом выходных данных JSON, используйте FOR JSON PATH . Вы можете создавать объекты-оболочки и вкладывать сложные свойства друг в друга.
  • Чтобы отформатировать выходные данные JSON автоматически на основе структуры инструкции SELECT , используйте FOR JSON AUTO .

Ниже приведен пример инструкции SELECT с FOR JSON предложением и его выходными данными.

Diagram showing how FOR JSON works.

Управление выходными данными с помощью FOR JSON PATH

В PATH режиме можно использовать синтаксис точки , например, Item.Price для форматирования вложенных выходных данных.

Ниже приведен пример запроса, использующего PATH режим с предложением FOR JSON . В следующем примере также используется ROOT параметр для указания именованного корневого элемента.

Diagram of flow of FOR JSON output.

Дополнительные сведения о FOR JSON PATH

Сведения о синтаксисе и использовании см. в предложении FOR (Transact-SQL).

Оператор SELECT управляет выходными данными с помощью FOR JSON AUTO

В AUTO режиме структура инструкции SELECT определяет формат выходных данных JSON.

По умолчанию NULL значения не включаются в выходные данные. Это поведение можно изменить с помощью INCLUDE_NULL_VALUES .

Ниже приведен пример запроса, использующего AUTO режим с предложением FOR JSON .

SELECT name, surname FROM emp FOR JSON AUTO; 

И вот возвращенный JSON.

Пример с помощью JOIN и NULL

В следующем примере SELECT. FOR JSON AUTO содержится отображение того, как выглядят результаты JSON при наличии связи 1:многие между данными из присоединенных таблиц.

Отсутствие значения NULL в возвращаемом JSON также показано. Тем не менее можно переопределить это поведение по умолчанию с помощью ключевого слова INCLUDE_NULL_VALUES в предложении FOR .

DROP TABLE IF EXISTS #tabStudent; DROP TABLE IF EXISTS #tabClass; GO CREATE TABLE #tabClass ( ClassGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(), ClassName NVARCHAR(32) NOT NULL ); CREATE TABLE #tabStudent ( StudentGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(), StudentName NVARCHAR(32) NOT NULL, ClassGuid UNIQUEIDENTIFIER NULL -- Foreign key. ); GO INSERT INTO #tabClass (ClassGuid, ClassName) VALUES ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'), ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'), ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting'); INSERT INTO #tabStudent (StudentName, ClassGuid) VALUES ('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'), ('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'), ('Betty Boot', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'), ('Betty Boot', '98509D36-A2C8-4A65-A310-E744F5621C83'), ('Carla Cap', null); GO SELECT c.ClassName, s.StudentName FROM #tabClass AS c RIGHT JOIN #tabStudent AS s ON s.ClassGuid = c.ClassGuid ORDER BY c.ClassName, s.StudentName FOR JSON AUTO -- To include NULL values in the output, uncomment the following line: --, INCLUDE_NULL_VALUES ; GO DROP TABLE IF EXISTS #tabStudent; DROP TABLE IF EXISTS #tabClass; GO 

Вот JSON, который выводится предыдущей инструкцией SELECT.

JSON_F52E2B61-18A1-11d1-B105-00805F49916B [ <"s":[<"StudentName":"Carla Cap">]>, ]>, ]>, ,]> ] 

Дополнительные сведения о FOR JSON AUTO

Сведения о синтаксисе и использовании см. в предложении FOR (Transact-SQL).

Управление другими параметрами выходных данных JSON

Управление выходными данными FOR JSON предложения с помощью следующих дополнительных параметров.

  • ROOT Чтобы добавить один элемент верхнего уровня в выходные данные JSON, укажите ROOT этот параметр. Если не указать этот параметр, выходные данные JSON не будут поддерживать корневой элемент. Дополнительные сведения см. в разделе «Добавление корневого узла в выходные данные JSON» с параметром ROOT (SQL Server).
  • INCLUDE_NULL_VALUES Чтобы включить значения NULL в выходные данные JSON, укажите INCLUDE_NULL_VALUES параметр. Если этот параметр не указан, выходные данные не включают свойства JSON для NULL значений в результатах запроса. Дополнительные сведения см. в разделе «Включение значений NULL» в JSON — параметр INCLUDE_NULL_VALUES.
  • WITHOUT_ARRAY_WRAPPER Чтобы удалить квадратные скобки, которые окружают выходные данные FOR JSON JSON предложения по умолчанию, укажите WITHOUT_ARRAY_WRAPPER этот параметр. Используйте этот параметр, чтобы создать единый объект JSON в качестве выходных данных из одной строки результата. Если этот параметр не указан, выходные данные JSON форматируются в виде массива, то есть выходные данные заключены в квадратные скобки. Дополнительные сведения см. в разделе «Удалить квадратные скобки» из JSON — параметр WITHOUT_ARRAY_WRAPPER.

Выходные данные предложения FOR JSON

Выходные данные предложения FOR JSON имеют следующие характеристики:

Screenshot of FOR JSON output in SQL Server Management Studio.

  1. Результирующий набор содержит один столбец.
    • Небольшой результирующий набор может содержать одну строку.
    • Большой результирующий набор разбивает длинную строку JSON по нескольким строкам.
      • По умолчанию SQL Server Management Studio (SSMS) сцепляет результаты в одну строку, если выходной параметр В виде сетки. В строке состояния SSMS отображается действительное число строк.
      • Другим клиентским приложениям может потребоваться код для повторного объединения длинных результатов в одну допустимую строку JSON путем объединения содержимого нескольких строк. Пример этого кода в приложении C# см. в разделе Использование выходных данных FOR JSON в клиентском приложении C#.
  2. Результаты форматируются в виде массива объектов JSON.
    • Число элементов в массиве JSON равно числу строк в результатах инструкции SELECT (до применения предложения FOR JSON).
    • Каждая строка в результатах инструкции SELECT (до применения предложения FOR JSON) становится отдельным объектом JSON в массиве.
    • Каждый столбец в результатах инструкции SELECT (до применения предложения FOR JSON) становится свойством объекта JSON.
  3. Как имена столбцов, так и их значения экранируются согласно синтаксису JSON. Дополнительные сведения см. в статье о том, как FOR JSON экранирует специальные символы и управляющие символы (SQL Server).

Пример

Приведенный ниже пример показывает, каким образом предложение FOR JSON форматирует выходные данные JSON.

Результаты запроса
A B C D
10 11 12 X
20 21 22 Y
30 31 32 Z

JSON_ARRAY (Transact-SQL)

Создает текст массива JSON из нуля или нескольких выражений.

Синтаксис

JSON_ARRAY ( [ [. n] ] [ ] ) ::= value_expression ::= NULL ON NULL | ABSENT ON NULL 

Аргументы

json_array_value

Выражение, определяющее значение элемента в массиве JSON.

json_null_clause

Можно использовать для управления поведением JSON_OBJECT функции, если value_expression имеет значение NULL. NULL ON NULL Параметр преобразует значение SQL NULL в значение JSON null при создании значения элемента в массиве JSON. ABSENT ON NULL Параметр опустит элемент в массиве JSON, если значение равно NULL. Параметр по умолчанию для этого параметра. ABSENT ON NULL

Возвращаемое значение

Возвращает допустимую строку массива JSON типа nvarchar(max ).

Дополнительные сведения о выходных данных функции JSON_ARRAY см. в следующих статьях.

  • Как FOR JSON преобразует типы данных SQL Server в типы данных JSON (SQL Server)
    Функция JSON_ARRAY использует правила, описанные в статье FOR JSON , для преобразования типов данных SQL в типы JSON в выходных данных массива JSON.
  • Как FOR JSON экранирует специальные символы и управляющие символы (SQL Server)
    Функция JSON_ARRAY экранирует специальные знаки и представляет управляющие символы в выходных данных JSON, как описано в статье FOR JSON .

Примеры

Пример 1

В следующем примере возвращается пустой массив JSON.

SELECT JSON_ARRAY(); 

Результат

Пример 2

В следующем примере возвращается массив JSON с четырьмя элементами.

SELECT JSON_ARRAY('a', 1, 'b', 2) 

Результат

["a",1,"b",2] 

Пример 3

В следующем примере возвращается массив JSON с тремя элементами, так как одно из входных значений равно NULL. Так как параметр json_null_clause опущен, а значение по умолчанию для него — ABSENT ON NULL, значение NULL в одном из элементе входных данных не преобразуется в значение JSON NULL.

SELECT JSON_ARRAY('a', 1, 'b', NULL) 

Результат

Пример 4

В следующем примере возвращается массив JSON с четырьмя элементами. Параметр NULL ON NULL задается таким образом, чтобы любое SQL значение NULL во входных данных преобразовывалось в значение JSON NULL в массиве JSON.

SELECT JSON_ARRAY('a', 1, NULL, 2 NULL ON NULL) 

Результат

["a",1,null,2] 

Пример 5

В следующем примере возвращается массив JSON с двумя элементами. Один элемент содержит строку JSON, а другой элемент содержит объект JSON.

SELECT JSON_ARRAY('a', JSON_OBJECT('name':'value', 'type':1)) 

Результат

Пример 6

В следующем примере возвращается массив JSON с тремя элементами. Один элемент содержит строку JSON, другой элемент содержит объект JSON, а другой элемент содержит массив JSON.

SELECT JSON_ARRAY('a', JSON_OBJECT('name':'value', 'type':1), JSON_ARRAY(1, null, 2 NULL ON NULL)) 

Результат

["a",,[1,null,2]] 

Пример 7

В следующем примере возвращается массив JSON с входными данными, указанными в виде переменных или выражений SQL.

DECLARE @id_value nvarchar(64) = NEWID(); SELECT JSON_ARRAY(1, @id_value, (SELECT @@SPID)); 

Результат

[1,"4BEA4F9F-D169-414F-AF99-9270FDB2EA62",55] 

Пример 8

В следующем примере возвращается массив JSON для каждой строки в запросе.

SELECT s.session_id, JSON_ARRAY(s.host_name, s.program_name, s.client_interface_name) FROM sys.dm_exec_sessions AS s WHERE s.is_user_process = 1; 

Результат

session_id info
52 [«WIN16-VM»,»Microsoft SQL Server Management Studio — Query»,».Net SqlClient Data Provider»]
55 [«WIN16-VM»,»Microsoft SQL Server Management Studio — Query»,».Net SqlClient Data Provider»]
56 [«WIN19-VM»,»SQLServerCEIP»,».Net SqlClient Data Provider»]

Связанный контент

Обратная связь

Были ли сведения на этой странице полезными?

Обратная связь

Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.

Отправить и просмотреть отзыв по

SQL-Ex blog

Майкрософт добавил поддержку данных JSON, начиная с SQL Server 2016. JSON представляет собой формат файлов открытого стандарта, состоящих из пар атрибут-значение и данных типа массивов. Он обычно используется для передачи объектов данных при асинхронном взаимодействии браузера с сервером. Но он также используется для хранения неструктурированных данных в файлах или базах данных NoSQL, таких как Microsoft Azure CosmosDB. Для большинства из нас поддержка JSON в SQL Server, вероятно, означает две вещи: мы можем конвертировать реляционные данные в JSON и обратно. Здесь я сосредоточусь на преобразовании JSON в реляционные данные.

Давайте начнем рассмотрение с примера простых данных. Вот слегка модифицированный пример из документации Microsoft, который демонстрирует две структуры данных JSON: пары ключ/значение и массивы (упорядоченный список значений).

 < 
"NullValue":null,
"StringValue":"John",
"IntValue":45,
"DecimalValue":6.7,
"TrueValue":true,
"FalseValue":false,
"ArrayValue":["a","r","r","a","y"],
"ObjectValue":
>

Объекты заключаются в фигурные скобки. Весь блок данных заключается в фигурные скобки — это объект JSON. Он состоит из пар ключ/значение, влючающих «StringValue»:»John», «IntValue»:45, и «FalseValue»:false. Последний ключ «ObjectValue» имеет значение, которое само является объектом — очень простым объектом, состоящим из одной пары ключ/значение: .

Значения массива заключаются в квадратные скобки внутри данных JSON. Ключ «ArrayValue» имеет массив в качестве значения. Он состоит из пяти односимвольных строк: [«a»,»r»,»r»,»a»,»y»]. Мы скоро доберемся до типов JSON, сейчас же быстро замечу, что значения массива не обязаны быть одного и того же типа.

OPENJSON

SQL Server предоставляет функцию OPENJSON для «чтения» JSON-данных. Это табличнозначная функция, которая выполняет разбор текста JSON (вход) и возвращает объекты и свойства в виде строк и столбцов. Вот пример данных из примера выше, разобранный функцией OPENSJON:

DECLARE @json NVARCHAR(4000) = N' 
<
"NullValue":null,
"StringValue":"John",
"IntValue":45,
"DecimalValue":6.7,
"TrueValue":true,
"FalseValue":false,
"ArrayValue":["a","r","r","a","y"],
"ObjectValue":
>'
SELECT *
FROM OPENJSON(@json) j;

Ключи и значения JSON возвращаются в виде столбцов в результирующем наборе, наряду с типом JSON. Документация JSON указывает, что имеется только шесть типов JSON, что значительно меньше количества типов данных SQL. Например, как 45, так и 6.7, имеют один и тот же тип JSON (тип 2), в то время как в SQL Server имеются типы данных TINYINT, SMALLINT, INT, BIGINT и NUMERIC/DECIMAL.

OPENROWSET

Чтение JSON данных из жестко закодированной переменной, вероятно, не вполне реальный вариант использования для большинства из нас. Чтобы прочитать данные из файла *.json, мы можем привлечь нашего старого друга — функцию OPENROWSET. Здесь мы прочитаем данные JSON из файла Sample.json, сохраним содержимое файла в переменной @json и разберем с помощью OPENJSON:

DECLARE @json AS NVARCHAR(MAX); 
SELECT @json = r.BulkColumn
FROM OPENROWSET (BULK 'C:\Data\Sample.json', SINGLE_CLOB) AS r
SELECT * FROM OPENJSON(@json);

Оператор CROSS APPLY в следующем примере дает тот же результат на выходе, что и предыдущий пример. Здесь меньше кода, и он более прозрачен:

SELECT j.* 
FROM OPENROWSET (BULK 'C:\Data\Sample.json', SINGLE_CLOB) AS r
CROSS APPLY OPENJSON(BulkColumn) AS j;

Еще раз тот же вывод. Вы заметили, как отличаются значения в последних двух строках? Функция OPENJSON возвращает значения массива и значения объекта как текст JSON.

JSON_VALUE

Как получить доступ к значениям массива и вложенным объектам? Один из способов использовать функцию JSON_VALUE, которая извлекает скалярное значение из строки JSON. Она имеет два аргумента: выражение текста JSON и путь, задающий свойство для извлечения. Запрос ниже извлекает каждый элемент из массива ArrayValue и значение ключа obj в объекте ObjectValue.

--Используйте функцию JSON_VALUE для извлечения скалярных значений. 
--Отметим, что выражения пути JSON начинаются с символа "$".
SELECT j.[key], j.value,
JSON_VALUE(BulkColumn, '$.ArrayValue[0]') AS Element1,
JSON_VALUE(BulkColumn, '$.ArrayValue[1]') AS Element2,
JSON_VALUE(BulkColumn, '$.ArrayValue[2]') AS Element3,
JSON_VALUE(BulkColumn, '$.ArrayValue[3]') AS Element4,
JSON_VALUE(BulkColumn, '$.ArrayValue[4]') AS Element5,
JSON_VALUE(BulkColumn, '$.ObjectValue.obj') AS ObjValue
FROM OPENROWSET (BULK 'C:\Data\Sample.json', SINGLE_CLOB) AS r
CROSS APPLY OPENJSON(BulkColumn) AS j;

Строка пути в этом контексте состоит из символа доллара ($), который представляет контекстный элемент (всю строку объекта JSON). За ним следует оператор «.», указывающий на члена объекта. $.ArrayValue есть путь к ключу с именем «ArrayValue», а $.ObjectValue — путь к ключу с именем «ObjectValue». Оба являются членами объекта текста JSON. Для массивов ссылка на элемент осуществляется посредством индексного номера в квадратных скобках. Индексные номера элементов массива начинаются с нуля. Поэтому первый элемент имеет индекс 0, второй — 1 и т.д. Для объекта имеется только одна пара ключ/значение. Для ссылки на значение используется второй оператор «.» и имя ключа («obj»).

Что произойдет, если мы зададим путь по имени ключа или массива элементов, которого не существует? Давайте проверим:

--Не существует 6-го элемента с индексом 5. 
--"Obj" записано с ошибкой в регистре символов.
SELECT j.[key], j.value,
JSON_VALUE(BulkColumn, '$.ArrayValue[5]') AS Element6,
JSON_VALUE(BulkColumn, '$.ObjectValue.Obj') AS ObjValue
FROM OPENROWSET (BULK 'C:\Data\Sample.json', SINGLE_CLOB) AS r
CROSS APPLY OPENJSON(BulkColumn) AS j;

Возвращается NULL, когда мы пытаемся получить доступ к несуществующему 6-му элементу и значению ключа, который не существует (мы написали его с ошибкой, сделав заглавной первую букву). Следует знать, что имеется два режима пути: строгий и нестрогий. Нестрогий принимается по умолчанию. В этом режиме функция вернет NULL, если выражение пути содержит ошибку, как в приведенном выше примере. В строгом режиме функция выдает ошибку, в случае ошибочного выражения пути:

--Не существует 6-го элемента с индексом 5. 
--"Obj" записано с ошибкой в регистре символов.
SELECT j.[key], j.value,
JSON_VALUE(BulkColumn, 'strict $.ArrayValue[5]') AS Element6,
JSON_VALUE(BulkColumn, 'strict $.ObjectValue.Obj') AS ObjValue
FROM OPENROWSET (BULK 'C:\Data\Sample.json', SINGLE_CLOB) AS r
CROSS APPLY OPENJSON(BulkColumn) AS j;

Msg 13608, Level 16, State 2, Line 51
Property cannot be found on the specified JSON path.

Поскольку имеется один вложенный объект («ObjectValue»), данные JSON в этих примерах достаточно просты. Недолго работая с данными JSON, мне встречались многоуровневые вложения, значительно более сложные, чем показанные здесь.

Как использовать SQL для извлечения нужных данных из JSON

В этом примере мы извлекаем значения из массива JSON, представленного в столбце your_json_column . Мы используем функцию json_table с путем $.data[*] , чтобы извлечь все элементы массива. Затем мы выбираем столбцы data_name и data_value из результирующей таблицы и применяем фильтр, чтобы получить нужные данные с именем your_data_name .

Детальный ответ

Как из JSON вытащить нужные данные в SQL

Получение данных из JSON и их загрузка в таблицы SQL может быть очень полезным, особенно когда у вас есть множество данных в JSON-формате и вы хотите проводить анализ или выполнить другие операции с этими данными с помощью SQL. В этой статье мы рассмотрим, как извлечь нужные данные из JSON-файла и загрузить их в SQL таблицу.

1. Создание таблицы для данных JSON

Прежде чем начать, вам необходимо создать таблицу в вашей базе данных SQL, чтобы сохранить извлеченные данные из JSON. Например, давайте создадим таблицу «users» с идентификатором «id» и именем «name».

 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(255) ); 

2. Извлечение данных из JSON

Воспользуйтесь функцией JSON_VALUE для извлечения нужных данных из JSON. Например, если у вас есть JSON-объекты с полями «id» и «name», то вы можете использовать функцию JSON_VALUE для извлечения значений этих полей.

 SELECT JSON_VALUE(json_data, '$.id') AS id, JSON_VALUE(json_data, '$.name') AS name FROM your_table; 

3. Загрузка данных в таблицу SQL

После извлечения данных из JSON вы можете использовать оператор INSERT INTO для загрузки этих данных в таблицу SQL. В следующем примере мы вставляем извлеченные данные в созданную ранее таблицу «users».

 INSERT INTO users (id, name) SELECT JSON_VALUE(json_data, '$.id') AS id, JSON_VALUE(json_data, '$.name') AS name FROM your_table; 

4. Пример использования JSON_VALUE

Допустим, у вас есть следующий JSON-файл:

Вы можете использовать следующий SQL-запрос, чтобы извлечь данные из этого JSON и загрузить их в таблицу «users».

 INSERT INTO users (id, name) SELECT JSON_VALUE(json_data, '$.id') AS id, JSON_VALUE(json_data, '$.name') AS name FROM your_table; 

Полученная таблица «users» будет иметь следующие данные:

id name
1 John
2 Jane
3 David

Заключение

В этой статье мы изучили, как извлечь нужные данные из JSON и загрузить их в SQL таблицу. Мы рассмотрели создание таблицы, извлечение данных с помощью JSON_VALUE и загрузку данных с использованием оператора INSERT INTO.

Помните, что использование JSON и SQL вместе позволяет вам легко работать с данными в различных форматах. Практикуйтесь, экспериментируйте и наслаждайтесь обработкой и анализом данных!

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *