{"id":13960,"date":"2022-06-13T07:00:36","date_gmt":"2022-06-13T05:00:36","guid":{"rendered":"https:\/\/sii.pl\/blog\/?p=13960"},"modified":"2023-07-05T17:21:57","modified_gmt":"2023-07-05T15:21:57","slug":"analizowanie-danych-json-w-bazie-danych-mssql-za-pomoca-sql","status":"publish","type":"post","link":"https:\/\/sii.pl\/blog\/analizowanie-danych-json-w-bazie-danych-mssql-za-pomoca-sql\/","title":{"rendered":"Analizowanie danych JSON w bazie danych MSSQL za pomoc\u0105 SQL"},"content":{"rendered":"\n<p>W tym artykule zajmiemy si\u0119 problemem por\u00f3wnywania zagnie\u017cd\u017conych w\u0142a\u015bciwo\u015bci w strukturach JSON przechowywanych w bazie danych Mirosoft SQL.<\/p>\n\n\n\n<p> U\u017cywaj\u0105c OPENJSON i funkcji okna, przeanalizujemy i przefiltrujemy warto\u015bci przechowywane wewn\u0105trz JSON za pomoc\u0105 j\u0119zyka SQL.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Kr\u00f3tkie wprowadzenie<\/h2>\n\n\n\n<p>Jednym z problem\u00f3w ustalania przyczyn b\u0142\u0119d\u00f3w w zachowaniu system\u00f3w, kt\u00f3rymi si\u0119 opiekujemy, jest zmienna natura danych w \u015brodowiskach produkcyjnych. Wraz z up\u0142ywem czasu informacje ulegaj\u0105 zmianom (najcz\u0119\u015bciej intencjonalnym) wykonanym przez u\u017cytkownik\u00f3w, ale tak\u017ce poprzez nieumy\u015blne b\u0142\u0119dy u\u017cytkownik\u00f3w oraz niewykryte b\u0142\u0119dy w oprogramowaniu.<\/p>\n\n\n\n<p>Szukaj\u0105c przyczyny, w pierwszej kolejno\u015bci eliminujemy te, kt\u00f3re s\u0105 najbardziej prawdopodobne oraz te, kt\u00f3re mo\u017cemy naj\u0142atwiej sprawdzi\u0107 eksperymentalnie.<\/p>\n\n\n\n<p><strong>Co zrobi\u0107 w sytuacji, gdy nie mo\u017cemy znale\u017a\u0107 takiej kombinacji okoliczno\u015bci, kt\u00f3ra wyt\u0142umaczy\u0142aby, dlaczego nasz system zachowa\u0142 si\u0119 inaczej ni\u017c powinien?<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Nietypowy przypadek<\/h2>\n\n\n\n<p>W takiej sytuacji znalaz\u0142em si\u0119 niedawno. U\u017cytkownik ko\u0144cowy zg\u0142osi\u0142 utrat\u0119 dost\u0119pu do cz\u0119\u015bci systemu, do kt\u00f3rej mia\u0142 dost\u0119p tydzie\u0144 wcze\u015bniej. Wed\u0142ug aktualnego stanu danych ta cz\u0119\u015b\u0107 systemu i tak nie powinna by\u0107 dla niego dost\u0119pna. Dodatkowo, wszystkie relacje danych powi\u0105zane bezpo\u015brednio z u\u017cytkownikiem nie uleg\u0142y zmianie, a sam system nie zosta\u0142 w tym czasie zaktualizowany.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Zmiana drzewiastej struktury danych<\/h3>\n\n\n\n<p>Jedn\u0105 z mo\u017cliwych przyczyn, kt\u00f3r\u0105 musia\u0142em sprawdzi\u0107, by\u0142a <strong>zmiana drzewiastej struktury danych<\/strong>, kt\u00f3ra nie jest bezpo\u015brednio powi\u0105zana ze wspomnianym u\u017cytkownikiem, ale mog\u0142a wp\u0142yn\u0105\u0107 na zmiany w poziomie dost\u0119pu.<\/p>\n\n\n\n<p>W moim projekcie ka\u017cda zmiana w systemie jest logowana w bazie danych Microsoft SQL. Pojedyncza tabela dla ka\u017cdej operacji zawiera:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>wpis o tym, kto zaktualizowa\u0142 rekord,<\/li>\n\n\n\n<li>dok\u0142adn\u0105 dat\u0119 modyfikacji,<\/li>\n\n\n\n<li>JSON zawieraj\u0105cy obiekt DTO (Data Transfer Object \u2013 obiekt, kt\u00f3ry przechowuje dane na potrzeby wymiany pomi\u0119dzy r\u00f3\u017cnymi komponentami systemu), kt\u00f3ry spowodowa\u0142 t\u0119 zmian\u0119.<\/li>\n<\/ul>\n\n\n\n<p>Przyczyna przechowywania JSON w bazie danych Microsoft SQL w moim projekcie nie jest istotna, ale istotne jest to, \u017ce <strong>ograniczone wsparcie Microsoft SQL dla przetwarzania JSON powoduje w tym wypadku problem.<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Struktura DTO<\/h3>\n\n\n\n<p>\u017beby zobrazowa\u0107 k\u0142opot, musz\u0119 kr\u00f3tko wyja\u015bni\u0107 struktur\u0119 konkretnego DTO i w jaki spos\u00f3b wp\u0142ywa ona na rekordy w bazie danych.<\/p>\n\n\n\n<p>Moje DTO, opr\u00f3cz podstawowych w\u0142a\u015bciwo\u015bci takich jak \u201eId\u201d czy \u201eName\u201d, zawiera tablic\u0119 z identyfikatorami powi\u0105zanych rekord\u00f3w. Kiedy odpowiedni serwis otrzyma zapytanie API z tym DTO, opr\u00f3cz aktualizacji warto\u015bci rekordu w bazie, zaktualizuje tak\u017ce relacje pomi\u0119dzy tym rekordem a rekordami, kt\u00f3rych identyfikatory zawarte s\u0105 w wspomnianej tablicy.<\/p>\n\n\n\n<p>Poszukiwana przeze mnie zmiana by\u0142a zatem na<strong> drugim poziomie zagnie\u017cd\u017cenia obiektu<\/strong>, wewn\u0105trz tablicy z identyfikatorami powi\u0105zanych rekord\u00f3w.<\/p>\n\n\n\n<p><strong>W jaki spos\u00f3b znale\u017a\u0107 zmiany, kt\u00f3re s\u0105 przechowywane g\u0142\u0119biej w strukturze JSON w kolumnie tabeli?<\/strong> \u017beby si\u0119 tego dowiedzie\u0107 pos\u0142u\u017cymy si\u0119 przyk\u0142adem.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Przygotowanie danych<\/h2>\n\n\n\n<p>W pierwszej kolejno\u015bci stw\u00f3rzmy uproszczon\u0105 wersj\u0119 tabeli log\u00f3w. Na podstawie struktury tabeli z problemu, kt\u00f3ry musia\u0142em rozwi\u0105za\u0107, b\u0119dzie wygl\u0105da\u0107 tak:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE TABLE DomainObjectLog (\n&#x5B;Id] &#x5B;uniqueidentifier] NOT NULL,\n&#x5B;EntityId] &#x5B;uniqueidentifier] NOT NULL,\n&#x5B;Type] &#x5B;nvarchar](250) NOT NULL,\n&#x5B;Entity] &#x5B;nvarchar](max) NOT NULL,\n&#x5B;Description] nvarchar(max) NOT NULL,\n&#x5B;Timestamp] datetime NOT NULL)\n<\/pre><\/div>\n\n\n<ul class=\"wp-block-list\">\n<li>Id \u2013 przechowuje identyfikator wpisu tabeli<\/li>\n\n\n\n<li>EntityId \u2013 identyfikator obiektu, kt\u00f3rego wpis dotyczy<\/li>\n\n\n\n<li>Type \u2013 okre\u015bla rodzaj obiektu<\/li>\n\n\n\n<li>Entity \u2013 zawiera JSON z ca\u0142ym obiektem<\/li>\n\n\n\n<li>Description \u2013 opis operacji<\/li>\n\n\n\n<li>Timestamp \u2013 data operacji.<\/li>\n<\/ul>\n\n\n\n<p>Nast\u0119pnie potrzebujemy danych, na kt\u00f3rych b\u0119dziemy mogli pracowa\u0107:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nINSERT INTO &#x5B;DomainObjectLog] (&#x5B;Id], &#x5B;EntityId], &#x5B;Type], &#x5B;Entity], &#x5B;Description], &#x5B;Timestamp]) VALUES\n(\nNEWID(),\n'50D8047D-F00B-4850-BC2C-8E39728B5949',\n'Product',\nN'{\n\"Id\": \"50D8047D-F00B-4850-BC2C-8E39728B5949\",\n\"Name\": \"Training set\",\n\"SubProducts\": &#x5B;]\n}',\n'Create product',\n'2022-05-05 08:00:00'),\n(\nNEWID(),\n'50D8047D-F00B-4850-BC2C-8E39728B5949',\n'Product',\nN'{\n\"Id\": \"50D8047D-F00B-4850-BC2C-8E39728B5949\",\n\"Name\": \"Training set\",\n\"SubProducts\": &#x5B;\"6644C9F4-5A1D-4BBE-AF7D-257317DA2B3D\"]\n}',\n'Update product',\n'2022-05-05 09:00:00'), (\nNEWID(),\n'50D8047D-F00B-4850-BC2C-8E39728B5949',\n'Product',\nN'{\n\"Id\": \"50D8047D-F00B-4850-BC2C-8E39728B5949\",\n\"Name\": \"Training set\",\n\"SubProducts\": &#x5B;\"6644C9F4-5A1D-4BBE-AF7D-257317DA2B3D\", \"DFA4975E-81B4-4A33-A949-7F352C52082B\", \"4E5AD486-970E-4EA3-99A9-F565556DB834\", \"D6D4A64A-13D1-463E-9F8A-D59618E01F57\"]\n}',\n'Update product',\n'2022-05-05 10:00:00'),\n(\nNEWID(),\n'50D8047D-F00B-4850-BC2C-8E39728B5949',\n'Product',\nN'{\n\"Id\": \"50D8047D-F00B-4850-BC2C-8E39728B5949\",\n\"Name\": \"Training set\",\n\"SubProducts\": &#x5B;\"6644C9F4-5A1D-4BBE-AF7D-257317DA2B3D\", \"4E5AD486-970E-4EA3-99A9-F565556DB834\", \"D6D4A64A-13D1-463E-9F8A-D59618E01F57\"]\n}',\n'Update product',\n'2022-05-06 08:00:00'),\n(\nNEWID(),\n'50D8047D-F00B-4850-BC2C-8E39728B5949',\n'Product',\nN'{\n\"Id\": \"50D8047D-F00B-4850-BC2C-8E39728B5949\",\n\"Name\": \"Training set\",\n\"SubProducts\": &#x5B;\"6644C9F4-5A1D-4BBE-AF7D-257317DA2B3D\"]\n}',\n'Update product',\n'2022-05-06 9:00:00');\n<\/pre><\/div>\n\n\n<p>W ten spos\u00f3b do tabeli log\u00f3w dopisali\u015bmy operacj\u0119 utworzenia i modyfikacji produktu o uproszczonej strukturze sk\u0142adaj\u0105cej si\u0119 z:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Id \u2013 klucza produktu (tego samego, kt\u00f3ry przechowujemy w EntityId),<\/li>\n\n\n\n<li>Name \u2013 nazwy produktu,<\/li>\n\n\n\n<li>SubProducts \u2013 tablicy z kluczami innych produkt\u00f3w.<\/li>\n<\/ul>\n\n\n\n<p>Oczywi\u015bcie, przy takiej ilo\u015bci danych wyszukiwanie zmian jest proste. Mo\u017cemy zwyczajnie przejrze\u0107 rekordy i prze\u015bledzi\u0107, kiedy zosta\u0142y usuni\u0119te poszczeg\u00f3lne identyfikatory z tablic \u201cSubProducts\u201d. Jednak <strong>przy du\u017cej liczbie wpis\u00f3w i identyfikator\u00f3w musimy ten proces upro\u015bci\u0107<\/strong>.<\/p>\n\n\n\n<p>Moj\u0105 propozycj\u0105 jest u\u017cycie SQL i wbudowanych funkcji w bazie danych Microsoft SQL.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">U\u017cycie SQL \u2013 przydatne funkcje<\/h2>\n\n\n\n<p>Do napisania takiego zapytania b\u0119dziemy potrzebowali kilku sk\u0142adnik\u00f3w:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/openjson-transact-sql?view=sql-server-ver15\" rel=\"nofollow\" >OPENJSON<\/a> \u2013 funkcja pozwala na przetwarzanie JSON i traktowanie ich jak tabel.<\/li>\n\n\n\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/queries\/select-over-clause-transact-sql?view=sql-server-ver15\" rel=\"nofollow\" >Funkcja okna<\/a> \u2013 u\u0142atwia przetwarzanie rekord\u00f3w jako grup.<\/li>\n\n\n\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/lag-transact-sql?view=sql-server-ver15)\" rel=\"nofollow\" >LAG<\/a> \u2013 funkcja pozwoli nam zajrze\u0107 do innego rekordu z tego samego \u201eokna\u201d danych.<\/li>\n\n\n\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/queries\/from-transact-sql?view=sql-server-ver15\" rel=\"nofollow\" >Operator OUTER APPLY<\/a> \u2013 u\u0142atwia \u0142\u0105czenie danych w przypadkach, kiedy nie mo\u017cemy lub nie chcemy u\u017cy\u0107 zwyk\u0142ego JOIN.<\/li>\n\n\n\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/string-agg-transact-sql?view=sql-server-ver15\" rel=\"nofollow\" >STRING_AGG<\/a> \u2013 pozwala na \u0142\u0105czenie rekord\u00f3w wewn\u0105trz podzapyta\u0144. Przy jej pomocy dodatkowo na koniec zrobimy alternatywn\u0105 wersj\u0119 prezentacji wynik\u00f3w.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Rozwi\u0105zanie problemu<\/h2>\n\n\n\n<p>Pierwsz\u0105 operacj\u0105, kt\u00f3r\u0105 musimy wykona\u0107, jest po\u0142\u0105czenie ka\u017cdego rekordu z poprzednim odpowiadaj\u0105cym mu rekordem:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT *,\nLAG(&#x5B;Entity], 1) OVER (PARTITION BY &#x5B;EntityId] ORDER BY &#x5B;Timestamp] ASC) AS EntityLag\nFROM &#x5B;DomainObjectLog]\n<\/pre><\/div>\n\n\n<p>Rozszerzamy w ten spos\u00f3b dane tabeli DomainObjectLog o now\u0105 kolumn\u0119 nazwan\u0105 EntityLag, kt\u00f3ra zawiera warto\u015b\u0107 kolumny Entity z poprzedniego wpisu.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u201cPARTITION BY [EntityId]\u201d okre\u015bla, \u017ce zakresem s\u0105 wszystkie wpisy w tabelce o takiej samej warto\u015bci EntityId.<\/li>\n\n\n\n<li>\u201cORDER BY [Timestamp] ASC\u201d okre\u015bla kolejno\u015b\u0107 rekord\u00f3w wewn\u0105trz tego zakresu.<\/li>\n\n\n\n<li>\u201cLAG([Entity], 1)\u201d odczytuje warto\u015b\u0107 kolumny Entity z poprzedniego rekordu, drugi parametr \u201c1\u201d okre\u015bla, o ile rekord\u00f3w funkcja musi si\u0119 cofn\u0105\u0107.<\/li>\n<\/ul>\n\n\n\n<p>Rezultatem takiego zapytania jest:<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><a href=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/1-1.png\"><img decoding=\"async\" width=\"1560\" height=\"126\" src=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/1-1.png\" alt=\"Tabela wynikowa\" class=\"wp-image-13979\" srcset=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/1-1.png 1560w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/1-1-300x24.png 300w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/1-1-1024x83.png 1024w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/1-1-768x62.png 768w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/1-1-1536x124.png 1536w\" sizes=\"(max-width: 1560px) 100vw, 1560px\" \/><\/a><\/figure>\n\n\n\n<p>Nast\u0119pnie, musimy zamkn\u0105\u0107 to zapytanie w podzapytanie i po\u0142\u0105czy\u0107 z wyci\u0105gni\u0119tymi z struktury JSON poszczeg\u00f3lnymi identyfikatorami z tablic SubProducts:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT *\nFROM\n(\nSELECT *,\nLAG(&#x5B;Entity], 1) OVER (PARTITION BY &#x5B;EntityId] ORDER BY &#x5B;Timestamp] ASC) AS EntityLag\nFROM &#x5B;DomainObjectLog]\n) AS t\nOUTER APPLY\n(\nSELECT sl.SubProductId\nFROM OPENJSON(t.&#x5B;EntityLag], 'lax $.\"SubProducts\"') WITH (SubProductId uniqueidentifier '$') AS sl\n) AS c\n<\/pre><\/div>\n\n\n<ul class=\"wp-block-list\">\n<li>\u201eOUTER APPLY\u201d ka\u017cdy rekord z podzapytania z aliasem \u201et\u201d po\u0142\u0105czy z ka\u017cdym rekordem podzapytania z aliasem \u201ec\u201d.<\/li>\n<\/ul>\n\n\n\n<p>Rozbijmy podzapytanie z aliasem \u201cc\u201d na cz\u0119\u015bci:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u201cOPENJSON(t.[EntityLag], \u2018lax $.\u201dSubProducts\u201d\u2019)\u201d spowoduje, \u017ce warto\u015b\u0107 kolumny EntityLag zostanie potraktowana jako JSON i zwr\u00f3ci konkretne informacje, kt\u00f3rych okre\u015blenie zachodzi poprzez \u015bcie\u017ck\u0119 podan\u0105 w drugim parametrze .<\/li>\n\n\n\n<li>S\u0142owo \u201elax\u201d okre\u015bla tryb dzia\u0142ania funkcji. W tym wypadku struktura b\u0119dzie traktowana \u201elu\u017ano\u201d i nie spowoduje b\u0142\u0119du, je\u015bli w kt\u00f3rym\u015b rekordzie \u015bcie\u017cka b\u0119dzie nieprawid\u0142owa. Drugim trybem jest \u201estrict\u201d, kt\u00f3ry spowoduje zako\u0144czenie zapytania b\u0142\u0119dem, je\u015bli kt\u00f3rakolwiek \u015bcie\u017cka nie b\u0119dzie prawid\u0142owa \u2013 ale na nasze potrzeby wystarczy \u201clax\u201d).<\/li>\n\n\n\n<li>\u201c$.\u201dSubProducts\u201d\u201d okre\u015bla \u015bcie\u017ck\u0119 do zawarto\u015bci w\u0142a\u015bciwo\u015bci o nazwie \u201eSubProducts\u201d<\/li>\n\n\n\n<li>\u201cWITH (SubProductId uniqueidentifier \u2018$\u2019)\u201d okre\u015bla oczekiwan\u0105 struktur\u0119 danych, kt\u00f3r\u0105 ma zwr\u00f3ci\u0107 funkcja OPENJSON. Je\u015bli tego nie zdefiniujemy, w wyniku dostaniemy struktur\u0119 key, value, type.<\/li>\n<\/ul>\n\n\n\n<p>Pozosta\u0142a cz\u0119\u015b\u0107 to okre\u015blenie aliasu \u201esl\u201d dla wyniku funkcji oraz zapytanie o kolumn\u0119 sl.SubProductId.<\/p>\n\n\n\n<p>Rezultatem takiego zapytania b\u0119dzie:<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><a href=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/2.png\"><img decoding=\"async\" width=\"1757\" height=\"223\" src=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/2.png\" alt=\"Tabela wynikowa\" class=\"wp-image-13973\" srcset=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/2.png 1757w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/2-300x38.png 300w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/2-1024x130.png 1024w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/2-768x97.png 768w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/2-1536x195.png 1536w\" sizes=\"(max-width: 1757px) 100vw, 1757px\" \/><\/a><\/figure>\n\n\n\n<p>Jak wida\u0107 <strong>liczba rekord\u00f3w zwi\u0119kszy\u0142a si\u0119<\/strong>. W tej chwili ka\u017cdy rekord jest zwielokrotniony tyle razy, ile element\u00f3w zawiera si\u0119 w odpowiadaj\u0105cej tablicy SubProducts wewn\u0105trz JSON w kolumnie EntityLag.<\/p>\n\n\n\n<p>Poniewa\u017c chcemy sprawdzi\u0107, kt\u00f3re elementy zosta\u0142y usuni\u0119te, w ostatnim kroku musimy rozbudowa\u0107 blok OUTER APPLY o dodatkowy JOIN:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT *\nFROM\n(\nSELECT *,\nLAG(&#x5B;Entity], 1) OVER (PARTITION BY &#x5B;EntityId] ORDER BY &#x5B;Timestamp] ASC) AS EntityLag\nFROM &#x5B;DomainObjectLog]\n) AS t\nOUTER APPLY\n(\nSELECT sl.SubProductId\nFROM OPENJSON(t.&#x5B;EntityLag], 'lax $.\"SubProducts\"') WITH (SubProductId uniqueidentifier '$') AS sl\nLEFT JOIN OPENJSON(t.&#x5B;Entity], 'lax $.\"SubProducts\"') WITH (SubProductId uniqueidentifier '$') AS s\nON s.&#x5B;SubProductId] = sl.&#x5B;SubProductId]\nWHERE s.&#x5B;SubProductId] IS NULL\n) AS c\n<\/pre><\/div>\n\n\n<p>Wewn\u0105trz podzapytania dopisali\u015bmy:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u201cOPENJSON(t.[Entity], \u2018lax $.\u201dSubProducts\u201d\u2019) WITH (SubProductId uniqueIdentifier \u2018$\u2019) as s\u201d \u2013 czyli analogiczn\u0105 operacj\u0119 wyci\u0105gania identyfikator\u00f3w SubProducts, ale z obecnej wersji obiektu \u2013 z kolumny Entity.<\/li>\n\n\n\n<li>\u201cLEFT JOIN (\u2026) ON s.[SubProductId] = sl.[SubProductId]\u201d<\/li>\n<\/ul>\n\n\n\n<p>Po\u0142\u0105czyli\u015bmy wyniki obu funkcji OPENJSON, u\u017cywaj\u0105c nowych kolumn SubProductId.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u201cWHERE s.[SubProductId] is null\u201d<\/li>\n<\/ul>\n\n\n\n<p>Znale\u017ali\u015bmy te rekordy, kt\u00f3re istnia\u0142y w poprzedniej wersji (pochodz\u0105ce z kolumny EntityLag), a nie istniej\u0105 w obecnej wersji (pochodz\u0105ce z kolumny Entity)<\/p>\n\n\n\n<p>Gdy wykonamy takie zapytanie, otrzymamy:<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><a href=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/3.png\"><img decoding=\"async\" width=\"1760\" height=\"138\" src=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/3.png\" alt=\"Tabela wynikowa\" class=\"wp-image-13974\" srcset=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/3.png 1760w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/3-300x24.png 300w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/3-1024x80.png 1024w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/3-768x60.png 768w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/3-1536x120.png 1536w\" sizes=\"(max-width: 1760px) 100vw, 1760px\" \/><\/a><\/figure>\n\n\n\n<p>W wynikach tego zapytania widzimy, \u017ce w dacie 2022-05-06 08:00:00 usuni\u0119ty zosta\u0142 z tablicy SubProducts identyfikator DFA4975E-81B4-4A33-A949-7F352C52082B, a godzin\u0119 p\u00f3\u017aniej (2022-05-06 09:00:00) usuni\u0119te zosta\u0142y dwa identyfikatory 4E5AD486-970E-4EA3-99A9-F565556DB834 oraz D6D4A64A-13D1-463E-9F8A-D59618E01F57.<\/p>\n\n\n\n<p>Je\u015bli jednak preferujemy widzie\u0107 wszystkie usuni\u0119te identyfikatory w jednym wierszu, bez duplikowania log\u00f3w, mo\u017cemy zmodyfikowa\u0107 zapytanie i zamieni\u0107 \u201cSELECT sl.SubProductId\u201d na \u201cSELECT STRING_AGG(CAST(sl.SubProductId as varchar(max)), \u2018, \u2018) as RemovedSubProducts\u201d wewn\u0105trz podzapytania z aliasem \u201cc\u201d, otrzymuj\u0105c zapytanie:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT *\nFROM\n(\nSELECT *,\nLAG(&#x5B;Entity], 1) OVER (PARTITION BY &#x5B;EntityId] ORDER BY &#x5B;Timestamp] ASC) AS EntityLag\nFROM &#x5B;DomainObjectLog]\n) AS t\nOUTER APPLY\n(\nSELECT STRING_AGG(CAST(sl.SubProductId as varchar(max)), ', ') AS RemovedSubProducts\nFROM OPENJSON(t.&#x5B;EntityLag], 'lax $.\"SubProducts\"') WITH (SubProductId uniqueidentifier '$') AS sl\nLEFT JOIN OPENJSON(t.&#x5B;Entity], 'lax $.\"SubProducts\"') WITH (SubProductId uniqueidentifier '$') AS s\nON s.&#x5B;SubProductId] = sl.&#x5B;SubProductId]\nWHERE s.&#x5B;SubProductId] IS NULL\n) AS c\n<\/pre><\/div>\n\n\n<p>Kt\u00f3rego rezultatem b\u0119dzie:<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><a href=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/4.png\"><img decoding=\"async\" width=\"1755\" height=\"127\" src=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/4.png\" alt=\"Tabela wynikowa\" class=\"wp-image-13975\" srcset=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/4.png 1755w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/4-300x22.png 300w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/4-1024x74.png 1024w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/4-768x56.png 768w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/4-1536x111.png 1536w\" sizes=\"(max-width: 1755px) 100vw, 1755px\" \/><\/a><\/figure>\n\n\n\n<p>Takich wynik\u00f3w nie mo\u017cna \u0142\u0105czy\u0107 z innymi rekordami r\u00f3wnie \u0142atwo, co w poprzedniej wersji, ale <strong>je\u015bli wiemy, czego szukamy, to taka prezentacja jest bardziej czytelna.<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Zmiana kierunku \u2013 modyfikacja zapytania o dodane informacje<\/h2>\n\n\n\n<p>Je\u015bli potrzebujemy, takie zapytanie mo\u017cna \u0142atwo zmodyfikowa\u0107, \u017ceby sprawdzi\u0107, co zosta\u0142o dodane do tablic:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT *\nFROM\n(\nSELECT *,\nLAG(&#x5B;Entity], 1) OVER (PARTITION BY EntityId ORDER BY &#x5B;Timestamp] ASC) AS EntityLag\nFROM &#x5B;DomainObjectLog]\n) AS t\nOUTER APPLY\n(\nSELECT s.SubProductId\nFROM OPENJSON(t.&#x5B;Entity], 'lax $.\"SubProducts\"') WITH (SubProductId uniqueidentifier '$') AS s\nLEFT JOIN\nOPENJSON(t.&#x5B;EntityLag], 'lax $.\"SubProducts\"') WITH (SubProductId uniqueidentifier '$') AS sl\nON s.&#x5B;SubProductId] = sl.&#x5B;SubProductId]\nWHERE sl.&#x5B;SubProductId] IS NULL\n) AS c\n<\/pre><\/div>\n\n\n<p>Zamieniaj\u0105c kolejno\u015b\u0107 \u0142\u0105czenia wynik\u00f3w OPENJSON i kolumn\u0119, po kt\u00f3rej filtrujemy, otrzymujemy identyfikatory, kt\u00f3re zosta\u0142y dopisane do tablicy SubProducts:<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><a href=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/5.png\"><img decoding=\"async\" width=\"1753\" height=\"160\" src=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/5.png\" alt=\"Tabela wynikowa\" class=\"wp-image-13976\" srcset=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/5.png 1753w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/5-300x27.png 300w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/5-1024x93.png 1024w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/5-768x70.png 768w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/5-1536x140.png 1536w\" sizes=\"(max-width: 1753px) 100vw, 1753px\" \/><\/a><\/figure>\n\n\n\n<p>Analogicznie, zapytanie o bardziej czytelne wyniki b\u0119dzie wygl\u0105da\u0142o tak:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT *\nFROM\n(\nSELECT *,\nLAG(&#x5B;Entity], 1) OVER (PARTITION BY &#x5B;EntityId] ORDER BY &#x5B;Timestamp] ASC) AS EntityLag\nFROM &#x5B;DomainObjectLog]\n) AS t\nOUTER APPLY\n(\nSELECT STRING_AGG(CAST(s.SubProductId as varchar(max)), ', ') AS AddedSubProducts\nFROM OPENJSON(t.&#x5B;Entity], 'lax $.\"SubProducts\"') WITH (SubProductId uniqueidentifier '$') AS s\nLEFT JOIN\nOPENJSON(t.&#x5B;EntityLag], 'lax $.\"SubProducts\"') WITH (SubProductId uniqueidentifier '$') AS sl\nON s.&#x5B;SubProductId] = sl.&#x5B;SubProductId]\nWHERE sl.&#x5B;SubProductId] IS NULL\n) AS c\n<\/pre><\/div>\n\n\n<p>Otrzymujemy wyniki w takiej formie:<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><a href=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/6.png\"><img decoding=\"async\" width=\"1740\" height=\"130\" src=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/6.png\" alt=\"Tabela wynikowa\" class=\"wp-image-13977\" srcset=\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/6.png 1740w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/6-300x22.png 300w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/6-1024x77.png 1024w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/6-768x57.png 768w, https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/6-1536x115.png 1536w\" sizes=\"(max-width: 1740px) 100vw, 1740px\" \/><\/a><\/figure>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Podsumowanie<\/strong><\/h2>\n\n\n\n<p>Otrzymuj\u0105c identyfikatory zmienionych rekord\u00f3w, mo\u017cemy r\u0119cznie lub za pomoc\u0105 innego zapytania sprawdzi\u0107, czy s\u0105 to rekordy, kt\u00f3re mog\u0142y spowodowa\u0107 zg\u0142oszony problem.<\/p>\n\n\n\n<p>Wyszukiwanie tak zwanych \u201ccorner case\u201d cz\u0119sto nie jest \u0142atwe i mo\u017ce zaj\u0105\u0107 du\u017co czasu. Nawet cz\u0119\u015bciowa automatyzacja analizy log\u00f3w systemowych mo\u017ce ten proces u\u0142atwi\u0107.<\/p>\n\n\n\n<p>Je\u015bli w naszym systemie logi ze zserializowanymi obiektami w postaci JSON przechowywane s\u0105 w bazie Microsoft SQL, mo\u017cemy u\u017cy\u0107 j\u0119zyka SQL w przedstawiony spos\u00f3b, aby unikn\u0105\u0107 tworzenia dedykowanych narz\u0119dzi do ich przetwarzania.<\/p>\n\n\n\n<p>\u0141\u0105cz\u0105c proste narz\u0119dzia takie jak funkcj\u0119 OPENJSON i funkcje okna, mo\u017cna w prosty spos\u00f3b doda\u0107 kolejny wymiar do naszych zapyta\u0144.<\/p>\n\n\n<div class=\"kk-star-ratings kksr-auto kksr-align-left kksr-valign-bottom\"\n    data-payload='{&quot;align&quot;:&quot;left&quot;,&quot;id&quot;:&quot;13960&quot;,&quot;slug&quot;:&quot;default&quot;,&quot;valign&quot;:&quot;bottom&quot;,&quot;ignore&quot;:&quot;&quot;,&quot;reference&quot;:&quot;auto&quot;,&quot;class&quot;:&quot;&quot;,&quot;count&quot;:&quot;6&quot;,&quot;legendonly&quot;:&quot;&quot;,&quot;readonly&quot;:&quot;&quot;,&quot;score&quot;:&quot;5&quot;,&quot;starsonly&quot;:&quot;&quot;,&quot;best&quot;:&quot;5&quot;,&quot;gap&quot;:&quot;11&quot;,&quot;greet&quot;:&quot;&quot;,&quot;legend&quot;:&quot;5\\\/5 ( votes: 6)&quot;,&quot;size&quot;:&quot;18&quot;,&quot;title&quot;:&quot;Analizowanie danych JSON w bazie danych MSSQL za pomoc\u0105 SQL&quot;,&quot;width&quot;:&quot;139.5&quot;,&quot;_legend&quot;:&quot;{score}\\\/{best} ( {votes}: {count})&quot;,&quot;font_factor&quot;:&quot;1.25&quot;}'>\n            \n<div class=\"kksr-stars\">\n    \n<div class=\"kksr-stars-inactive\">\n            <div class=\"kksr-star\" data-star=\"1\" style=\"padding-right: 11px\">\n            \n\n<div class=\"kksr-icon\" style=\"width: 18px; height: 18px;\"><\/div>\n        <\/div>\n            <div class=\"kksr-star\" data-star=\"2\" style=\"padding-right: 11px\">\n            \n\n<div class=\"kksr-icon\" style=\"width: 18px; height: 18px;\"><\/div>\n        <\/div>\n            <div class=\"kksr-star\" data-star=\"3\" style=\"padding-right: 11px\">\n            \n\n<div class=\"kksr-icon\" style=\"width: 18px; height: 18px;\"><\/div>\n        <\/div>\n            <div class=\"kksr-star\" data-star=\"4\" style=\"padding-right: 11px\">\n            \n\n<div class=\"kksr-icon\" style=\"width: 18px; height: 18px;\"><\/div>\n        <\/div>\n            <div class=\"kksr-star\" data-star=\"5\" style=\"padding-right: 11px\">\n            \n\n<div class=\"kksr-icon\" style=\"width: 18px; height: 18px;\"><\/div>\n        <\/div>\n    <\/div>\n    \n<div class=\"kksr-stars-active\" style=\"width: 139.5px;\">\n            <div class=\"kksr-star\" style=\"padding-right: 11px\">\n            \n\n<div class=\"kksr-icon\" style=\"width: 18px; height: 18px;\"><\/div>\n        <\/div>\n            <div class=\"kksr-star\" style=\"padding-right: 11px\">\n            \n\n<div class=\"kksr-icon\" style=\"width: 18px; height: 18px;\"><\/div>\n        <\/div>\n            <div class=\"kksr-star\" style=\"padding-right: 11px\">\n            \n\n<div class=\"kksr-icon\" style=\"width: 18px; height: 18px;\"><\/div>\n        <\/div>\n            <div class=\"kksr-star\" style=\"padding-right: 11px\">\n            \n\n<div class=\"kksr-icon\" style=\"width: 18px; height: 18px;\"><\/div>\n        <\/div>\n            <div class=\"kksr-star\" style=\"padding-right: 11px\">\n            \n\n<div class=\"kksr-icon\" style=\"width: 18px; height: 18px;\"><\/div>\n        <\/div>\n    <\/div>\n<\/div>\n                \n\n<div class=\"kksr-legend\" style=\"font-size: 14.4px;\">\n            5\/5 ( votes: 6)    <\/div>\n    <\/div>\n","protected":false},"excerpt":{"rendered":"<p>W tym artykule zajmiemy si\u0119 problemem por\u00f3wnywania zagnie\u017cd\u017conych w\u0142a\u015bciwo\u015bci w strukturach JSON przechowywanych w bazie danych Mirosoft SQL. U\u017cywaj\u0105c OPENJSON &hellip; <a class=\"continued-btn\" href=\"https:\/\/sii.pl\/blog\/analizowanie-danych-json-w-bazie-danych-mssql-za-pomoca-sql\/\">Continued<\/a><\/p>\n","protected":false},"author":369,"featured_media":13985,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_editorskit_title_hidden":false,"_editorskit_reading_time":0,"_editorskit_is_block_options_detached":false,"_editorskit_block_options_position":"{}","inline_featured_image":false,"footnotes":""},"categories":[1314],"tags":[287,513,1284,1007],"class_list":["post-13960","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-development-na-twardo","tag-software-development","tag-analiza-danych","tag-json","tag-sql"],"acf":[],"aioseo_notices":[],"republish_history":[],"featured_media_url":"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/06\/Analizowanie-danych-JSON-w-bazie-danych-MSSQL-za-pomoca-SQL.png","category_names":["Development na twardo"],"_links":{"self":[{"href":"https:\/\/sii.pl\/blog\/wp-json\/wp\/v2\/posts\/13960"}],"collection":[{"href":"https:\/\/sii.pl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sii.pl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sii.pl\/blog\/wp-json\/wp\/v2\/users\/369"}],"replies":[{"embeddable":true,"href":"https:\/\/sii.pl\/blog\/wp-json\/wp\/v2\/comments?post=13960"}],"version-history":[{"count":2,"href":"https:\/\/sii.pl\/blog\/wp-json\/wp\/v2\/posts\/13960\/revisions"}],"predecessor-version":[{"id":22686,"href":"https:\/\/sii.pl\/blog\/wp-json\/wp\/v2\/posts\/13960\/revisions\/22686"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sii.pl\/blog\/wp-json\/wp\/v2\/media\/13985"}],"wp:attachment":[{"href":"https:\/\/sii.pl\/blog\/wp-json\/wp\/v2\/media?parent=13960"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sii.pl\/blog\/wp-json\/wp\/v2\/categories?post=13960"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sii.pl\/blog\/wp-json\/wp\/v2\/tags?post=13960"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}