Funkcje JSON a typy danych opisują sposób, w jaki SQL Server konwertuje wartości podczas pracy z danymi w tym formacie.
JSON w SQL Server a typ tekstowy danych
W SQL Server dokument JSON jest przechowywany przede wszystkim jako wartość typu znakowego, najczęściej nvarchar albo varchar. Sam fakt, że tekst ma składnię JSON, nie powoduje automatycznej zmiany jego typu na strukturę podobną do tabeli. Kolumnę zawierającą dokument można więc zdefiniować na przykład jako nvarchar(max), a funkcje JSON odczytują i analizują tekst dopiero podczas wykonania zapytania. Typ nvarchar pozwala przechowywać znaki Unicode, co ma znaczenie przy nazwach właściwości, wartościach tekstowych i danych zapisanych w języku polskim.
Funkcja ISJSON służy do sprawdzenia, czy przekazany tekst ma poprawną składnię JSON. Zwraca wartość całkowitą wskazującą wynik walidacji, ale nie potwierdza, że dokument zawiera wymagane właściwości, właściwe typy wartości ani poprawny model biznesowy. Poprawny składniowo dokument może nadal nie nadawać się do dalszego przetwarzania, jeżeli na przykład zamiast liczby zawiera tekst albo nie ma właściwości oczekiwanej przez aplikację. Walidację składni i kontrolę znaczenia danych należy zatem traktować jako dwa odrębne etapy.
Odczyt wartości przez JSON_VALUE i JSON_QUERY
JSON_VALUE pobiera pojedynczą wartość skalarną wskazaną ścieżką JSON. W SQL Server wynik tej funkcji ma typ nvarchar(4000). Dotyczy to także sytuacji, w której źródłowy dokument znajduje się w kolumnie nvarchar(max). Funkcja jest przeznaczona do odczytu wartości takich jak tekst, liczba, wartość logiczna lub null, ale wynik nadal jest zwracany jako tekst. Jeżeli aplikacja potrzebuje liczby, daty albo wartości logicznej w konkretnym typie SQL Server, należy zastosować jawną konwersję, na przykład TRY_CONVERT(int, JSON_VALUE(...)) albo TRY_CONVERT(datetime2, JSON_VALUE(...)).
Ograniczenie długości wyniku ma praktyczne znaczenie. Jeżeli wartość skalarna przekracza 4000 znaków, tryb zachowania funkcji zależy od użytej ścieżki: w trybie domyślnym lax może zostać zwrócona wartość NULL, natomiast tryb strict zgłasza błąd. Do pobierania obiektu lub tablicy służy JSON_QUERY. Funkcja ta zwraca fragment JSON jako tekst, a nie jako tabelę relacyjną. W praktyce JSON_VALUE jest używana do pojedynczych pól, zaś JSON_QUERY do zachowania zagnieżdżonej tablicy albo obiektu w postaci JSON.
Ścieżki JSON mogą wskazywać właściwości obiektu oraz elementy tablicy, na przykład $.klient.email lub $.pozycje[0].cena. Tryb lax, stosowany domyślnie, łagodniej traktuje brak ścieżki lub niezgodność rodzaju wskazanego elementu. Tryb strict jest przydatny, gdy brak wymaganej właściwości ma przerwać zapytanie i zostać zauważony przez warstwę aplikacji. Zły wybór trybu może powodować ciche pojawianie się wartości NULL zamiast jednoznacznego sygnału o błędzie danych.
Konwersja przy OPENJSON i schemacie WITH
OPENJSON rozbija dokument JSON na wiersze. W domyślnym użyciu zwraca kolumny key, value i type. Kolumna value ma charakter tekstowy, natomiast type określa rodzaj elementu według modelu JSON: między innymi wartość tekstową, liczbę, wartość logiczną, tablicę, obiekt albo null. Taki wynik jest wygodny do inspekcji dokumentu, lecz nie zapewnia jeszcze docelowych typów relacyjnych. Wartość odczytana z pola JSON nie staje się automatycznie kolumną typu int, decimal czy date.
Do zamiany dokumentu na kolumny o określonych typach służy klauzula WITH. Można w niej wskazać nazwę kolumny, typ SQL Server oraz ścieżkę JSON, na przykład cena decimal(12,2) '$.cena' albo data_zamowienia datetime2 '$.data'. SQL Server próbuje wtedy przekonwertować wartość JSON do zadeklarowanego typu. Jeżeli tekst nie przedstawia poprawnej liczby, daty lub innej wartości zgodnej z typem, zapytanie może zakończyć się błędem konwersji. Dlatego przy danych pochodzących z niejednorodnych źródeł często bezpieczniej jest najpierw pobrać tekst, a następnie użyć TRY_CONVERT, które zwraca NULL w przypadku nieudanej konwersji.
Typy JSON nie są równoważne wszystkim typom SQL Server. JSON rozróżnia między innymi tekst, liczbę, wartość logiczną, obiekt, tablicę i null, ale nie ma osobnego typu daty ani typu pieniężnego. Data zapisana jako tekst wymaga jawnej interpretacji, a liczba dziesiętna wymaga określenia precyzji i skali po stronie SQL Server. Również wartości logiczne powinny być mapowane świadomie, ponieważ sposób ich zapisu w danych wejściowych musi odpowiadać oczekiwaniom zapytania i aplikacji.
Spójność konwersji podczas zapisu i diagnostyki
JSON_MODIFY modyfikuje dokument i zwraca zmieniony tekst JSON, zwykle jako nvarchar(max). Funkcja nie zmienia typu kolumny przechowującej dokument, a wartość przekazana do aktualizacji podlega zasadom konwersji właściwym dla wyrażenia SQL Server. Jeżeli do JSON trafia liczba, data lub wartość logiczna, sposób przekazania jej do funkcji powinien być sprawdzony, ponieważ tekst utworzony wcześniej przez aplikację może zostać potraktowany inaczej niż wartość o typie SQL Server. Przy budowaniu JSON za pomocą FOR JSON SQL Server serializuje wartości relacyjne zgodnie z ich typami, a wynik także jest tekstem JSON.
Diagnostyka problemu powinna obejmować typ kolumny, rzeczywistą zawartość dokumentu, ścieżkę JSON oraz typ docelowy w klauzuli WITH lub w jawnej funkcji konwersji. Należy osobno sprawdzić przypadki braku właściwości, wartości null, pustego tekstu, liczby zapisanej jako tekst, niepoprawnego formatu daty i zagnieżdżonego obiektu użytego tam, gdzie oczekiwano wartości skalarnej. Pomocne jest porównanie wyników JSON_VALUE, JSON_QUERY i OPENJSON, ponieważ każda z tych funkcji odpowiada za inny rodzaj odczytu. Warto również sprawdzić, czy użycie trybu lax nie ukrywa błędu przez zwracanie NULL.
Przy awarii zapytania związanej z JSON analizuje się zarówno definicję tabeli, jak i kod wykonujący konwersję. Szczególnej uwagi wymagają niejawne konwersje, ograniczenie nvarchar(4000) dla JSON_VALUE, precyzja typu decimal oraz zgodność formatu daty z konwersją stosowaną przez SQL Server. Takie rozdzielenie warstwy tekstowego dokumentu, odczytu ścieżki i typowania relacyjnego pozwala ustalić, czy problem wynika z uszkodzonego JSON, błędnej ścieżki, niezgodnego typu danych czy niepoprawnej logiki zapytania.
