Kiedy mogę zapisać dane JSON lub XML w tabeli SQL

Główne pytania to

Co zamierzasz zrobić z tymi danymi? oraz
• Jak filtrujesz/sortujesz/łączysz / manipulujesz tymi danymi?

JSON (podobnie jak XML) jest świetny do wymiany danych, małej pamięci masowej i ogólnie zdefiniowanych struktur, ale nie może uczestniczyć w typowych działaniach uruchamianych w RDBMS. W większości przypadków lepiej będzie przenieść dane JSON do normalnych tabel i ponownie utworzyć JSON, gdy zajdzie taka potrzeba to.

XML / JSON i 1.NF

Pierwsza zasada normalizacji nakazuje, aby nigdy nie przechowywać więcej niż jednego bitu informacji w jednej kolumnie. Widzisz kolumnę "PersonName" o wartości "Mickey Mouse"? Wskazujesz na to i płaczesz: Zmień to natychmiast!

A co z XML lub JSON? Czy te typy łamią 1.NF tak i nie... 

Doskonale jest przechowywać kompletną strukturę jako jeden bit informacji jeśli jest Właściwie to jedna informacja. Otrzymujesz odpowiedź SOAP i chcesz ją przechowywać, ponieważ możesz potrzebować jej do przyszłego odniesienia (ale nie będziesz używać tych danych do własnych procesów)? Po prostu przechowuj tak jak jest !

Teraz wyobraź sobie złożoną strukturę (XML lub JSON) reprezentującą osobę (z jej adresem, dalsze szczegóły...). Teraz umieść to w jednej kolumnie jako PersonInCharge. Czy to źle? Czy to nie powinno raczej żyć w odpowiednio zaprojektowanym powiązane tabele z odniesieniem do klucza obcego zamiast XML / JSON? Szczególnie jeśli ta sama osoba może występować w wielu różnych wierszach, zdecydowanie błędne jest stosowanie podejścia XML / JSON.

Ale teraz wyobraź sobie potrzebę przechowywania danych historycznych. Chcesz utrwalić dane danej osoby przez dany moment w czasie. Kilka dni później osoba podaje Ci nowy adres? Nie ma sprawy! Stary adres żyje w XML / JSON, jeśli kiedykolwiek go potrzebujesz...

Wniosek: jeśli przechowuj dane, aby je zachować. Jeśli te dane są unikalną porcją, to jest w porządku...
Ale jeśli potrzebujesz wewnętrznych części regularnie lub jeśli oznaczałoby to nadmiarowe duplikaty pamięci, nie jest to w porządku...

Fizyczne przechowywanie

Poniższy tekst dotyczy SQL Server i może się różnić w innych systemach Rdbm.

XML nie jest przechowywany jako tekst, który widzisz, ale jako drzewo hierarchii. Pytanie to jest zadziwiająco dobrze wykonujące! Struktura ta nie jest parsed on string level!
JSON w SQL Server (2016+) żyje w łańcuchu znaków i musi być przetwarzany. Nie ma prawdziwego natywnego typu JSON (tak jak jest natywny typ XML). Może to nastąpić później, ale na razie zakładam, że JSON nie będzie tak wydajny jak XML na SQL Server (patrz sekcja UPDATE 2). Jakakolwiek potrzeba odczytania wartości z JSON będzie wymagała ogromnej ilości ukrytych wywołań metod string...

Your lovable DB artist: - d wie, że przechowywanie JSON jak jest, jest sprzeczne ze wspólnymi zasadami RDBMs. On wie,

• że JSON pewnie się łamie 1.NF
• że JSON może się zmienić w czasie (ta sama kolumna, różna treść).
• że JSON nie jest łatwy do odczytania i bardzo trudno jest filtrować/wyszukiwać / łączyć lub sortować według niego.
• że takie operacje przeniosą sporo dodatkowego obciążenia na słaby mały serwer DB

Istnieją pewne obejścia (w zależności od RDBMS, których używasz), ale większość z nich nie działa tak, jak chcesz...

Odpowiedź na twoje pytanie w skrócie

Tak

• jeśli nie chcesz używać danych, które są przechowywane W twojego JSON do kosztownych operacji (filter/join/sort).
  Możesz przechowywać tę zawartość tak jak każda inna istnieje tylko. Przechowujemy wiele zdjęć jako Bloby, ale nie próbowalibyśmy filtrować wszystkich obrazów za pomocą kwiat...
