SQL-Ex blog

JSON (JavaScript Object Notation) является форматом представления данных на базе текста посредством пар ключ-значение. Формат JSON более компактный по сравнению с XML, но JSON предоставляет во многом те же преимущества. Его компактный размер делает более быстрым обмен данными. Подобно XML, JSON легко читается человеком. Способность JSON иерархического представления данных и вложенных иерархических данных с другими парами ключ-значение расширяет его возможность для представления сложных иерархических связей между элементами данных.

Microsoft SQL Server оценил силу JSON, постепенно добавляя все новые функции для импорта, преобразования в реляционные таблицы и экспорта данных JSON из реляционных таблиц в текстовые файлы для использования в приложениях NoSQL. Здесь мы покажем несколько примеров T-SQL для парсинга файлов данных JSON на образце файла из коммерческого приложения.

Эта статья посвящена обработке JSON файлов в SQL Server, опираясь на ранее рассмотренные методы, а также демонстрируя новый подход к извлечению ключевых значений из вложенных пар ключ-значение JSON. Обработка производится встроенными функциями SQL Server. Файл JSON, используемый в примерах, можно получить из загружаемого здесь файла, а также по ссылке к Restful API.

Пример файла JSON для этой статьи

Alpha Vantage API - сервис с бесплатными и премиальными рядами, доступными для скачивания через интернет. Среди рядов исторических данных имеются данные о стоимости и объемах акций, выручке и доходах компании, обменных курсах иностранной валюты и криптовалюты, а также экономические показатели. Документация включает ссылку для загрузки бесплатных ежедневных цен на тикер IBM, которая не требует специального ключа и доступна без оплаты. Ссылка позволяет получить информацию о последних 100 торговых днях в браузере на вашем компьютере. Вы также можете получить точно такой же файл JSON, используемый здесь в демонстрационных целях, из файла загрузки.

Здесь приводится пара скриншотов приложения Notepad++, в которых показаны строки данных JSON из начала и конца этого файла. Notepad++ - это свободно распространяемое приложение, похожее на Microsoft Notepad, но с более продвинутой функциональностью для работы с текстовыми файлами, включая файлы данных JSON. Вывод Notepad++ подсвечивает связи между парами ключ-значение в файле JSON.

• В этом файле есть два главных объекта JSON - один называется Meta Data, а другой - Time Series. (Daily).


• В эти два главных объекта вложены другие пары ключ-значение:
  
  
  • Объект Meta Data содержит пары ключ-значение, описывающие типы данных, имеющихся в файле JSON.
  
  
  • Объект Time Series (Daily) содержит ряды дат, начинающихся с 7 июля 2022 и продолжающихся до 25 ноября 2022.
  
  
  • Даты следуют в убывающем порядке от наиболее свежей даты до самой старой.
  
  
  • Каждая дата служит ключом для пяти вложенных пар ключ-значение, начинающихся с цены открытия в день торгов и заканчивая объемом акций, обмененных в день торгов.

Открытие и вывод файла JSON в SQL Server

Вы можете использовать функцию openrowset для доступа к тексту JSON либо в varchar, либо в nvarchar формате для импорта в столбец таблицы SQL Server. Затем вы можете присвоить значения столбца таблицы локальной переменной. Далее вы можете открыть и вывести с помощью функции openjson пары ключ-значение верхнего уровня из файла JSON, скопированного в локальную переменную. Следующий скрипт реализует эти шаги.

• Функция openrowset появляется в предложении FROM первого оператора SELECT.
  • Требуется оператор массовой вставки (bulk)
    • Аргумент оператора bulk задает путь к файлу с данным JSON. Аккаунт, под которым выполняется скрипт, должен иметь разрешение на доступ к файлу JSON.
    
    
    • Второй аргумент указывает формат символов в файле JSON.
      • SINGLE_CLOB используется для текста, отформатированного как символы varchar, который используется тут в примерах.
      
      
      • SINGLE_NCLOB является альтернативным значением аргумента для текста, отформатированного как символы nvarchar, что не демонстрируется здесь.
    
    
  • Результирующий набор функции openrowset возвращается как bulkcolumn, который присваивается локальной переменной @json.
  
  
  • Также объявляется пара дополнительных локальных переменных (@jsonMetaDataObject и @jsonTimeSeriesDailyObject) для использования в последующих примерах в том же самом файле скрипта T-SQL. Эти примеры появятся ниже в этой статье.


• Функция openjson выполняет парсинг содежимого файла данных JSON в локальной переменной @json. Этот пример извлекает данные JSON из файла. Возможно также извлечь данные JSON из локальной переменной, объявленной как строка. Более подробно об использовании функции openjson с различными форматами данных JSON изложено здесь. Знакомство с этой статьей может быть особенно полезно, если у вас небольшой опыт работы с данными в формате JSON.

-- импорт файла json в локальную переменную @json

-- Вывод внешних пар ключ-значение с форматом по умолчанию

declare 

 @json varchar(max)

,@jsonMetaDataObject varchar(max)

