Wann kann ich JSON- oder XML-Daten in einer SQL-Tabelle speichern?

Die wichtigsten Fragen

• Was werden Sie tun mit diesen Daten? und
• Wie Sie sind-Filterung/Sortierung/Beitritt/Manipulation dieser Daten?

JSON (wie XML) ist ideal für den Datenaustausch, kleiner Abstellraum und allgemein definierten Strukturen, aber nicht an den typischen Aktionen, die Sie ausführen, in Ihrem RDBMS. In den meisten Fällen wird es besser sein zu transfer Ihre JSON-Daten in normalen Tabellen und neu erstellen der JSON-wenn Sie es brauchen.

XML /JSON und 1.NF

Die erste Regel der Normalisierung bestimmt, nie zu speichern mehr als ein bit information in einer Spalte. Sie sehen eine Spalte "PersonName" mit einem Wert wie "Mickey Maus"? Sie zeigen auf diese und Schrei: Ändern, sofort!

Was über XML-oder JSON? Sind diese Typen brechen 1.NF? Nun, ja und Nein... 

Ist es vollkommen in Ordnung, speichern Sie eine komplette Struktur als ein bit von Informationen wenn es ein bit information eigentlich. Sie bekommen eine SOAP-Antwort und wollen, um es zu speichern, da Sie möglicherweise müssen Sie diese für zukünftige Referenz (aber Sie wird nicht verwenden diese Daten für Ihre eigenen Prozesse)? Verstauen Sie es einfach wie!

Nun stellen Sie sich eine komplexe Struktur (XML oder JSON), die eine person (mit Ihrer Adresse, weiteren details...). Jetzt setzen Sie diese in einer Spalte als PersonInCharge. Ist das falsch? Sollte dies nicht lieber in richtig entworfenen zugehörigen Tabellen mit foreign-key-Referenz anstelle des XML/JSON? Vor allem, wenn die gleiche person auftreten, in vielen verschiedenen Zeilen, es ist definitiv falsch zu verwenden, eine XML - /JSON-Ansatz.

Aber stellen Sie sich nun die Notwendigkeit der Speicherung von historischen Daten. Sie möchten bestehen die person, die Daten für einen bestimmten moment in der Zeit. Einige Tage später die person, die Ihnen sagt, eine neue Adresse? Kein problem! Die alte Adresse wohnt in einer XML - /JSON-wenn Sie jemals brauchen...

Fazit: Wenn Sie die Daten speichern, um es zu halten, ist es okay. Wenn diese Daten eine einzigartige Teil, es ist okay...

Aber wenn Sie benötigen, die internen Teile regelmäßig oder, wenn dies bedeuten würde, redundante doppelte Lagerung es ist nicht okay...

Physischen Speicher

Folgenden wird für SQL Server und anderen möglicherweise auf andere RDBMs.

XML nicht gespeichert wie der text, den Sie sehen, sondern als eine Hierarchie-Baum. Bei der Abfrage dieser ist erstaunlich gut durchführen! Diese Struktur wird nicht analysiert, auf string-Ebene!

JSON in SQL Server (2016+) lebt in einem string und müssen analysiert werden. Es gibt keine echte native JSON-Typ (wie es eine native-XML-Typ). Dies könnte später kommen, aber jetzt würde ich davon ausgehen, dass JSON nicht so performant wie XML auf SQL Server (siehe Abschnitt UPDATE 2). Keine Notwendigkeit zu Lesen einen Wert aus der JSON-müssen eine Hölle von viel versteckte string-Methode ruft...

Was bedeutet das für Sie?

Ihre liebenswert DB-Künstler 😀 weiß, dass die Speicherung JSON wieist gegen gemeinsame Prinzipien von RDBMs. Er weiß,

• dass ein JSON ist ziemlich wahrscheinlich gegen 1.NF
• dass ein JSON-könnte sich mit der Zeit ändern (gleiche Spalte, unterschiedliche Inhalte).
• dass ein JSON ist nicht leicht zu Lesen, und es ist sehr schwer zu filtern/suchen/beitreten oder Sortieren.
• dass solche Operationen wird die Verschiebung durchaus einige zusätzliche Last auf den Armen kleinen DB-server

Es gibt einige workarounds (je nach RDBMS, das Sie verwenden), aber die meisten von Ihnen nicht arbeiten, wie Sie möchten, es...

Die Antwort auf Ihre Frage in kürzester

JA

• Wenn Sie nicht verwenden möchten-Daten, die gespeichert innerhalb deine JSON für teure Operationen (filter/join/Sortieren).
  
  Sie können speichern Sie diese wie jede andere existiert nur Inhalt. Wir speichern viele Bilder als BLOBs, aber wir würden nicht versuchen, die filter für alle Bilder, die mit einer Blume...
