Microsoft SQL Server
Zapytania z danymi JSON

Używanie wartości z JSON w zapytaniu

Funkcja JSON_VALUE umożliwia pobranie danych z tekstu JSON na ścieżkę określoną jako drugi argument i użycie tej wartości w dowolnej części wybranego zapytania:

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'

Używanie wartości JSON w raportach

Po wyodrębnieniu wartości JSON z tekstu JSON można użyć ich w dowolnej części zapytania. Możesz utworzyć pewnego rodzaju raport na temat danych JSON z agregacjami grupującymi itp .:

select JSON_VALUE(Data, '$.Type') as type,
        AVG( cast(JSON_VALUE(Data, '$.ManufacturingCost') as float) ) as cost
from Product
group by JSON_VALUE(Data, '$.Type') 
having JSON_VALUE(Data, '$.Type') is not null

Odfiltruj zły tekst JSON z wyników zapytania

Jeśli część tekstu JSON może być niepoprawnie sformatowana, możesz usunąć te wpisy z zapytania za pomocą funkcji ISJSON.

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'
and ISJSON(Data) > 0

Zaktualizuj wartość w kolumnie JSON

Za pomocą funkcji JSON_MODIFY można zaktualizować wartość na pewnej ścieżce. Za pomocą tej funkcji można zmodyfikować oryginalną wartość komórki JSON w instrukcji UPDATE:

update Product
set Data = JSON_MODIFY(Data, '$.Price', 24.99)
where ProductID = 17;

Funkcja JSON_MODIFY zaktualizuje lub utworzy klucz ceny (jeśli nie istnieje). Jeśli nowa wartość to NULL, klucz zostanie usunięty. Funkcja JSON_MODIFY będzie traktować nową wartość jako ciąg znaków (unikaj znaków specjalnych, owiń ją podwójnymi cudzysłowami, aby utworzyć prawidłowy ciąg JSON). Jeśli twoją nową wartością jest fragment JSON, powinieneś owinąć go funkcją JSON_QUERY:

update Product
set Data = JSON_MODIFY(Data, '$.tags', JSON_QUERY('["promo","new"]'))
where ProductID = 17;

Funkcja JSON_QUERY bez drugiego parametru zachowuje się jak „rzut na JSON”. Ponieważ wynik JSON_QUERY jest prawidłowym fragmentem JSON (obiekt lub tablica), JSON_MODIFY nie zmieni wartości tej wartości po zmodyfikowaniu wejściowego JSON.

Dodaj nową wartość do tablicy JSON

Funkcji JSON_MODIFY można użyć do dodania nowej wartości do tablicy w JSON:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', "sales")
where ProductID = 17;

Nowa wartość zostanie dodana na końcu tablicy lub zostanie utworzona nowa tablica o wartości [„sprzedaż”]. Funkcja JSON_MODIFY będzie traktować nową wartość jako ciąg znaków (unikaj znaków specjalnych, owiń ją podwójnymi cudzysłowami, aby utworzyć prawidłowy ciąg JSON). Jeśli twoją nową wartością jest fragment JSON, powinieneś owinąć go funkcją JSON_QUERY:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', JSON_QUERY('{"type":"new"}'))
where ProductID = 17;

Funkcja JSON_QUERY bez drugiego parametru zachowuje się jak „rzut na JSON”. Ponieważ wynik JSON_QUERY jest prawidłowym fragmentem JSON (obiekt lub tablica), JSON_MODIFY nie zmieni wartości tej wartości po zmodyfikowaniu wejściowego JSON.

Stół JOIN z wewnętrzną kolekcją JSON

Jeśli masz „tabelę podrzędną” sformatowaną jako kolekcję JSON i zapisaną w wierszu jako kolumnę JSON, możesz rozpakować tę kolekcję, przekształcić ją w tabelę i połączyć z wierszem nadrzędnym. Zamiast standardowego operatora JOIN, powinieneś użyć CROSS APPLY. W tym przykładzie części produktu są sformatowane jako kolekcja obiektów JSON i przechowywane w kolumnie Dane:

select ProductID, Name, Size, Price, Quantity, PartName, Code
from Product
    CROSS APPLY OPENJSON(Data, '$.Parts') WITH (PartName varchar(20), Code varchar(5))

Wynik zapytania odpowiada łączeniu między tabelami produktów i części.

Znajdowanie wierszy zawierających wartość w tablicy JSON

W tym przykładzie tablica Tagi może zawierać różne słowa kluczowe, takie jak [„promocja”, „sprzedaż”], więc możemy otworzyć tę tablicę i filtrować wartości:

select ProductID, Name, Color, Size, Price, Quantity
from Product
    CROSS APPLY OPENJSON(Data, '$.Tags') 
where value = 'sales'

OPENJSON otworzy wewnętrzną kolekcję tagów i zwróci ją jako tabelę. Następnie możemy filtrować wyniki według pewnej wartości w tabeli.