Когда я могу сохранить данные JSON или XML в таблице SQL

Ответ 1

Основные вопросы:

• Что вы собираетесь делать с этими данными? и
• Как вы фильтруете/сортируете/соединяете/манипулируете этими данными?

JSON (например, XML) отлично подходит для обмена данными, малого хранилища и общих структур, но он не может участвовать в типичных действиях, выполняемых в вашей РСУБД. В большинстве случаев лучше переносить данные JSON в обычные таблицы и повторно создавать JSON, когда вам это нужно.

XML/JSON и 1.NF

Первое правило нормализации диктует, никогда не хранить более одного бита информации в один столбец. Вы видите столбец "PersonName" со значением "Mickey Mouse"? Вы указываете на это и плачете: немедленно измените это!

Как насчет XML или JSON? Являются ли эти типы нарушением 1.NF? Ну, да и нет...  

Вполне нормально хранить полную структуру как один бит информации, если это один бит информации. Вы получаете ответ SOAP и хотите сохранить его, потому что вам может понадобиться это для будущей ссылки (но вы не используете эти данные для своих собственных процессов)? Просто сохраните его, как есть!

Теперь представьте сложную структуру (XML или JSON), представляющую человека (с его адресом, дальнейшие подробности...). Теперь вы помещаете этот в один столбец как PersonInCharge. Это неправильно? Должно ли это скорее жить в правильно разработанных связанных таблицах с ссылкой на внешний ключ вместо XML/JSON? Особенно, если один и тот же человек может встречаться во многих разных строках, определенно неправильно использовать подход XML/JSON.

Но теперь представьте себе необходимость хранения исторических данных. Вы хотите перенести данные человека за данный момент времени. Через несколько дней человек сообщает вам новый адрес? Нет проблем! Старый адрес живет в XML/JSON, если вам когда-нибудь понадобится...

Заключение: Если вы храните данные только для того, чтобы их сохранить, все в порядке. Если эти данные являются уникальной частью, все в порядке... Но если вам нужны внутренние детали регулярно или если это будет означать избыточное дублирующее хранение, это не нормально...

Физическое хранилище

Ниже приведено описание для SQL Server и может отличаться на других RDBM.

XML не сохраняется как текст, который вы видите, а как дерево иерархии. Запросить это поразительно хорошо! Эта структура не анализируется на уровне строк!
JSON в SQL Server (2016+) живет - AFAIK - в строке и должен быть проанализирован. Нет реального родного типа JSON (например, есть собственный тип XML). Это может произойти позже, но на данный момент я предполагаю, что JSON не будет таким же эффективным, как XML на SQL Server. Любая потребность в чтении значения из JSON потребует чертовски много вызовов скрытых строк...

Что это значит для вас?

ваш привлекательный художник БД: -D знает, что сохранение JSON как есть, противоречит общим принципам RDBM. Он знает,

• что JSON, вероятно, нарушает 1.NF
• что JSON может измениться во времени (тот же столбец, отличающийся контентом).
• что JSON непросто читать, и очень сложно фильтровать/искать/присоединяться или сортировать по нему.
• что такие операции сдвинут довольно большую нагрузку на плохой сервер БД

Есть некоторые способы обхода (в зависимости от используемой СУБД), но большинство из них не работают так, как вам хотелось бы...

Ответ на ваш вопрос коротким

Да

• Если вы не хотите использовать данные, которые хранятся в вашем JSON для дорогостоящих операций (filter/join/sort).
  Вы можете хранить это, так как любой другой существует только контент. Мы храним много изображений в виде BLOB, но мы не будем пытаться фильтровать все изображения с цветком...
• Если вы вообще не беспокоитесь о том, что внутри (просто сохраните его и прочитайте как один бит информации)
• Если структуры являются переменными, что затрудняет создание физических таблиц, то для работы с данными JSON.
• Если структура глубоко вложенна, то хранение в физических таблицах связано с большими накладными расходами

НЕТ

• Если вы хотите использовать внутренние данные, как если бы вы использовали данные реляционной таблицы (фильтр, индексы, объединения...)
• Если вы будете хранить дубликаты (создавать избыточность)
• В общем: если вы столкнулись с проблемами производительности (наверняка вы столкнетесь с ними во многих типичных сценариях!)

Вы можете начать с JSON в строковом столбце или в виде BLOB и изменить его на физические таблицы, когда вам это нужно. Мой волшебный хрустальный шар говорит мне, что это может быть завтра: -D

UPDATE

Найдите несколько идей о производительности и дисках здесь: fooobar.com/questions/203563/...

Ответ 2

Это слишком долго для комментария.

Если бы это было "абсолютно неправильно", большинство баз данных не поддерживали бы его. Хорошо, большинство баз данных поддерживают запятые в предложении FROM, и я считаю, что это "абсолютно неправильно". Но поддержка JSON - это новая разработка, а не "совместимая" функция "назад".