• Wenn Sie nicht die Mühe machen überhaupt was drin ist (nur speichern Sie es und Lesen Sie es als ein bit an information)
• Wenn die Strukturen sind variabel, das würde es schwieriger machen, zu erstellen, physikalische Tabellen dann zur Arbeit mit JSON-Daten.
• Wenn die Struktur zu tief geschachtelt ist, dass der Speicher in physischen Tabellen ist zu viel Aufwand

KEINE

• Wenn Sie möchten, um die internen Daten, wie Sie verwenden würden, um eine relationale Tabelle, die Daten (filter, Indizes, joins...)
• Wenn Sie speichern Duplikate (erstellen Redundanz)
• Im Allgemeinen: Wenn du vor performance-Problemen, für sicher, Sie werden gegen Sie in vielen typischen Szenarien!)

Könnten Sie anfangen, mit der JSON-innerhalb einer string-Spalte oder als BLOB und ändern Sie diese, um physikalische Tabellen, wenn Sie es brauchen. Meine Magische Kristallkugel sagt mir, das könnte morgen sein 😀

UPDATE

Finden Sie einige Ideen über die Leistung und Speicherplatz hier: https://stackoverflow.com/a/47408528/5089204

UPDATE 2: Mehr über die Leistungen...

Die folgenden Adressen JSON-und XML-Unterstützung im SQL-Server 2016

User @mike123 darauf Artikel auf einer offiziellen microsoft-blog das scheint Beweis in einem experiment, dass Abfragen von JSON ist 10 x schneller dann Abfragen einer XML - im SQL-Server.

Einige Gedanken darüber:

Es kann gezeigt werden, dass eine Wollmütze ist besser als ein Sturzhelm!

Du bist ein Witz...

Nein, gestern habe ich ein experiment. Ich nahm zehn verschiedene crash-Helme und 10 verschiedenen wollenen Mützen und warf Sie kopfüber an einem Wolkenkratzer. Du wirst es nicht glauben: Alle Helme kaputt gingen, während die wollenen Mützen gar nicht zeigen Anzeichen von Schäden!

Einige cross-checks mit dem "experiment":

• die "experiment" - Maßnahmen eine Menge, aber nicht die Leistung von XML vs. JSON. Tun die gleiche Aktion agaist die gleichen (unveränderten) string wiederholt, ist kein realistisches Szenario
• Die getestete Beispiele sind weit zu einfach für eine Allgemeine Aussage!
• Der gelesene Wert ist immer gleich und nicht einmal benutzt. Der Optimierer...
• Nicht ein einziges Wort über den mächtigen XQuery Unterstützung! Suchen Sie ein Produkt mit einer bestimmten ID in einem array? JSON muss, Lesen Sie die ganze Menge und einen filter verwenden, danach mit WHEREwährend XML erlauben würde, eine interne XQuery predicate. Nicht zu reden von FLWOR...
• die "Experimente" code wie auf meinem system bringt: JSON scheint zu 3x schneller (aber nicht 10x).
• Hinzufügen /text() zu den XPath reduziert diese auf weniger als 2x. In den zugehörigen Artikel der Benutzer "Mister Magoo" wies dies bereits, aber die Klick-Köder Titel ist immer noch unverändert...
• Mit wie eine einfache JSON wie in der "experiment" der Schnellste Reine T-SQL-Ansatz war eine Kombination von SUBSTRING und CHARINDEX 😀

Folgende code zeigt ein realistischeres experiment

• Über eine JSON und eine identische XML mit mehr als einem Product (ein JSON-array vs. Geschwisterknoten)
• JSON und XML etwas ändern (10000 Laufenden Nummern) und in die Tabellen eingefügt.
• Gibt es ein Erstgespräch agaist beide Tabellen zu vermeiden first-call-bias
• Alle 10000 Einträge gelesen und die abgerufenen Werte werden eingefügt, um zu einer anderen Tabelle.
• Mit GO 10 wird durch diesen block zehn mal zu vermeiden first-call-bias

Das endgültige Ergebnis zeigt deutlich, dass bei JSON ist langsamer als XML - (nicht viel, etwa 1,5 x auf ein noch sehr einfaches Beispiel).

Die Letzte Anweisung:

• Mit einem allzu vereinfachten Beispiel unter unangemessenen Umständen JSON schneller sein können als XML -
• Mit einem immer noch sehr einfach, aber mehr realistische Beispiel-XML werden schneller in einfachen Lesen
• Wenn es irgendeine Notwendigkeit gibt, zu Lesen, dass ein bestimmtes element aus einem array filtern Sie alle Einträge, wo eine bestimmte ProductID enthalten ist in dem array, oder zum navigieren nach oben und unten den Pfad, JSON nicht halten kann. Es muss analysiert werden, aus einem string komplett - jedes mal, wenn Sie haben zu packen...

Den test-code

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

Das Ergebnis (SQL-Server 2016 Express auf einen Acer Aspire v17 Nitro, Intel i7, 8GB Ram)

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

InformationsquelleAutor der Antwort Shnugo

Werde ich Welle mein Zauberstab. Puh! Goldene Regeln für die Verwendung von JSON:

• Wenn MySQL nicht suchen müssen innen JSON, und die Anwendung muss einfach nur eine Sammlung von Sachen, dann von JSON ist in Ordnung, vielleicht sogar besser.

• Wenn Sie die Suche auf Daten, die innerhalb und Sie haben MariaDB 10.0.1 oder MySQL 5.7 (mit einem JSON-Datentyp und Funktionen), dann JSON - könnte praktisch sein. MariaDB 5.3 "Dynamischen" Spalten ist eine Variante.

• Wenn Sie das tun, "Entität-Attribut-Wert -" Sachen, dann ist JSON nicht gut, aber es ist das geringste von mehreren übeln. http://mysql.rjweb.org/doc.php/eav

• Für die Suche nach einer indizierten Spalte, die nicht den Wert begraben innerhalb JSON ist ein großes plus.

• Für die Suche von einer Palette auf eine indizierte Spalte oder eine FULLTEXT Suche oder SPATIALJSON ist nicht möglich.

• Für WHERE a=1 AND b=2 die "composite" - index INDEX(a,b) ist toll, kann wahrscheinlich gar nicht kommen in der Nähe mit JSON.

• JSON funktioniert gut mit "sparse" Daten; Indizierung funktioniert, aber nicht so gut, mit solchen. (Ich beziehe mich auf die Werte, die "fehlenden" oder "NULL" für die vielen Zeilen.)

• JSON kann geben Sie "arrays" und "Bäume" ohne Rückgriff auf zusätzliche Tabelle(N). Aber Graben sich in solche arrays/Bäume nur in der app nicht in SQL.

• JSON ist um Welten besser als XML. (Meiner Meinung nach)

• Wenn Sie nicht wollen, um in den JSON-string, außer die app, dann empfehle ich komprimieren (im-client) wird es eine Speicherung in eine BLOB. Es ist wie bei einem .jpg-es gibt Sachen dort, aber SQL nicht kümmern.

Zustand Ihrer Anwendung; vielleicht können wir etwas konkreter sein.

InformationsquelleAutor der Antwort Rick James

Die "goldene Regel", die ich verwenden, in ein-hand-wavey Art und Weise, ist, dass, wenn ich brauche, JSON, in seiner raw-format, es ist okay zu speichern. Wenn ich einen speziellen Punkt der Analyse ist es, dann ist es auch nicht.

Wenn ich zum Beispiel bin erstellen einer API sendet rohen JSON, und aus welchem Grund dieser Wert ist nicht zu ändern, dann ist es okayum es zu speichern als raw JSON. Wenn ich zu analysieren, Sie zu ändern, zu aktualisieren, usw... dann nicht so viel.

InformationsquelleAutor der Antwort piisexactly3

Json sind keine große relationional db. Wenn Sie entfalten sich die json-in-Spalten und speichern in einer db , es ist toll, aber die Speicherung von json als ein blob ist neben der Verwendung als Daten-Archivierungs-system.

Könnte es mehrere Gründe für die nicht-Entfaltung einer json und speichern in einer einzelnen Spalte, aber die Entscheidung getroffen worden wäre, wie die Werte in das json-Feld nicht verwendet werden, die für alle Abfragen (oder die Werte wurden bereits entfalteten in Spalten).

Auch die meisten der json-Verarbeitung wenn überhaupt war das Feld abgefragt werden würde, der außerhalb der sql-Umgebung, sql ist einfach nicht dazu gedacht, für json-Verarbeitung. Die eigentliche Frage ist dann , wo Speichere ich diese json, lasse ich es als flat-files und, wenn erforderlich, Fragen Sie über einige andere system (spark/hive/etc).

Ich würde Zustimmen, mit Ihrer DB-Künstler , nicht verwenden RDBMS für die Archivierung. Es gibt billigere Möglichkeiten. Auch json-blobs können sich riesig und können bogging die DB-Speicherplatz mit der Zeit.

InformationsquelleAutor der Antwort Satyadev