sql - 什么时候可以将JSON或XML数据保存在SQL表中

Question

使用SQL或MySQL（或与此相关的任何关系数据库）时-我知道将数据保存在常规列中对于索引和其他目的更好。

事情是加载和保存JSON数据有时要简单得多。并使开发更容易。

是否有用于在数据库中保存原始JSON数据的“黄金法则”？

这样做绝对是错误的做法吗？

摘要

给出了很好的答案，但是毫无疑问，最井井有条的是@Shnugo给出的答案，这应该得到赏金。

还想指出@Gordon Linoff和@Amresh Pandey给出的答案，用于解释其他特殊用例。

感谢上帝，祝大家工作顺利！

Answer 1

主要问题是


您将如何处理这些数据？和
您如何过滤/排序/合并/处理此数据？


JSON（如XML）非常适合数据交换，小型存储和通用定义的结构，但它不能参与在RDBMS中运行的典型操作。在大多数情况下，将JSON数据传输到普通表中并在需要时重新创建JSON会更好。

XML / JSON和1.NF

规范化的第一条规则规定，决不要将多于一位的信息存储到一列中。您看到带有“ Mickey Mouse”之类的值的“ PersonName”列吗？您指向此并哭泣：立即更改！

XML或JSON呢？这些类型是否破坏了1.NF？好吧，是的，不是...

如果实际上只存储一小部分信息，则完全可以将其存储为一小部分信息。您会得到一个SOAP响应并要存储它，因为您可能需要它作为以后的参考（但您不会将这些数据用于自己的进程）？只需按原样存储它即可！

现在，想象一个代表一个人的复杂结构（XML或JSON）（及其地址，更多详细信息...）。现在，将其作为PersonInCharge放在一列中。这是错的吗？难道这不应该存在于具有外键引用而不是XML / JSON的经过适当设计的相关表中吗？特别是如果同一个人可能出现在许多不同的行中，那么使用XML / JSON方法肯定是错误的。

但是现在想象一下需要存储历史数据。您想要在给定的时间段内保留该人的数据。几天后，对方告诉您新地址？没问题！如果您需要，旧地址将保存在XML / JSON中...

结论：如果存储数据只是为了保留它，就可以了。如果这些数据是唯一的部分，就可以了...
但是如果您定期需要内部零件，或者如果这意味着多余的重复存储，那就不好了...

物理存储

以下内容适用于SQL Server，在其他RDBM上可能有所不同。

XML不是存储为您看到的文本，而是存储为层次结构树。查询这是惊人的好表现！无法在字符串级别解析此结构！
SQL Server（2016+）中的JSON位于字符串中，必须进行解析。没有真正的本机JSON类型（例如，有本机XML类型）。这可能会在以后出现，但就目前而言，我假设JSON在SQL Server上的性能不如XML（请参阅UPDATE 2）。任何需要从JSON读取值的操作都将需要大量隐藏的字符串方法调用...

这对您意味着什么？

您可爱的DB艺术家：-D知道，按原样存储JSON违反RDBM的通用原则。他知道，


JSON很可能会破坏1.NF
JSON可能会随时间变化（同一列，不同内容）。
JSON不易阅读，并且很难对其进行过滤/搜索/加入或排序。
这样的操作会将相当多的额外负载转移到可怜的小型DB服务器上


有一些解决方法（取决于您所使用的RDBMS），但是大多数方法都无法按照您希望的方式工作...

简而言之，您的问题的答案

是


如果您不想使用存储在JSON中的数据以进行昂贵的操作（过滤器/联接/排序）。
您可以像存储任何其他仅存在的内容一样存储它。我们将许多图片存储为BLOB，但是我们不会尝试过滤所有带有花朵的图片...
如果您根本不打扰里面的内容（只需将其存储并作为一小部分信息阅读）
如果结构是可变的，这将使创建物理表变得更加困难，然后将其与JSON数据一起使用。
如果该结构是深层嵌套的，则物理表中的存储将产生大量开销