Один очевидный случай: структура JSON - это просто BLOB, который передается обратно в приложение. Тогда нет дебатов - кроме того, накладные расходы на хранение JSON, что излишне многословно для структурированных данных с общими полями в каждой записи.

Другим случаем является случай с "разреженным" столбцом. У вас есть строки со многими возможными столбцами, но они варьируются от строки к строке.

Другой случай - это когда вы хотите хранить "вложенные" записи в записи. JSON является мощным.

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

Ответ 3

Я буду махать волшебной палочкой. Пуф! Золотые правила использования JSON:

• Если MySQL не нужно заглядывать в JSON, и приложение просто нуждается в наборе вещей, тогда JSON отлично, возможно, даже лучше.

• Если вы будете искать данные, находящиеся внутри, и у вас есть MariaDB 10.0.1 или MySQL 5.7 (с типом и функциями JSON), тогда JSON может быть практичным. MariaDB 5.3 "Динамические" столбцы - это вариант.

• Если вы делаете вещи "Entity-Attribute-Value", то JSON не хорош, но это наименьшее из нескольких зол. http://mysql.rjweb.org/doc.php/eav

• Для поиска по индексированному столбцу отсутствие значения, заложенного внутри JSON, является большим плюсом.

• Для поиска по диапазону в индексированном столбце или в FULLTEXT поиске или SPATIAL JSON невозможен.

• Для WHERE a=1 AND b=2 "составной" индекс INDEX(a,b) велик; вероятно, не может приблизиться к JSON.

• JSON хорошо работает с "разреженными" данными; INDEXing работает, но не так, с такими. (Я имею в виду значения, которые "отсутствуют" или NULL для многих строк.)

• JSON может предоставить вам "массивы" и "деревья", не прибегая к дополнительным таблицам. Но копайте в такие массивы/деревья только в приложении, а не в SQL.

• JSON - это мир лучше, чем XML. (Мое мнение)

• Если вы не хотите попасть в строку JSON, кроме приложения, я рекомендую сжать (в клиенте) его сохранение в BLOB. Подумайте об этом как .jpg - там что-то там, но SQL все равно.

Укажите ваше приложение; возможно, мы можем быть более конкретными.

Ответ 4

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

    DROP TABLE IF EXISTS Person

 CREATE TABLE Person 
 ( _id int identity constraint PK_JSON_ID primary key,
 value nvarchar(max)
 CONSTRAINT [Content should be formatted as JSON]
 CHECK ( ISJSON(value)>0 )
 )

Эта простая структура похожа на стандартную коллекцию NoSQL, которую можно создавать в базах данных NoSQL (например, Azure DocumentDB или MongoDB), где у вас есть только ключ, представляющий идентификатор и значение, представляющее JSON.

Обратите внимание, что NVARCHAR - это не просто обычный текст. SQL Server имеет встроенный механизм сжатия текста, который может прозрачно сжимать данные, хранящиеся на диске. Сжатие зависит от языка и может достигать 50% в зависимости от ваших данных (см. Сжатие UNICODE).

Ключевое различие между SQL-сервером и другими обычными базами данных NoSQL заключается в том, что SQL Server позволяет использовать гибридную модель данных, в которой вы можете хранить несколько объектов JSON в одной и той же "коллекции" и объединять их с регулярными реляционными столбцами.

В качестве примера представьте, что мы знаем, что каждый человек в вашей коллекции будет иметь имя FirstName и LastName и что вы можете хранить общую информацию о человеке как о одном объекте JSON, а также о телефонных номерах/адресах электронной почты в качестве отдельных объектов. В SQL Server 2016 мы можем легко создать эту структуру без какого-либо дополнительного синтаксиса:

DROP TABLE IF EXISTS Person

CREATE TABLE Person (

 PersonID int IDENTITY PRIMARY KEY,

 FirstName nvarchar(100) NOT NULL,

 LastName nvarchar(100) NOT NULL,

 AdditionalInfo nvarchar(max) NULL,

 PhoneNumbers nvarchar(max) NULL,

 EmailAddresses nvarchar(max) NULL
 CONSTRAINT [Email addresses must be formatted as JSON array]
 CHECK ( ISJSON(EmailAddresses)>0 )

 )

Вместо одного объекта JSON вы можете упорядочить свои данные в этой "коллекции". Если вы не хотите явно проверять структуру каждого столбца JSON, вам не нужно добавлять ограничение проверки JSON для каждого столбца (в этом примере я добавил ограничение CHECK только в столбец EmailAddresses).

Если вы сравните эту структуру со стандартной коллекцией NoSQL, вы можете заметить, что у вас будет более быстрый доступ к строго типизированным данным (FirstName и LastName). Поэтому это решение является хорошим выбором для гибридных моделей, где вы можете идентифицировать некоторую информацию, которая повторяется во всех объектах, а другая информация о переменных может храниться как JSON. Таким образом, вы можете сочетать гибкость и производительность.

Если вы сравните эту структуру со схемой таблицы Person AdventureWorks, вы можете заметить, что мы удалили множество связанных таблиц.

Помимо простоты схемы, ваши операции доступа к данным будут проще по сравнению со сложной реляционной структурой. Теперь вы можете читать одну таблицу вместо объединения нескольких таблиц. Когда вам нужно вставить нового человека со связанной информацией (адреса электронной почты, номера телефонов), вы можете вставить одну запись в одну таблицу вместо того, чтобы вставлять одну запись в таблицу AdventureWorks Person, используя столбец идентификатора, чтобы найти внешний ключ, который будет использоваться для хранения телефонов, адреса электронной почты и т.д. Кроме того, в этой модели вы можете легко удалить строку с одним человеком без каскадных удалений с использованием внешних ключей.

Базы данных NoSQL оптимизированы для простых операций чтения, вставки и удаления. SQL Server 2016 позволяет применять одну и ту же логику в реляционной базе данных.

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

ALTER TABLE Person
 ADD CONSTRAINT [Age should be number]
 CHECK ( ISNUMERIC(JSON_VALUE(value, '$.age'))>0 )

 ALTER TABLE Person
 ADD CONSTRAINT [Person should have skills]
 CHECK ( JSON_QUERY(value, '$.skills') IS NOT NULL)
First constraint will take the value of $.age property and check is this numeric value. Second constraint will try to find JSON object in $.skills property and verify that it exists. The following INSERT statements will fail due to the violation of constraints:



INSERT INTO Person(value)
 VALUES ('{"age": "not a number", "skills":[]}')

 INSERT INTO Person(value)
 VALUES ('{"age": 35}')

Обратите внимание, что ограничения CHECK могут замедлять процессы вставки/обновления, поэтому вы можете избежать их, если вам нужна более высокая производительность записи.

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

CREATE TABLE Person

 ( _id int identity constraint PK_JSON_ID primary key,

 data varbinary(max),

 value AS CAST(DECOMPRESS(data) AS nvarchar(max))

 )



 INSERT INTO Person(data)

 VALUES (COMPRESS(@json))

Функции COMPRESS и DECOMPRESS используют стандартное сжатие GZip. Если ваш клиент может обрабатывать сжатие GZip (например, браузер, который понимает содержимое gzip), вы можете напрямую возвращать сжатый контент. Обратите внимание, что это компромисс производительности/хранения. Если вы часто запрашиваете сжатые данные, у вас есть более медленная производительность, потому что текст должен каждый раз распаковываться.

Примечание. Функции JSON доступны только в SQL Server 2016+ и Azure SQL Database.

Подробнее можно прочитать из источника этой статьи

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/23/storing-json-in-sql-server/

Ответ 5

"Золотое правило", которое я использую в ручном режиме, заключается в том, что, если мне нужен JSON в его необработанном формате, все нормально хранить. Если я должен сделать особую точку разбора, то это не так.

Например, если я создаю API, который отправляет исходный JSON, и по какой-либо причине это значение не изменится, тогда нормально хранить его как raw JSON. Если мне нужно разобрать его, изменить его, обновить и т.д.... тогда не так много.

Ответ 6

Вопрос, который вы должны задать, это:

Я привязан к использованию только этой базы данных?

DO

• Если вы можете использовать другую базу данных для хранения JSON, используйте решение для хранения документов, такое как CouchDB, DynamoDB или MongoDB.
• Используйте эту способность DB хранилища данных для индексирования и поиска иерархических данных.
• Используйте реляционную базу данных для ваших реляционных данных.
• Используйте реляционную базу данных для отчетности, хранилищ данных и интеллектуального анализа данных.

НЕ

• Храните JSON как строку, если это возможно.
• Попробуйте найти максимальную длину данных JSON.
• Используйте varchar для хранения JSON (используйте текст /blob, если нужно).
• Попробуйте выполнить поиск через сохраненные JSON для значений.
• Беспокойство об исключении JSON для хранения в виде строки.

Ответ 7

Json не очень хороши в отношениях db. Если вы разворачиваете json в столбцы и храните в db, это здорово, но хранение json в качестве blob - это использование его как системы архивирования данных.

Может быть несколько причин не разворачивать json и хранить его в одном столбце, но решение было принято, поскольку значения в этом поле json не использовались бы для какого-либо запроса (или значения уже были развернуты в колонны).

Кроме того, большая часть обработки json, если бы все запросы были запрошены, будет вне среды sql, поскольку sql просто не предназначен для обработки json. Тогда возникает реальный вопрос: где я храню этот json, я просто разрешаю ему быть плоскими файлами и, когда требуется, запрашивать их через какую-то другую систему (искра/улей/и т.д.).

Я бы согласился с вашим художником DB, не используйте RDBMS для архивации. Есть более дешевые варианты. Также json blobs может стать огромным и начать со временем забивать дисковое пространство БД.