Analizowanie danych JSON w bazie danych MSSQL za pomocą SQL

W tym artykule zajmiemy się problemem porównywania zagnieżdżonych właściwości w strukturach JSON przechowywanych w bazie danych Mirosoft SQL.

Używając OPENJSON i funkcji okna, przeanalizujemy i przefiltrujemy wartości przechowywane wewnątrz JSON za pomocą języka SQL.

Krótkie wprowadzenie

Jednym z problemów ustalania przyczyn błędów w zachowaniu systemów, którymi się opiekujemy, jest zmienna natura danych w środowiskach produkcyjnych. Wraz z upływem czasu informacje ulegają zmianom (najczęściej intencjonalnym) wykonanym przez użytkowników, ale także poprzez nieumyślne błędy użytkowników oraz niewykryte błędy w oprogramowaniu.

Szukając przyczyny, w pierwszej kolejności eliminujemy te, które są najbardziej prawdopodobne oraz te, które możemy najłatwiej sprawdzić eksperymentalnie.

Co zrobić w sytuacji, gdy nie możemy znaleźć takiej kombinacji okoliczności, która wytłumaczyłaby, dlaczego nasz system zachował się inaczej niż powinien?

Nietypowy przypadek

W takiej sytuacji znalazłem się niedawno. Użytkownik końcowy zgłosił utratę dostępu do części systemu, do której miał dostęp tydzień wcześniej. Według aktualnego stanu danych ta część systemu i tak nie powinna być dla niego dostępna. Dodatkowo, wszystkie relacje danych powiązane bezpośrednio z użytkownikiem nie uległy zmianie, a sam system nie został w tym czasie zaktualizowany.

Zmiana drzewiastej struktury danych

Jedną z możliwych przyczyn, którą musiałem sprawdzić, była zmiana drzewiastej struktury danych, która nie jest bezpośrednio powiązana ze wspomnianym użytkownikiem, ale mogła wpłynąć na zmiany w poziomie dostępu.

W moim projekcie każda zmiana w systemie jest logowana w bazie danych Microsoft SQL. Pojedyncza tabela dla każdej operacji zawiera:

• wpis o tym, kto zaktualizował rekord,
• dokładną datę modyfikacji,
• JSON zawierający obiekt DTO (Data Transfer Object – obiekt, który przechowuje dane na potrzeby wymiany pomiędzy różnymi komponentami systemu), który spowodował tę zmianę.

Przyczyna przechowywania JSON w bazie danych Microsoft SQL w moim projekcie nie jest istotna, ale istotne jest to, że ograniczone wsparcie Microsoft SQL dla przetwarzania JSON powoduje w tym wypadku problem.

Struktura DTO

Żeby zobrazować kłopot, muszę krótko wyjaśnić strukturę konkretnego DTO i w jaki sposób wpływa ona na rekordy w bazie danych.

Moje DTO, oprócz podstawowych właściwości takich jak „Id” czy „Name”, zawiera tablicę z identyfikatorami powiązanych rekordów. Kiedy odpowiedni serwis otrzyma zapytanie API z tym DTO, oprócz aktualizacji wartości rekordu w bazie, zaktualizuje także relacje pomiędzy tym rekordem a rekordami, których identyfikatory zawarte są w wspomnianej tablicy.

Poszukiwana przeze mnie zmiana była zatem na drugim poziomie zagnieżdżenia obiektu, wewnątrz tablicy z identyfikatorami powiązanych rekordów.

W jaki sposób znaleźć zmiany, które są przechowywane głębiej w strukturze JSON w kolumnie tabeli? Żeby się tego dowiedzieć posłużymy się przykładem.

Przygotowanie danych

W pierwszej kolejności stwórzmy uproszczoną wersję tabeli logów. Na podstawie struktury tabeli z problemu, który musiałem rozwiązać, będzie wyglądać tak:

CREATE TABLE DomainObjectLog (
[Id] [uniqueidentifier] NOT NULL,
[EntityId] [uniqueidentifier] NOT NULL,
[Type] [nvarchar](250) NOT NULL,
[Entity] [nvarchar](max) NOT NULL,
[Description] nvarchar(max) NOT NULL,
[Timestamp] datetime NOT NULL)

• Id – przechowuje identyfikator wpisu tabeli
• EntityId – identyfikator obiektu, którego wpis dotyczy
• Type – określa rodzaj obiektu
• Entity – zawiera JSON z całym obiektem
• Description – opis operacji
• Timestamp – data operacji.

Następnie potrzebujemy danych, na których będziemy mogli pracować:

INSERT INTO [DomainObjectLog] ([Id], [EntityId], [Type], [Entity], [Description], [Timestamp]) VALUES
(
NEWID(),
'50D8047D-F00B-4850-BC2C-8E39728B5949',
'Product',
N'{
"Id": "50D8047D-F00B-4850-BC2C-8E39728B5949",
"Name": "Training set",
"SubProducts": []
}',
'Create product',
'2022-05-05 08:00:00'),
(
NEWID(),
'50D8047D-F00B-4850-BC2C-8E39728B5949',
'Product',
N'{
"Id": "50D8047D-F00B-4850-BC2C-8E39728B5949",
"Name": "Training set",
"SubProducts": ["6644C9F4-5A1D-4BBE-AF7D-257317DA2B3D"]
}',
'Update product',
'2022-05-05 09:00:00'), (
NEWID(),
'50D8047D-F00B-4850-BC2C-8E39728B5949',
'Product',
N'{
"Id": "50D8047D-F00B-4850-BC2C-8E39728B5949",
"Name": "Training set",
"SubProducts": ["6644C9F4-5A1D-4BBE-AF7D-257317DA2B3D", "DFA4975E-81B4-4A33-A949-7F352C52082B", "4E5AD486-970E-4EA3-99A9-F565556DB834", "D6D4A64A-13D1-463E-9F8A-D59618E01F57"]
}',
'Update product',
'2022-05-05 10:00:00'),
(
NEWID(),
'50D8047D-F00B-4850-BC2C-8E39728B5949',
'Product',
N'{
"Id": "50D8047D-F00B-4850-BC2C-8E39728B5949",
"Name": "Training set",
"SubProducts": ["6644C9F4-5A1D-4BBE-AF7D-257317DA2B3D", "4E5AD486-970E-4EA3-99A9-F565556DB834", "D6D4A64A-13D1-463E-9F8A-D59618E01F57"]
}',
'Update product',
'2022-05-06 08:00:00'),
(
NEWID(),
'50D8047D-F00B-4850-BC2C-8E39728B5949',
'Product',
N'{
"Id": "50D8047D-F00B-4850-BC2C-8E39728B5949",
"Name": "Training set",
"SubProducts": ["6644C9F4-5A1D-4BBE-AF7D-257317DA2B3D"]
}',
'Update product',
'2022-05-06 9:00:00');

W ten sposób do tabeli logów dopisaliśmy operację utworzenia i modyfikacji produktu o uproszczonej strukturze składającej się z:

• Id – klucza produktu (tego samego, który przechowujemy w EntityId),
• Name – nazwy produktu,
• SubProducts – tablicy z kluczami innych produktów.

Oczywiście, przy takiej ilości danych wyszukiwanie zmian jest proste. Możemy zwyczajnie przejrzeć rekordy i prześledzić, kiedy zostały usunięte poszczególne identyfikatory z tablic “SubProducts”. Jednak przy dużej liczbie wpisów i identyfikatorów musimy ten proces uprościć.

Moją propozycją jest użycie SQL i wbudowanych funkcji w bazie danych Microsoft SQL.

Użycie SQL – przydatne funkcje

Do napisania takiego zapytania będziemy potrzebowali kilku składników:

• OPENJSON – funkcja pozwala na przetwarzanie JSON i traktowanie ich jak tabel.
• Funkcja okna – ułatwia przetwarzanie rekordów jako grup.
• LAG – funkcja pozwoli nam zajrzeć do innego rekordu z tego samego „okna” danych.
• Operator OUTER APPLY – ułatwia łączenie danych w przypadkach, kiedy nie możemy lub nie chcemy użyć zwykłego JOIN.
• STRING_AGG – pozwala na łączenie rekordów wewnątrz podzapytań. Przy jej pomocy dodatkowo na koniec zrobimy alternatywną wersję prezentacji wyników.

Rozwiązanie problemu

Pierwszą operacją, którą musimy wykonać, jest połączenie każdego rekordu z poprzednim odpowiadającym mu rekordem:

SELECT *,
LAG([Entity], 1) OVER (PARTITION BY [EntityId] ORDER BY [Timestamp] ASC) AS EntityLag
FROM [DomainObjectLog]

Rozszerzamy w ten sposób dane tabeli DomainObjectLog o nową kolumnę nazwaną EntityLag, która zawiera wartość kolumny Entity z poprzedniego wpisu.

• “PARTITION BY [EntityId]” określa, że zakresem są wszystkie wpisy w tabelce o takiej samej wartości EntityId.
• “ORDER BY [Timestamp] ASC” określa kolejność rekordów wewnątrz tego zakresu.
• “LAG([Entity], 1)” odczytuje wartość kolumny Entity z poprzedniego rekordu, drugi parametr “1” określa, o ile rekordów funkcja musi się cofnąć.

Rezultatem takiego zapytania jest:

Następnie, musimy zamknąć to zapytanie w podzapytanie i połączyć z wyciągniętymi z struktury JSON poszczególnymi identyfikatorami z tablic SubProducts:

SELECT *
FROM
(
SELECT *,
LAG([Entity], 1) OVER (PARTITION BY [EntityId] ORDER BY [Timestamp] ASC) AS EntityLag
FROM [DomainObjectLog]
) AS t
OUTER APPLY
(
SELECT sl.SubProductId
FROM OPENJSON(t.[EntityLag], 'lax $."SubProducts"') WITH (SubProductId uniqueidentifier '$') AS sl
) AS c

• „OUTER APPLY” każdy rekord z podzapytania z aliasem „t” połączy z każdym rekordem podzapytania z aliasem „c”.

Rozbijmy podzapytanie z aliasem “c” na części:

• “OPENJSON(t.[EntityLag], ‘lax $.”SubProducts”’)” spowoduje, że wartość kolumny EntityLag zostanie potraktowana jako JSON i zwróci konkretne informacje, których określenie zachodzi poprzez ścieżkę podaną w drugim parametrze .
• Słowo „lax” określa tryb działania funkcji. W tym wypadku struktura będzie traktowana „luźno” i nie spowoduje błędu, jeśli w którymś rekordzie ścieżka będzie nieprawidłowa. Drugim trybem jest „strict”, który spowoduje zakończenie zapytania błędem, jeśli którakolwiek ścieżka nie będzie prawidłowa – ale na nasze potrzeby wystarczy “lax”).
• “$.”SubProducts”” określa ścieżkę do zawartości właściwości o nazwie „SubProducts”
• “WITH (SubProductId uniqueidentifier ‘$’)” określa oczekiwaną strukturę danych, którą ma zwrócić funkcja OPENJSON. Jeśli tego nie zdefiniujemy, w wyniku dostaniemy strukturę key, value, type.

Pozostała część to określenie aliasu „sl” dla wyniku funkcji oraz zapytanie o kolumnę sl.SubProductId.

Rezultatem takiego zapytania będzie:

Jak widać liczba rekordów zwiększyła się. W tej chwili każdy rekord jest zwielokrotniony tyle razy, ile elementów zawiera się w odpowiadającej tablicy SubProducts wewnątrz JSON w kolumnie EntityLag.

Ponieważ chcemy sprawdzić, które elementy zostały usunięte, w ostatnim kroku musimy rozbudować blok OUTER APPLY o dodatkowy JOIN:

SELECT *
FROM
(
SELECT *,
LAG([Entity], 1) OVER (PARTITION BY [EntityId] ORDER BY [Timestamp] ASC) AS EntityLag
FROM [DomainObjectLog]
) AS t
OUTER APPLY
(
SELECT sl.SubProductId
FROM OPENJSON(t.[EntityLag], 'lax $."SubProducts"') WITH (SubProductId uniqueidentifier '$') AS sl
LEFT JOIN OPENJSON(t.[Entity], 'lax $."SubProducts"') WITH (SubProductId uniqueidentifier '$') AS s
ON s.[SubProductId] = sl.[SubProductId]
WHERE s.[SubProductId] IS NULL
) AS c

Wewnątrz podzapytania dopisaliśmy:

• “OPENJSON(t.[Entity], ‘lax $.”SubProducts”’) WITH (SubProductId uniqueIdentifier ‘$’) as s” – czyli analogiczną operację wyciągania identyfikatorów SubProducts, ale z obecnej wersji obiektu – z kolumny Entity.
• “LEFT JOIN (…) ON s.[SubProductId] = sl.[SubProductId]”

Połączyliśmy wyniki obu funkcji OPENJSON, używając nowych kolumn SubProductId.

• “WHERE s.[SubProductId] is null”

Znaleźliśmy te rekordy, które istniały w poprzedniej wersji (pochodzące z kolumny EntityLag), a nie istnieją w obecnej wersji (pochodzące z kolumny Entity)

Gdy wykonamy takie zapytanie, otrzymamy:

