Json файлы как база данных
В данном посте я хочу рассказать об использовании json файлов как базы данных. Использование именно формата json удобно тем, что информация в данном формате — это Javascript массивы и объекты, к которым легко можно получить доступ с клиентской части web приложения.
Любая база данных включает в себя набор функций для записи, чтения, обновления и удаления данных из таблиц. В данном случае это будет класс с набором методов для управления базой.
- $path — путь от корня до папки, в которой будут храниться файлы. По умолчанию $path = $_SERVER[«DOCUMENT_ROOT»].'/jdb/'.
Управление базой данных
Ниже представлен набор методов и примеров их использования.
Create
Создание таблицы.
- $keys — массив ключей таблицы и их характеристик. Поддерживается auto_increment и default.
Select
Выборка данных из таблицы.
- $select — массив или строка, содержащие ключи для выборки.
- $table — название таблицы из которой будет происходить выборка данных.
- $rules — массив, содержащий параметры where, order и limit.
Insert
Вставка данных в таблицу.
- $data — ассоциативный массив данных для вставки в таблицу.
Update
Обновление данных в таблице.
- $table — название таблицы.
- $data — ассоциативный массив с данными для обновления.
- $where — ассоциативный массив с данными для выбора нужной записи для обновления.
Delete
Удаление записей из таблицы.
- $table — название таблицы.
- $where — ассоциативный массив данных для выборки нужной записи для удаления.
Drop
Удаление таблицы.
Alter
Добавление и удаление ключей из таблицы.
- $table — название таблицы.
- $todo — выполняемое действие. Может быть add или drop.
- $keys — строка или ассоциативный массив данных для удаления или добавления ключей.
Truncate
Полностью очистить таблицу.
Определение ошибок в запросах
Все перечисленные методы возвращают результат запроса, если он был удачен, и false если нет. Метод "status" позволяет узнать статус предыдущего запроса.
- 0 — All ok.
- 101 — Table already exists.
- 102 — Table doent exist.
- 103 — Unkonw property.
- 201 — Key already exist.
- 202 — Keys doesnt exsit.
Вспомогательные методы
Last_insert_id
Часто бывает, что нужно узнать id добавленной записи в базу данных. Для этого нужно воспользоваться методом last_insert_id.
Exist
Проверяет существование таблицы. Возвращает true или false;
Пример:
Так же для удобства есть возможность использовать sql синтаксис для выполнения запросов. Для этого используется другой класс.
- $path — путь от корня до папки, в которой хранятся файлы. По умолчанию $path = $_SERVER[«DOCUMENT_ROOT»].'/jdb/'.
Так как в php есть возможность создавать функции и классы с одним именем, то можно применить следующую конструкцию:
Дополнительные запросы
Узнать статус запроса:
Узнать id добавленной записи в базу данных:
Узнать существует таблица или нет:
Сравнение Jsondb с MySql
Конечно, тут и сравнивать то нечего, базы данных для того и созданы, чтобы хранить данные и быстро получать к ним доступ. Но все же интересно.
Для сравнивания быстродействия и количества используемой памяти, был выполнен ряд тестов для методов insert, update и select.
JSON — это популярный формат текстовых данных, который используется для обмена данными в современных веб- и мобильных приложениях. Кроме того, JSON используется для хранения неструктурированных данных в файлах журналов или базах данных NoSQL, таких как Microsoft Azure Cosmos DB. Многие веб-службы REST возвращают результаты в формате текста JSON или принимают данные в формате JSON. Например, большинство служб Azure, таких как поиск Azure, служба хранилища Azure и Azure Cosmos DB, имеют конечные точки REST, которые возвращают или принимают JSON. JSON — это также основной формат обмена данными между веб-страницами и веб-серверами с помощью вызовов AJAX.
JSON as a bridge between NoSQL and relational worlds (JSON как мост между NoSQL и реляционными решениями)
Вот пример текста JSON:
С помощью встроенных функций и операторов SQL Server вы можете выполнять указанные ниже действия с текстом JSON.
- Синтаксический анализ текста JSON, а также считывание и изменение значений.
- Преобразования массивов объектов JSON в табличный формат.
- Выполнение любого запроса Transact SQL к преобразованным объектам JSON.
- Форматирование результатов запросов Transact-SQL в формате JSON.
Основные возможности JSON, предоставляемые SQL Server и базой данных SQL
В следующих разделах описываются основные возможности, предоставляемые SQL Server со встроенной поддержкой JSON. Посмотрите видео, чтобы понять, как использовать функции и операторы JSON:
SQL Server 2016 and JSON Support (SQL Server 2016 и поддержка JSON)
Извлечение значений из текста JSON и их использование в запросах
Если у вас есть текст JSON, который хранится в таблицах базы данных, вы можете прочитать или изменить значения в тексте JSON с помощью следующих встроенных функций.
-
проверяет наличие в строке допустимых данных JSON. извлекает из строки JSON скалярное значение. извлекает из строки JSON объект или массив. изменяет значение в строке JSON.
Пример
В следующем примере представлен запрос, в котором используются реляционные данные и данные JSON (хранятся в столбце jsonCol ) из таблицы:
Приложения и средства не видят разницы между значениями, взятыми из скалярных столбцов таблицы, и значениями, взятыми из столбца JSON. Значения из текста JSON можно использовать в любой части запроса Transact-SQL (включая предложения WHERE, ORDER BY или GROUP BY, агрегатные операции с окнами и т. д.). Для ссылок на значения в тексте JSON функции JSON используют синтаксис типа JavaScript.
Изменение значений JSON
Если вам нужно изменить части текста JSON, используйте функцию JSON_MODIFY (Transact-SQL), чтобы обновить значение свойства в строке JSON и получить обновленную строку JSON. В следующем примере показано, как изменить значение свойства в переменной, которая содержит данные в формате JSON.
Результаты
Преобразование коллекций JSON в набор строк
Для выполнения запросов JSON в SQL Server никакой особый язык запросов не требуется. Для запроса данных JSON можно использовать стандартные инструкции T-SQL. Если вам нужно создать запрос или отчет по данным JSON, вы можете легко преобразовать данные JSON в строки и столбцы, вызвав функцию набора строк OPENJSON. Дополнительные сведения см. в статье Преобразование данных JSON в строки и столбцы с помощью функции OPENJSON (SQL Server).
В следующем примере вызывается функция OPENJSON, и массив объектов, хранящийся в переменной @json , преобразуется в набор строк, который можно запросить с помощью стандартной инструкции SQL SELECT:
Результаты
| ID | firstName | lastName | age | dateOfBirth |
|---|---|---|---|---|
| 2 | Джон | Смит | 25 | |
| 5 | Джейн | Смит | 2005-11-04T12:00:00 |
OPENJSON преобразует массив объектов JSON в таблицу, где каждый объект представлен в отдельной строке, а пары "ключ —значение" возвращаются как ячейки. К выходным данным применяются следующие правила.
- OPENJSON преобразует значения JSON в типы, указанные в предложении WITH.
- OPENJSON может обрабатывать плоские пары ключ:значение, а также вложенные объекты с иерархической организацией.
- Все поля в тексте JSON возвращать необязательно.
- Если значений JSON нет, OPENJSON возвращает значения NULL.
- Путь, обозначенный после указания типа, можно использовать для ссылки на вложенное свойство или просто для ссылки на свойство с другим именем.
- Необязательный префикс strict в пути означает, что значения указанных свойств должны присутствовать в тексте JSON.
Документы JSON могут содержать вложенные элементы и иерархические данные, которые невозможно напрямую сопоставить со стандартными реляционными столбцами. В этом случае можно выполнить сведение иерархии JSON посредством соединения родительской сущности с вложенными массивами.
В следующем примере второй объект в массиве содержит вложенный массив, представляющий навыки сотрудника. Каждый вложенный объект может быть проанализирован с помощью дополнительных вызовов функции OPENJSON :
Массив skills возвращается в первой функции OPENJSON в качестве исходного фрагмента текста JSON и передается в другую функцию OPENJSON с использованием оператора APPLY . Вторая функция OPENJSON анализирует массив JSON и возвращает строковые значения в виде единого набора строк столбцов, который будет соединен с результатами первой функции OPENJSON .
Результат этого запроса показан в следующей таблице:
Результаты
Функция OUTER APPLY OPENJSON соединит сущности первого уровня с вложенным массивом и вернет набор результатов, преобразованный в плоскую структуру. Из-за выполнения операции JOIN вторая строка будет повторяться для каждого навыка.
Преобразование данных SQL Server в JSON или экспортирование JSON
Преобразование данных из Azure Synapse Analytics в формат JSON или экспорт в формате JSON не поддерживается.
Данные из SQL Server в формате JSON или результаты запросов SQL можно отформатировать как JSON, добавив предложение FOR JSON к инструкции SELECT . FOR JSON позволяет делегировать форматирование выходных данных JSON из клиентских приложений в SQL Server. Дополнительные сведения см. в разделе Форматирование результатов запроса как JSON с помощью предложения FOR JSON (SQL Server).
В следующем примере используется режим PATH с предложением FOR JSON.
Это FOR JSON отформатирует результаты SQL как текст JSON, который можно предоставить любому приложению, которое понимает JSON. Параметр PATH содержит псевдонимы, разделенные точками, в предложении SELECT для вложения объектов в результаты запросов.
Результаты
Варианты использования данных JSON в SQL Server
Поддержка JSON в SQL Server и базе данных SQL Azure позволяет объединить принципы NoSQL и реляционных баз данных. Вы можете легко преобразовывать реляционные данные в частично структурированные и наоборот. Однако JSON не заменяет существующие реляционные модели. Ниже приведены некоторые конкретные варианты использования с преимуществами поддержки JSON в SQL Server и базе данных SQL.
Упрощение сложных моделей данных
Рассмотрите возможность денормализации модели данных с полями JSON вместо нескольких дочерних таблиц.
Хранение данных розничной торговли и электронной коммерции
Храните сведения о продуктах, используя в денормализованной модели множество атрибутов переменных для обеспечения гибкости.
Обработка данных журнала и данных телеметрии
Загружайте, запрашивайте и анализируйте данные журнала, хранящиеся в виде JSON-файлов, используя все возможности языка Transact-SQL.
Сохранение частично структурированных данных Интернета вещей
Чтобы проанализировать данные Интернета вещей в режиме реального времени, загружайте входящие данные непосредственно в базу данных, а не размещайте их в месте хранения.
Упрощение разработки REST API
Легко преобразовывайте реляционные данных из базы данных в формат JSON, используемый интерфейсами REST API, которые поддерживают ваш веб-сайт.
Объединение реляционных данных и данных JSON
SQL Server предоставляет гибридную модель для хранения и обработки реляционных данных и данных JSON с использованием стандартного языка Transact-SQL. Вы можете формировать коллекции документов JSON в таблицах, устанавливать отношения между ними, комбинировать строго типизированные скалярные столбцы, которые хранятся в таблицах с гибкими парами "ключ —значение", хранящимися в столбцах JSON, и запрашивать скалярные значения и значения JSON в одной таблице или нескольких с использованием полного Transact-SQL.
Текст JSON обычно хранится в столбцах VARCHAR или NVARCHAR и индексируется как обычный текст. Любая функция или компонент SQL Server, которые поддерживают текст, поддерживают и JSON, поэтому в обмене данных между JSON и другими компонентами SQL Server нет практически никаких ограничений. JSON можно хранить во временных таблицах или в таблицах в памяти, применять к тексту JSON предикаты безопасности на уровне строк и т. д.
Если у вас есть рабочие нагрузки, в которых присутствует только JSON, и вы хотите использовать для них язык запросов, специально предназначенный для обработки документов JSON, рассмотрите Microsoft Azure Cosmos DB.
Рассмотрим несколько способов применения встроенной поддержки JSON в SQL Server.
Хранение и индексирование данных JSON в SQL Server
JSON — это текстовый формат, следовательно, документы JSON могут храниться в столбцах NVARCHAR в Базе данных SQL. Тип NVARCHAR поддерживается во всех подсистемах SQL Server, поэтому вы можете помещать документы JSON в таблицы с индексами CLUSTERED COLUMNSTORE, оптимизированные для памяти таблицы, а также во внешние файлы, которые могут считываться с помощью OPENROWSET или PolyBase.
Дополнительные сведения о возможностях хранения, индексирования и оптимизации данных JSON в SQL Server, см. в следующих статьях.
Загрузка файлов JSON в SQL Server
Сведения, которые хранятся в файлах, можно отформатировать как стандартный JSON или JSON с разбивкой на строки. SQL Server может импортировать содержимое файлов JSON, проанализировать его с помощью функций OPENJSON или JSON_VALUE и загрузить их в таблицы.
Если документы JSON хранятся в локальных файлах, на общих сетевых дисках или в хранилище файлов Azure, доступном для SQL Server, данные JSON можно загрузить в SQL Server с помощью массового импорта.
Если файлы JSON с разбивкой на строки хранятся в хранилище BLOB-объектов Azure или в файловой системе Hadoop, вы можете загрузить текст JSON с помощью Polybase, проанализировать его в коде Transact-SQL и загрузить в таблицы.
Импорт данных JSON в таблицы SQL Server
Если требуется загрузка данных JSON из внешней службы в SQL Server, можно импортировать данные в SQL Server с помощью OPENJSON вместо того, чтобы использовать синтаксический анализ данных на уровне приложения.
Анализ данных JSON с помощью запросов SQL
Если вам нужно отфильтровать или вычислить данные JSON для целей отчетности, JSON можно преобразовать в реляционный формат с помощью OPENJSON. После подготовьте отчеты, используя стандартный Transact-SQL и встроенные функции.
В одном и том же запросе можно использовать стандартные столбцы таблицы и значения из текста JSON. Для повышения эффективности запроса можно добавить индексы в выражение JSON_VALUE(Tab.json, '$.Status') . Дополнительные сведения см. в разделе Индексирование данных JSON.
Возврат данных из таблицы SQL Server в формате JSON
Если у вас есть веб-служба, которая получает данные с уровня базы данных и возвращает их в формате JSON, либо платформы или библиотеки JavaScript, которые принимают данные в формате JSON, вы можете отформатировать выходные данные JSON прямо в запросе SQL. Вместо написания кода или включения библиотеки для преобразования результатов табличных запросов и последующей сериализации объектов в формате JSON вы можете делегировать форматирование в SQL Server с помощью FOR JSON.
Например, можно сформировать выходные данные JSON, совместимые со спецификацией OData. Веб-служба ожидает запрос и ответ в указанном ниже формате.
URL-адрес OData представляет запрос столбцов ProductID и ProductName для продукта с ID 1. FOR JSON можно использовать для форматирования выходных данных для SQL Server.
Выходные данные этого запроса — текст JSON, который полностью соответствует спецификации OData. Форматирование и экранирование выполняются SQL Server. SQL Server может также выдать результаты запроса в любом формате, таком как OData JSON или GeoJSON.
Проверка встроенной поддержки JSON с образцом базы данных AdventureWorks
Чтобы получить образец базы данных AdventureWorks, скачайте по крайней мере файл базы данных и примеры сценариев с GitHub.
После восстановления образца базы данных в экземпляре SQL Server распакуйте файлы образца и откройте файл JSON Sample Queries procedures views and indexes.sql в папке JSON. Выполните сценарии в этом файле, чтобы переформатировать некоторые данные как данные JSON, протестируйте образцы запросов и отчеты по данным JSON, индексируйте данные JSON, а затем импортируйте и экспортируйте JSON.
Вот, что делать с помощью скриптов, включенных в файл.
Выполнить денормализацию существующей схемы для создания столбцов данных JSON.
Сохранить информацию из SalesReasons , SalesOrderDetails , SalesPerson , Customer и других таблиц, содержащих информацию о заказах на продажу, в столбцах JSON в таблице SalesOrder_json .
Сохранить информацию из таблиц EmailAddresses / PersonPhone в таблице Person_json в качестве массивов объектов JSON.
Создайте процедуры и представления для запроса данных JSON.
Проиндексируйте данные JSON. Создайте индексы свойств JSON и полнотекстовые индексы.
Импортируйте и экспортируйте JSON. Создать и запустить процедуры для экспорта содержимого таблиц Person и SalesOrder в качестве результатов в формате JSON, а затем импортировать и обновить таблицы Person и SalesOrder , используя входные данные JSON.
Выполните примеры запросов. Выполните несколько запросов, вызывающих хранимые процедуры и представления, которые были созданы при выполнении шагов 2 и 4.
Очистите скрипты. Не выполняйте это действие, если хотите оставить хранимые процедуры и представления, которые были созданы при выполнении шагов 2 и 4.
Дополнительные сведения о JSON в SQL Server и базе данных SQL Azure
Видео Майкрософт
Наглядные инструкции по встроенной поддержке JSON в SQL Server и базе данных SQL Azure см. в следующем видео:
Using JSON in SQL Server 2016 and Azure SQL Database (Использование JSON в SQL Server 2016 и базе данных SQL Azure)
Обратите внимание, что любая база данных будет принимать документы JSON как одностроковый BLOB-объект. Однако MySQL и PostgreSQL поддерживают проверенные данные JSON в реальных парах ключ / значение, а не в базовой строке.
Нормализация — это метод, используемый для оптимизации структуры базы данных. Правило первой нормальной формы (1NF) гласит, что каждый столбец должен содержать одно значение, что явно нарушается при хранении многозначных документов JSON.
Если у вас есть четкие требования к реляционным данным, используйте соответствующие однозначные поля. JSON следует использовать экономно в крайнем случае. Поля значений JSON не могут быть проиндексированы, поэтому не используйте его в столбцах, которые регулярно обновляются или просматриваются. Кроме того, меньше клиентских приложений поддерживают JSON, а технология новее, поэтому она может быть менее стабильной, чем другие типы.
Тем не менее, есть хорошие варианты использования JSON для редко заполненных данных или настраиваемых атрибутов.
Создать таблицу с полем JSON
Рассмотрим магазин по продаже книг. У всех книг есть идентификатор, ISBN, название, издатель, количество страниц и другие четкие данные о взаимоотношениях. Предположим, вы хотите добавить к каждой книге любое количество тегов категорий. Вы можете добиться этого в SQL, используя:
- тег таблица, в которой хранится имя каждого тега с уникальным идентификатором, и
- tagmap стол со многими ко многим записям отображения книг идентификаторов в идентификаторы тегов
Это будет работать, но это громоздко и требует значительных усилий для незначительной функции. Следовательно, вы можете определить поле тегов JSON в таблице book вашей базы данных MySQL :
Обратите внимание, что столбцы JSON не могут иметь значение по умолчанию, использоваться в качестве первичного ключа, использоваться в качестве внешнего ключа или иметь индекс. Вы можете создавать вторичные индексы для сгенерированных виртуальных столбцов, но проще и практичнее сохранять значение в отдельном поле, если индексы требуются.
Добавление данных JSON
Целые документы JSON можно передавать в операторах INSERT или UPDATE. Например, наши книжные теги могут быть переданы в виде массива (внутри строки):
JSON также можно создать с помощью этих:
- Функция JSON_ARRAY (), создающая массивы. Например:
-- returns [1, 2, "abc"]:
SELECT JSON_ARRAY ( 1 , 2 , 'abc' ) ;
- Функция JSON_OBJECT (), которая создает объекты. Например:
Функция JSON_TYPE () позволяет проверять типы значений JSON. Он должен возвращать OBJECT, ARRAY, скалярный тип (INTEGER, BOOLEAN и т. Д.), NULL или ошибку. Например:
Функция JSON_VALID () возвращает 1, если JSON действителен, или 0 в противном случае:
Попытка вставить недопустимый документ JSON вызовет ошибку, и вся запись не будет вставлена / обновлена.
Поиск данных JSON
Функция JSON_CONTAINS () принимает документ JSON, в котором выполняется поиск, и другой документ для сравнения. Он возвращает 1, когда найдено совпадение. Например:
Аналогичная функция JSON_SEARCH () возвращает путь к заданному совпадению или NULL, если совпадения нет. Он передал документ JSON, в котором выполняется поиск, ’one’чтобы найти первое совпадение или ’all’найти все совпадения, и строку поиска (где %соответствует любому количеству символов и _соответствует одному символу идентично LIKE). Например:
Пути JSON
Путь JSON нацелен на значения и может использоваться для извлечения или изменения частей документа JSON. Функция JSON_EXTRACT () демонстрирует это, извлекая одно или несколько значений:
Все определения пути начинаются с символа, $за которым следуют другие селекторы:
- точка, за которой следует имя, например $.website
- [N] где N — позиция в массиве с нулевым индексом
- .[*]подстановочные оценивает все элементы объекта
- [*]подстановочные оценивает все элементы массива
- в prefix**suffixшаблонные принимает значение всех путей, которые начинаются с префикса имени и в конце с именем суффикс
Следующие примеры относятся к следующему документу JSON:
- $.a возвращается 1
- $.c возвращается [3, 4]
- $.c[1] возвращается 4
- $.d.e возвращается 5
- $**.e возвращается [5]
Извлечение путей JSON в запросах
Вы можете извлечь имя и первый тег таблицы своей книги, используя запрос:
Для более сложного примера предположим, что у вас есть таблица пользователей с данными профиля JSON. Например:
| я бы | имя | профиль |
| 1 | Крейг | |
| 2 | SitePoint |
Вы можете извлечь имя Twitter, используя путь JSON. Например:
Вы можете использовать путь JSON в предложении WHERE, чтобы возвращать только пользователей с учетной записью Twitter:
Изменение части документа JSON
Есть несколько функций MySQL для изменения частей документа JSON с использованием записи пути. Они включают:
Поэтому вы можете добавить «технический» тег к любой книге, в которой уже есть тег «JavaScript»:
Дальнейшая информация
Руководство MySQL предоставляет дополнительную информацию о типе данных JSON и связанных функциях JSON.
Опять же, я призываю вас не использовать JSON, если в этом нет крайней необходимости. Вы можете эмулировать всю документно-ориентированную базу данных NoSQL в MySQL, но это сведет на нет многие преимущества SQL, и вы также можете переключиться на настоящую систему NoSQL! Тем не менее, типы данных JSON могут сэкономить усилия для более неясных требований к данным в приложении SQL.
При этом если – это строка, то использовать ее для построения различных SQLзапросов не представляется возможным, особенно если необходимо выбрать значение некоторого свойства.
Но с выходом системы управления базами данных MySQL версии 5.7, все значительно изменилось, так как разработчики добавили новый тип данных для полей создаваемых таблиц, под названием MySQL JSON. Говоря тем самым, что был добавлен функционал благодаря которому можно работать с информацией хранящейся в строке формата JSON и использовать ее для построения SQLзапросов. Поэтому в данном уроке я хотел показать, как работать с JSON данными, используя СУБД MySQL.
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
Для начала, хотел бы отметить, что поддержка данных формата JSON введена в систему управлениями базами данных MySQL начиная с версии 5.7, а значит для хорошего понимания данного урока и тестирования результатов работы, Вам необходимо проверить версию используемой СУБД MySQL и при необходимости выполнить обновление. Если Вы используете программное обеспечение OpenServer, в качестве инструмента для веб-разработки, то выше указанная, версия баз данных, входит в комплект версии 5.2.2 пакета OpenServer.
Теперь давайте создадим новую базу данных, под названием test, которую мы будем использовать для тестирования доступных функций. После этого откроем консоль продукта OpenServer и соединимся с сервером базы данных, используя команду:
Читайте также: