API do danych JSON to interfejs SQL Server umożliwiający zapytania i modyfikację danych zapisanych w tym formacie tekstowym.
JSON jako format danych wymienianych z SQL Server
API do danych JSON jest sposobem komunikacji, w którym aplikacja przekazuje do SQL Server dokument zapisany jako tekst JSON albo odbiera odpowiedź w takim samym formacie. JSON składa się z obiektów, tablic, par właściwość–wartość, wartości tekstowych, liczb, wartości logicznych oraz wartości null. W SQL Server dane JSON nie są przechowywane w osobnym natywnym typie kolumny. Najczęściej zapisuje się je w kolumnie typu nvarchar, a poprawność składni i strukturę dokumentu sprawdza za pomocą wbudowanych funkcji.
Takie rozwiązanie pozwala połączyć relacyjny model tabel z formatem używanym przez aplikacje internetowe, programy desktopowe i inne usługi. Tabela może przechowywać identyfikator rekordu, datę utworzenia oraz cały dokument JSON, natomiast jego poszczególne właściwości mogą być odczytywane dopiero podczas wykonywania zapytania. Alternatywnie wybrane wartości z dokumentu można rozdzielić na osobne kolumny relacyjne, gdy są często filtrowane, sortowane albo łączone z innymi tabelami.
Odczyt właściwości i tablic za pomocą funkcji T-SQL
SQL Server udostępnia funkcję JSON_VALUE do pobierania pojedynczej wartości skalarnej z dokumentu, na przykład tekstu, liczby albo wartości logicznej. Ścieżka JSON wskazuje położenie właściwości, dlatego zapytanie może odczytać zarówno element najwyższego poziomu, jak i wartość zagnieżdżoną w obiekcie. Funkcja JSON_QUERY służy natomiast do pobierania obiektu lub tablicy JSON jako fragmentu dokumentu. Rozróżnienie tych funkcji ma znaczenie, ponieważ tablica nie jest pojedynczą wartością skalarną.
Do przekształcania tablic JSON w wiersze służy OPENJSON. Funkcja może zwrócić elementy tablicy wraz z ich kluczami, indeksami i wartościami albo mapować strukturę dokumentu do określonego schematu kolumn przy użyciu klauzuli WITH. Dzięki temu dane z dokumentu mogą zostać połączone z tabelami relacyjnymi w instrukcjach SELECT, JOIN lub INSERT. Przy zagnieżdżonych tablicach często stosuje się kilka wywołań OPENJSON, gdzie kolejne wywołanie pracuje na fragmencie zwróconym przez poprzednie.
Przed odczytem warto sprawdzić dokument funkcją ISJSON. Zwraca ona informację, czy przekazany tekst ma poprawny format JSON. Sama poprawność składni nie oznacza jednak, że dokument zawiera wymagane właściwości, właściwe typy danych albo oczekiwane wartości. Walidacja struktury powinna więc obejmować również sprawdzenie obecności konkretnych pól, możliwość konwersji wartości oraz zgodność danych z regułami aplikacji.
Modyfikowanie dokumentu bez przebudowy całego rekordu
Do zmiany zawartości tekstu JSON w SQL Server służy funkcja JSON_MODIFY. Umożliwia ona dodanie właściwości, zmianę istniejącej wartości albo usunięcie elementu, zależnie od użytej ścieżki i wartości przekazanej do funkcji. Wynikiem jest nowy tekst JSON, dlatego podczas aktualizacji kolumny należy uwzględnić instrukcję UPDATE. Jeżeli właściwość ma zawierać obiekt lub tablicę JSON, trzeba prawidłowo rozróżnić tekst zwykły od fragmentu JSON, aby nie zapisać struktury jako dodatkowo zacytowanego łańcucha.
Generowanie odpowiedzi JSON może odbywać się przy użyciu klauzul FOR JSON PATH albo FOR JSON AUTO. FOR JSON PATH pozwala precyzyjniej określić nazwy właściwości i układ obiektów na podstawie aliasów kolumn oraz zagnieżdżonych zapytań. FOR JSON AUTO buduje strukturę w większym stopniu na podstawie relacji między tabelami. Parametr INCLUDE_NULL_VALUES może wymusić umieszczanie w odpowiedzi właściwości o wartości null, ponieważ domyślnie wartości null mogą zostać pominięte.
Projektując API, należy ustalić, czy JSON jest tylko formatem transportowym, czy również głównym sposobem przechowywania danych. Dokument przechowywany w jednej kolumnie jest elastyczny, ale utrudnia kontrolę typów, egzekwowanie relacji i wydajne wyszukiwanie po wielu właściwościach. Dane często wykorzystywane w warunkach zapytań warto przechowywać relacyjnie albo udostępnić przez kolumny obliczane i odpowiednie indeksy. Przy aktualizacji trzeba także rozważyć współbieżność, ponieważ równoczesne modyfikowanie tego samego dokumentu może prowadzić do nadpisania zmian.
Diagnostyka błędów w przepływie JSON
Problemy z API do danych JSON mogą powstać na kilku etapach: podczas serializacji obiektu w aplikacji, przesyłania treści, zapisu tekstu w SQL Server, odczytu ścieżki JSON albo konwersji typu. Typowe objawy to niepoprawny format dokumentu, brak oczekiwanej właściwości, pusty wynik funkcji, błąd konwersji liczby lub daty oraz różnica między pojedynczym obiektem a tablicą. Analiza powinna porównywać rzeczywistą treść JSON z używaną ścieżką, nazwami właściwości, wielkością liter przy określonym porównywaniu oraz oczekiwanym typem wyniku.
Przy diagnostyce zapytania warto najpierw potwierdzić wynik ISJSON, następnie odczytać prostą właściwość przez JSON_VALUE, a później sprawdzić strukturę tablicy przez OPENJSON. Należy też zweryfikować, czy tekst nie został ucięty przez niewłaściwy typ kolumny, czy aplikacja nie koduje dokumentu podwójnie oraz czy odpowiedź generowana przez FOR JSON ma oczekiwany kształt. W przypadku diagnostyki komputera, na którym działa aplikacja korzystająca z takiego API, sprawdza się również konfigurację połączenia, logi błędów, wersję sterownika oraz zgodność zapytań z używaną wersją SQL Server.
Naprawa może polegać na korekcie ścieżki JSON, zmianie mapowania w OPENJSON, poprawieniu konwersji typów, odtworzeniu brakujących danych albo przebudowie zapytania generującego odpowiedź. Przy pracy serwisowej istotne jest zachowanie kopii konfiguracji i danych przed zmianą oraz rozdzielenie błędu aplikacji od problemu systemowego. Neutralna diagnostyka laptopa lub komputera PC powinna obejmować działanie programu, komunikację z bazą i zapis logów, bez utożsamiania błędu formatu JSON z automatyczną awarią sprzętu.