Jeśli w ogóle nie przejmujesz się tym, co jest w środku (po prostu przechowuj i przeczytaj jako jedną informację)]}
• jeśli struktury są zmienne, co utrudniłoby tworzenie tabel fizycznych, a następnie pracę z danymi JSON.
• jeśli struktura jest głęboko zagnieżdżona, to Przechowywanie w tabelach fizycznych jest zbyt duże]}

Nie

• jeśli chcesz użyć danych wewnętrznych, tak jak używasz danych tabeli relacyjnej (filtr, indeksy, / align = "left" / ..)
• jeśli chcesz przechowywać duplikaty (utworzyć redundancję)
• ogólnie: jeśli napotkasz problemy z wydajnością (na pewno napotkasz je w wielu typowych scenariuszach!)

Możesz zacząć od JSON w kolumnie łańcuchowej lub jako BLOB I zmienić to na tabele fizyczne, gdy tego potrzebujesz. Moja magiczna Kryształowa kula mówi mi, że to może być jutro: - d

UPDATE

Tutaj znajdziesz kilka pomysłów na wydajność i przestrzeń dyskową: https://stackoverflow.com/a/47408528/5089204

Aktualizacja 2: więcej o wydajności...

Poniżej adresy obsługa JSON i XML w SQL-Server 2016

Kilka przemyśleń na ten temat:

Niektóre kontrole krzyżowe "eksperyment": {]}

• "eksperyment" mierzy wiele, ale nie Wydajność XML vs. JSON. Wykonywanie tej samej akcji i powtarzanie tego samego (niezmienionego) ciągu znaków nie jest realistycznym scenariuszem
• testowane przykłady są daleko do prostoty dla ogólnego stwierdzenia !
• odczytana wartość jest zawsze taka sama i nawet nie jest używana. Optymalizator to zobaczy...
Ani słowa o potężnym wsparciu! Znajdź produkt z danym ID w tablicy? JSON musi odczytać całość i użyć filtra używając WHERE, podczas gdy XML pozwoli na wewnętrzne XQuery predicate. Nie mówić o FLWOR...
• kod " eksperymentów " Jak jest{[24] } W moim systemie przywołuje: JSON wydaje się być 3x szybszy (ale nie 10x).
• dodanie /text() do XPath zmniejsza to do mniej niż 2x. W powiązanym artykule użytkownik "Mister Magoo" zwrócił już na to uwagę, ale tytuł click-bait jest nadal bez zmian...
• przy tak łatwym JSON jak podano w "eksperymencie" najszybsze czyste podejście T-SQL było kombinacją SUBSTRING i CHARINDEX:- D

Poniższy kod pokaże bardziej realistyczny eksperyment

XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML-XML]}
• JSON i XML są nieznacznie zmieniane (10000 numerów bieżących) i wstawiane do tabel.
• istnieje początkowe wywołanie obu tabel do avoid first-call-bias
• wszystkie 10000 wpisów jest odczytywanych, a pobrane wartości są wstawiane do innej tabeli.
• użycie GO 10 będzie przebiegać przez ten blok dziesięć razy, aby uniknąć first-call-bias

Ostateczny wynik jasno pokazuje, że JSON jest wolniejszy niż XML (nie tak dużo, około 1.5 x na wciąż bardzo prostym przykładzie).

Ostatnia wypowiedź:

• z zbyt uproszczonym przykładem w nieuzasadnionych okolicznościach JSON może być szybszy niż XML
• radzenie sobie z JSON jest czystą akcją łańcuchową , podczas gdy XML jest przetwarzany i przekształcany. Jest to dość kosztowne w pierwszej akcji, ale przyspieszy wszystko, gdy to zrobisz.
JSON może być lepszy w jednorazowej akcji (uniknięcie kosztów tworzenia wewnętrznej hierarchicznej reprezentacji XML)
• z jeszcze bardzo prostym, ale bardziej realistycznym przykładem XML będzie szybszy w prostym czytaniu
• ilekroć istnieje potrzeba odczytania określonego elementu z tablicy, aby filtrować wszystkie wpisy, w których dany ProductID jest zawarty w tablicy, lub aby poruszać się w górę iw dół ścieżki, JSON nie może się utrzymać. Musi być całkowicie parsowany z łańcucha-za każdym razem, gdy musisz go złapać...

Kod testu

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

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