W wynikach tego zapytania widzimy, że w dacie 2022-05-06 08:00:00 usunięty został z tablicy SubProducts identyfikator DFA4975E-81B4-4A33-A949-7F352C52082B, a godzinę później (2022-05-06 09:00:00) usunięte zostały dwa identyfikatory 4E5AD486-970E-4EA3-99A9-F565556DB834 oraz D6D4A64A-13D1-463E-9F8A-D59618E01F57.

Jeśli jednak preferujemy widzieć wszystkie usunięte identyfikatory w jednym wierszu, bez duplikowania logów, możemy zmodyfikować zapytanie i zamienić “SELECT sl.SubProductId” na “SELECT STRING_AGG(CAST(sl.SubProductId as varchar(max)), ‘, ‘) as RemovedSubProducts” wewnątrz podzapytania z aliasem “c”, otrzymując zapytanie:

SELECT *
FROM
(
SELECT *,
LAG([Entity], 1) OVER (PARTITION BY [EntityId] ORDER BY [Timestamp] ASC) AS EntityLag
FROM [DomainObjectLog]
) AS t
OUTER APPLY
(
SELECT STRING_AGG(CAST(sl.SubProductId as varchar(max)), ', ') AS RemovedSubProducts
FROM OPENJSON(t.[EntityLag], 'lax $."SubProducts"') WITH (SubProductId uniqueidentifier '$') AS sl
LEFT JOIN OPENJSON(t.[Entity], 'lax $."SubProducts"') WITH (SubProductId uniqueidentifier '$') AS s
ON s.[SubProductId] = sl.[SubProductId]
WHERE s.[SubProductId] IS NULL
) AS c

Którego rezultatem będzie:

Takich wyników nie można łączyć z innymi rekordami równie łatwo, co w poprzedniej wersji, ale jeśli wiemy, czego szukamy, to taka prezentacja jest bardziej czytelna.

Zmiana kierunku – modyfikacja zapytania o dodane informacje

Jeśli potrzebujemy, takie zapytanie można łatwo zmodyfikować, żeby sprawdzić, co zostało dodane do tablic:

SELECT *
FROM
(
SELECT *,
LAG([Entity], 1) OVER (PARTITION BY EntityId ORDER BY [Timestamp] ASC) AS EntityLag
FROM [DomainObjectLog]
) AS t
OUTER APPLY
(
SELECT s.SubProductId
FROM OPENJSON(t.[Entity], 'lax $."SubProducts"') WITH (SubProductId uniqueidentifier '$') AS s
LEFT JOIN
OPENJSON(t.[EntityLag], 'lax $."SubProducts"') WITH (SubProductId uniqueidentifier '$') AS sl
ON s.[SubProductId] = sl.[SubProductId]
WHERE sl.[SubProductId] IS NULL
) AS c

Zamieniając kolejność łączenia wyników OPENJSON i kolumnę, po której filtrujemy, otrzymujemy identyfikatory, które zostały dopisane do tablicy SubProducts:

Analogicznie, zapytanie o bardziej czytelne wyniki będzie wyglądało tak:

SELECT *
FROM
(
SELECT *,
LAG([Entity], 1) OVER (PARTITION BY [EntityId] ORDER BY [Timestamp] ASC) AS EntityLag
FROM [DomainObjectLog]
) AS t
OUTER APPLY
(
SELECT STRING_AGG(CAST(s.SubProductId as varchar(max)), ', ') AS AddedSubProducts
FROM OPENJSON(t.[Entity], 'lax $."SubProducts"') WITH (SubProductId uniqueidentifier '$') AS s
LEFT JOIN
OPENJSON(t.[EntityLag], 'lax $."SubProducts"') WITH (SubProductId uniqueidentifier '$') AS sl
ON s.[SubProductId] = sl.[SubProductId]
WHERE sl.[SubProductId] IS NULL
) AS c

Otrzymujemy wyniki w takiej formie:

Podsumowanie

Otrzymując identyfikatory zmienionych rekordów, możemy ręcznie lub za pomocą innego zapytania sprawdzić, czy są to rekordy, które mogły spowodować zgłoszony problem.

Wyszukiwanie tak zwanych “corner case” często nie jest łatwe i może zająć dużo czasu. Nawet częściowa automatyzacja analizy logów systemowych może ten proces ułatwić.

Jeśli w naszym systemie logi ze zserializowanymi obiektami w postaci JSON przechowywane są w bazie Microsoft SQL, możemy użyć języka SQL w przedstawiony sposób, aby uniknąć tworzenia dedykowanych narzędzi do ich przetwarzania.

Łącząc proste narzędzia takie jak funkcję OPENJSON i funkcje okna, można w prosty sposób dodać kolejny wymiar do naszych zapytań.