,@jsonTimeSeriesDailyObject varchar(max)

select @json= bulkcolumn

from openrowset(bulk 'C:\DataScienceSamples\json_files_SS_19\daily_prices_ibm.json', SINGLE_CLOB) importfile

select *

from openjson(@json)

Следующий скриншот показывает вывод этого скрипта.

На предыдущих скриншотах видно, что в представлении файла JSON в NotePad++ 711 строк. Однако следующий скриншот показывает, что имеется только два ключа внешнего уровня с именами Meta Data и Time Series (Daily) в файле JSON. Все другие пары ключ-значение в файле JSON находятся внутри одного из этих двух внешних ключей, или объект в одном из этих ключей. Эти ключи представлены в формате по умолчанию на выходе функции openjson.

• Формат по умолчанию представляет данные в трех столбцах:
  
  
  • имя ключа
  
  
  • значение ключа
  
  
  • тип ключа


• Первый ключ внешнего уровня называется Meta Data
  • Если вы проверите символы в столбце значения ключа, то увидите, что они соответствуют начальным символам файла текста JSON, выведенных на скриншотах предыдущего раздела.
  
  
  • JSON поддерживает шесть уникальных типов данных. Пятый тип данных (представленный под номером 5 в столбце типов) означает объектный тип данных, который может содержать одну или более вложенных пар ключ-значение. Имеется пять пар ключ-значение, вложенных в тип ключа Meta Data:
    
    
    • Первый вложенный ключ называется 1. Information
    
    
    • Второй вложенный ключ называется 2. Symbol
    
    
    • и так далее для остальных трех вложенных ключей


• Второй ключ внешнего уровня называется Time Series (Daily). Этот ключ внешнего уровня включает два уровня вложенных ключей:
  • Первый вложенный уровень относится к идентификаторам даты торгов. Например, самая свежая дата торгов - 2022-11-25. Самая старая дата торгов - 2022-07-07.
  
  
  • Для каждого ключа даты торгов имеется пять вложенных пар ключ-значение. Вот их имена:
    • 1. open
    
    
    • 2. high
    
    
    • 3. low
    
    
    • 4. close
    
    
    • 5. volume


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

Извлечение пар ключ-значение из объекта Meta Data

Предполагается, что следующий фрагмент скрипта выполняется сразу после предыдущего в том же самом файле скрипта. В нем имеется три оператора SELECT:

• Первый оператор SELECT присваивает значение локальной переменной @jsonMetaDataObject. Этой локальной переменной присваиваются пары ключ-значение, вложенные в объект Meta Data. Напомню, что этот объект является одним из двух объектов верхнего уровня в исходном файле JSON, который назывался daily_prices_ibm.json в предыдущем разделе.
  • Имеется два ключа в локальной переменной @json, которая является аргументом функции openjson в следующем фрагменте скрипта.
  
  
  • Выводу функции openjson присваивается псевдоним x в первом операторе SELECT.
    • x.[Key] ссылается на имена ключей в @json
    
    
    • Критерий в предложении WHERE явно задает имя ключа Meta Data
    
    
    • x.[Value] ссылается на значения ключа в @json, которые берутся из объекта Meta Data
    
    
    • x.[Value] в списке предложения SELECT возвращает значение из пары ключ-значение Meta Data; возвращаемое значение является вложенным набором пар ключ-значение
    В первом операторе SELECT вложенные пары ключ-значение Meta Data присваиваются локальной переменной @jsonMetaDataObject
  
  
  • Второй оператор SELECT выводит пары ключ-значение в локальную переменную @jsonMetaDataObject
  
  
  • Третий оператор SELECT перестраивает пары ключ значение Meta Data в традиционный формат реляционного набора данных с именами ключей (y.key) в первом столбце, и значений ключа (y.value) в соответствующих строках во втором столбце.
  
  

  -- извлекаем только значения данных json для объекта Meta Data
  
  -- в @jsonMetaDataObject
  
  select @jsonMetaDataObject = x.[Value]
  
  FROM openjson(@Json) x 
  
  where x.[Key] = 'Meta Data'
  
  -- выводим @jsonMetaDataObject
  
  select @jsonMetaDataObject [Meta Data object key-name pairs]
  
  -- выводим пары имя ключа и значения ключа в @jsonMetaDataObject
  
  select y.[key] [Meta Data key name], y.value  [Meta Data key value] 
  
  from openjson(@jsonMetaDataObject) y

  
  
  Вот вывод скрипта в этом фрагменте; выходные данные предыдущего скрипта продолжают вывод скрипта в предыдущем фрагменте.
  
  
  • На первой панели показаны два ключа в локальной переменной @json с форматом по умолчанию из функции openjson.
  
  
  • На второй панели показаны вложенные пары ключ-значение в виде строки внутри локальной переменной @jsonMetaDataObject. Форматирование JSON заключает в фигурные скобки ({}) вложенные пары ключ-значение.
  
  
  • На третьей панели показы пары ключ-значение в традиционном формате реляционного набора данных.
  
  
  
  

  Извлечение пар ключ-значение из объекта Time Series (Daily)

  
  Как и в предыдущем разделе, где показано, как скопировать объект Meta Data из локальной переменной @json, вы так же можете скопировать объект Time Series (Daily) из локальной переменной @json. В то время как имеется только один экземпляр объекта Meta Data, существует множество экземпляров объекта Time Series (Daily). Имена ключей в экземплярах объекта Time Series (Daily) являются уникальными по значению их даты торгов. Кроме того, экземпляр объекта Time Series (Daily) связан со своим собственным набором вложенных пар ключ-значение. Следовательно, если вы хотите отслеживать значения закрытия по датам торгов, вам необходимо извлекать пару ключ-значение закрытия из каждого экземпляра объекта Time Series.
  
  Следующий фрагмент скрипта демонстрирует один подход к решению этой задачи. Этот скрипт необходимо выполнять в том же самом скриптовом файле, совместно с двумя предыдущими фрагментами. Этому есть две важные причины:
  
  
  • Фрагмент зависит от локальной переменной @json, которая получает значение в предыдущем фрагменте.
  
  
  • Фрагмент присваивает значение локальной переменной (@jsonTimeSeriesDailyObject), которая была объявлена в предыдущем фрагменте.
  
  Вот новый фрагмент скрипта для парсинга значений закрытия для каждой даты торгов в объекте Time Series (Daily).
  
  
  • Первый оператор SELECT извлекает объект Time Series (Daily) из локальной переменной @json; извлеченный объект сохраняется в локальной переменной @jsonTimeSeriesDailyObject.
  
  
  • Второй оператор SELECT выводит вложенные пары ключ-значение для каждой даты торгов в локальной переменной @jsonTimeSeriesDailyObject.
    • В содержимом для этой статьи имеется 100 дат торгов.
    
    
    • Список SELECT ссылается и на z.[key], и на z.[value], которые являются значениями столбцов для каждой из 100 строк торгов в результирующем наборе.
    
    
    • Источником для оператора SELECT является объект @jsonTimeSeriesDailyObject, который имеет алиас z.
  
  
  • Третий оператор SELECT показывает, как использовать функцию SQL Server substring для извлечения значений закрытия для последовательных дат торгов. Имеются специализированнные функции JSON для извлечения значения из пары ключ-значение. Однако функция substring особенно проста при использовании в этих обстоятельствах. Использовать функцию substring допустимо, поскольку файлы JSON состоят из текстовых значений.
  
  

  -- извлечение значений данных json только для объекта Time Series (Daily)
  
  -- в @jsonTimeSeriesDailyObject
  
  select @jsonTimeSeriesDailyObject = z.[Value]
  
  FROM openjson(@Json) z
  
  where z.[Key] = 'Time Series (Daily)'
  
  -- вывод пар имени ключа и значения ключа в объекте Time Series (Daily)
  
  select z.[key] [Time Series (Daily) key name], z.value  [Time Series (Daily) key value]
  
  from openjson(@jsonTimeSeriesDailyObject) z
  
  -- ключ и значение ключа, но значение ключа является объектом json
  
  -- использование функции substring для извлечения цены закрытия 
  
  -- (начиная в столбце 136 с длиной 8 столбцов)
  
  select 
  
   z.[key] [Time Series (Daily) key name]
  
  ,substring(z.[value],136,8) [close]
  
  from openjson(@jsonTimeSeriesDailyObject) z

  
  Следующий экран показывает выборку из вывода второго оператора SELECT в предыдущем фрагменте скрипта.
  
  
  • Первый столбец имеет имя ключа Time Series (Daily).
    • Значения столбца стартуют с 2022-11-25.
    
    
    • Выборка показывает вывод для следующих четырех строк.
    
    
    • Значением столбца для сотой строки вывода, которая не попала в выборку, является 2022-07-07.
  
  
  • Второй столбец имеет имя значения ключа Time Series (Daily).
    • Для каждого значения столбца имеется пять наборов пар ключ-значение.
    
    
    • Значения закрытия начинаются в столбце 136 и продолжаются от отсюда на 8 столбцов всего; эти параметры служат аргументами функции substring для извлечения значений закрытия.
    
    
  
  
  
  Следующий скрншот демонстрирует аналогичную выборку из третьего оператора SELECT в предыдущем скрипте. Эти результаты представляют собой также выборку для пяти последовательных дат проведения торгов.
  
  
  • Значение закрытия для 2022-11-25 отвечает соответствующему значению закрытия в предыдущей выборке из результирующего набора.
  
  
  • Значения закрытия со второй по пятую строку ниже также отвечают соответствующим значениям закрытия в предыдущей выборке из результирующего набора.
  
  
  
  

  Далее

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

  Ссылки по теме

  
  
  1. Парсинг данных типа JSON в SQL Server
  
  
  2. Расщепление строк при помощи OPENJSON
  
  
  3. OPENJSON, явная схема и тип данных
  
  
  4. OPENJSON и CROSS APPLY
  
  
  5. OPENJSON: получение данных и PATH - часть 2