没有


如果要像使用内部数据一样使用内部表的数据（过滤器，索引，联接...）
如果要存储重复项（创建冗余）
一般而言：如果您遇到性能问题（可以肯定的是，在许多典型情况下都将面对它们！）


您可以在字符串列中以JSON开头或以BLOB开头，并在需要时将其更改为物理表。我的魔幻水晶球告诉我，这可能是明天:-D

更新

在此处找到有关性能和磁盘空间的一些想法：https://stackoverflow.com/a/47408528/5089204

更新2：有关性能的更多信息...

以下内容解决了SQL-Server 2016中的JSON和XML支持

用户@ mike123指出了article on an official microsoft blog，这似乎在实验中得到了证明，与在SQL Server中查询XML相比，查询JSON的速度快10倍。

关于此的一些想法：

与“实验”进行一些交叉核对：


“实验”衡量很多，但XML和JSON的性能无关。反复对相同（不变）的字符串重复进行相同操作是不现实的情况
一般而言，经过测试的示例非常简单！
读取的值始终相同，甚至不使用。优化器将看到此...
关于强大的XQuery支持一言不发！在数组中找到具有给定ID的产品？ JSON需要读取全部内容，然后使用WHERE使用过滤器，而XML允许内部使用XQuery predicate。更不用说FLWOR ...
我的系统上的“实验”代码显示出来：JSON似乎快了3倍（但没有10倍）。
将/text()添加到XPath会将其减少到小于2倍。在相关文章中，用户“ Mister Magoo”已经指出了这一点，但是点击诱饵的标题仍然没有改变。
通过“实验”中提供的简单JSON，最快的纯T-SQL方法是SUBSTRING和CHARINDEX的组合：-D


以下代码将显示更实际的实验


使用JSON和具有多个Product的相同XML（JSON数组与同级节点）
JSON和XML略有变化（10000个运行数字），并已插入表中。
两个表都有初始调用反对表，以避免首次调用偏差
读取所有10000个条目，并将检索到的值插入到另一个表中。
使用GO 10将在此块中运行十次，以避免首次调用偏差


最终结果清楚地表明，JSON比XML慢（不是那么多，在一个非常简单的示例中约为1.5倍）。

最后的声明：


在过度的情况下，通过过于简化的示例，JSON可能比XML更快
处理JSON是纯字符串操作，而XML被解析和转换。在第一步中，这是相当昂贵的，但是一旦完成，它将加快一切。
一次性执行一次JSON可能更好（避免了创建XML的内部层次结构表示的开销）
通过一个仍然非常简单但更现实的示例，XML的简单阅读速度将会更快
每当需要从数组中读取特定元素，过滤数组中包含给定ProductID的所有条目，或在路径中上下移动时，JSON都无法阻止。必须从字符串中完全解析出它-每次您必须抓住它时...


测试代码

USE master;
GO
--create a clean database
CREATE DATABASE TestJsonXml;
GO
USE TestJsonXml;
GO
--create tables
CREATE TABLE TestTbl1(ID INT IDENTITY,SomeXml XML);
CREATE TABLE TestTbl2(ID INT IDENTITY,SomeJson NVARCHAR(MAX));
CREATE TABLE Target1(SomeString NVARCHAR(MAX));
CREATE TABLE Target2(SomeString NVARCHAR(MAX));
CREATE TABLE Times(Test VARCHAR(10),Diff INT)
GO
--insert 10000 XMLs into TestTbl1
WITH Tally AS(SELECT TOP 10000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*2 AS Nmbr FROM master..spt_values AS v1 CROSS APPLY master..spt_values AS v2)
INSERT INTO TestTbl1(SomeXml)
SELECT 
N'<Root>
    <Products>
    <ProductDescription>
        <Features>
            <Maintenance>' + CAST(Nmbr AS NVARCHAR(10)) + ' year parts and labor extended maintenance is available</Maintenance>
            <Warranty>1 year parts and labor</Warranty>
        </Features>
        <ProductID>' + CAST(Nmbr AS NVARCHAR(10)) + '</ProductID>
        <ProductName>Road Bike</ProductName>
    </ProductDescription>
    <ProductDescription>
        <Features>
            <Maintenance>' + CAST(Nmbr + 1 AS NVARCHAR(10)) + ' blah</Maintenance>
            <Warranty>1 year parts and labor</Warranty>
        </Features>
        <ProductID>' + CAST(Nmbr + 1 AS NVARCHAR(10)) + '</ProductID>
        <ProductName>Cross Bike</ProductName>
    </ProductDescription>
    </Products>
