SQL 2016: JSON w SQL

31-Maj-2016

Świat SQL to świat SQL, ale od czasu do tego świata wchodzi coś z zaświatów. Pierwszym takim gościem był XML, a wraz z nim typ XML, OPENXML, wbudowane metody typu XML, kóre pozwalały wykonywać na nim operacje ale również składnia FOR XML. I super, tylko XMLa, SQLowcy zwykle nie lubią…

Od wersji 2016 mamy nowego gościa: JSON. JSON ma o wiele prostszą składnię niż XML i jego obsługa w SQL też jest prostsza może więc da się go polubić?

Po pierwsze wynik zapytania można konwertować do postaci JSON. Składniowo działa to tak jak FOR XML. Masz do wyboru format AUTO lub PATH:

FOR_JSON

W przypadku FOR PATH możesz określić root-element, który spowoduje utworzenie JSON-owej tablicy obiektów JSON-owych. Alias kolumn określi jakie właściwości będzie posiadał utworzony obiekt.

Oprócz tego mamy funkcję logiczną ISJSON sprawdzającą, czyokreślona wartość tekstowa zawiera w sobie napis, jaki da się zrozimieć jako JSON:

JSON_01Mamy też funkcję wyłuskującą pojedynczą wartość z napisu JSON:

JSON_02

Jeżeli masz do czynienia z listą obiektów, to należy wskazać o który z nich chodzi (numerujemy od 0). Jeżeli wartość nie jest wartością złożoną, to odwołujemy się do niej np tak: $.SalesTeam[1].BusinessEntityID. Jeżeli właściwość jest złożona to aby dostać się do właściwości właściwości użyj dodatkowej kropki: $.SalesTeam.Fullname.FirstName. Zauważ, że w tekście występuje pole z numerem identyfikacyjnym ID, a w zapytaniu omyłkowo chciano wyodrębnić BusinessEntityID. W takim przypadku nie ma błędu, jedynie zwrócona będzie wartość NULL.

Do JSON można też pisać „zapytania”:

JSON_03

Tak wyglądałby wynik do poprzednio zdefiniowanego tekstu JSON o ile użyjemy funkcji JSON_QUERY.

No i wreszcie coś dal administratorów, którzy też muszą jakoś żyć, gdy programiści zaczną stosować to, co wymyślił Microsoft. Pola JSON można poindeksować. Nie ma jednak jakiś cudownych specjalnych metod na indeksowanie tego typu. Po prostu tworzysz kolumnę wyliczaną i do wyliczenia wartości używasz funkcji JSON_VALUE.

ALTER TABLE MyTable ADD ExtractedName AS JSON_VALUE(SomeJsonText, '$.Name')

Następnie budujemy indeks na tej kolumnie:

CREATE INDEX idx_json_ExtractedName ON MyTable(json_ExtractedName)

Indeks będzie wykorzystany, gdy w zapytaniu użyjesz dokłądnie takiego samego wywołania funkcji JSON_VALUE:

SELECT * FROM MyTable WHERE JSON_VALUE(SomeJsonText, '$.Name') = 'Smith'

 

Dodaj komentarz:

Autor: Rafał Kraik