</Root>'
FROM Tally;

--insert 10000 JSONs into TestTbl2
WITH Tally AS(SELECT TOP 10000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nmbr FROM master..spt_values AS v1 CROSS APPLY master..spt_values AS v2)
INSERT INTO TestTbl2(SomeJson)
SELECT 
N'{
    "Root": {
        "Products": {
            "ProductDescription": [
                {
                    "Features": {
                        "Maintenance": "' + CAST(Nmbr AS NVARCHAR(10)) + ' year parts and labor extended maintenance is available",
                        "Warranty": "1 year parts and labor"
                    },
                    "ProductID": "' + CAST(Nmbr AS NVARCHAR(10)) + '",
                    "ProductName": "Road Bike"
                },
                {
                    "Features": {
                        "Maintenance": "' + CAST(Nmbr + 1 AS NVARCHAR(10)) + ' blah",
                        "Warranty": "1 year parts and labor"
                    },
                    "ProductID": "' + CAST(Nmbr + 1 AS NVARCHAR(10)) + '",
                    "ProductName": "Cross Bike"
                }
            ]
        }
    }
}'
FROM Tally;
GO

--Do some initial action to avoid first-call-bias
INSERT INTO Target1(SomeString)
SELECT SomeXml.value('(/Root/Products/ProductDescription/Features/Maintenance/text())[1]', 'nvarchar(4000)')
FROM TestTbl1;
INSERT INTO Target2(SomeString)
SELECT JSON_VALUE(SomeJson, N'$.Root.Products.ProductDescription[0].Features.Maintenance')
FROM TestTbl2;
GO

--Start the test
DECLARE @StartDt DATETIME2(7), @EndXml DATETIME2(7), @EndJson DATETIME2(7);

--Read all ProductNames of the second product and insert them to Target1
SET @StartDt = SYSDATETIME();
INSERT INTO Target1(SomeString)
SELECT SomeXml.value('(/Root/Products/ProductDescription/ProductName/text())[2]', 'nvarchar(4000)')
FROM TestTbl1
ORDER BY NEWID();
--remember the time spent
INSERT INTO Times(Test,Diff)
SELECT 'xml',DATEDIFF(millisecond,@StartDt,SYSDATETIME());

--Same with JSON into Target2
SET @StartDt = SYSDATETIME();
INSERT INTO Target2(SomeString)
SELECT JSON_VALUE(SomeJson, N'$.Root.Products.ProductDescription[1].ProductName')
FROM TestTbl2
ORDER BY NEWID();
--remember the time spent
INSERT INTO Times(Test,Diff)
SELECT 'json',DATEDIFF(millisecond,@StartDt,SYSDATETIME());

GO 10 --do the block above 10 times

--Show the result
SELECT Test,SUM(Diff) AS SumTime, COUNT(Diff) AS CountTime
FROM Times
GROUP BY Test;
GO
--clean up
USE master;
GO
DROP DATABASE TestJsonXml;
GO

结果（Acer Aspire v17 Nitro Intel i7、8GB Ram上的SQL Server 2016 Express）

Test    SumTime 
------------------
json    2706    
xml     1604