Jak działa JSON_EXTRACT w języku SQL? Przykłady zastosowania

Jeśli kiedykolwiek pracowałeś z danymi w formacie JSON w bazach danych SQL, na pewno natknąłeś się na funkcję JSON_EXTRACT. Jest to niezwykle przydatne narzędzie, które pozwala wydobywać konkretne wartości ze struktury JSON. W tym artykule wyjaśnię, jak działa JSON_EXTRACT w języku SQL na praktycznych przykładach.

Czym jest JSON_EXTRACT?

Funkcja JSON_EXTRACT służy do pobierania danych z kolumny zawierającej treść w formacie JSON. Dzięki niej możemy uzyskać dostęp do pojedynczych wartości, tablic czy nawet zagnieżdżonych obiektów.

Podstawowa składnia tej funkcji wygląda tak:

JSON_EXTRACT(json_doc, path)

• json_doc – ciąg znaków w formacie JSON
• path – ścieżka do wartości, którą chcemy pobrać

Przykłady użycia JSON_EXTRACT

Aby lepiej zobrazować działanie JSON_EXTRACT, posłużę się kilkoma praktycznymi przykładami.

1. Pobieranie pojedynczej wartości

Załóżmy, że w naszej bazie mamy tabelę users, a w jednej z kolumn przechowujemy dane w formacie JSON:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_data JSON
);

Dodajmy przykładowe dane:

INSERT INTO users (user_data) VALUES 
('{"name": "Jan", "age": 30, "city": "Warszawa"}'),
('{"name": "Anna", "age": 25, "city": "Kraków"}');

Chcąc pobrać imię użytkownika, możemy wykonać zapytanie:

SELECT JSON_EXTRACT(user_data, '$.name') FROM users;

Wynik:

2. Pobieranie wartości zagnieżdżonych

Co jeśli mamy bardziej skomplikowaną strukturę danych?

INSERT INTO users (user_data) VALUES 
('{"name": "Kuba", "contact": {"email": "kuba@example.com", "phone": "123-456-789"}}');

Aby pobrać numer telefonu:

SELECT JSON_EXTRACT(user_data, '$.contact.phone') FROM users;

Wynik:

3. Pobieranie wartości z tablicy

JSON często zawiera tablice wartości. Załóżmy, że mamy następującą strukturę:

INSERT INTO users (user_data) VALUES 
('{"name": "Mateusz", "skills": ["SQL", "Python", "JavaScript"]}');

Aby pobrać pierwszy element z tablicy skills:

SELECT JSON_EXTRACT(user_data, '$.skills[0]') FROM users;

Wynik:

Różnica między JSON_EXTRACT a JSON_UNQUOTE

Być może zauważyłeś, że wartości zwracane przez JSON_EXTRACT są ujęte w cudzysłowy. Dzieje się tak, ponieważ funkcja zwraca wynik w typie JSON, nawet jeśli to zwykły ciąg znaków.

Jeśli chcemy pozbyć się cudzysłowów, możemy użyć JSON_UNQUOTE:

SELECT JSON_UNQUOTE(JSON_EXTRACT(user_data, '$.name')) FROM users;

Wynik:

Obsługa wartości null

Gdy ścieżka wskazana w JSON_EXTRACT nie istnieje w JSON, funkcja zwraca NULL.

SELECT JSON_EXTRACT(user_data, '$.non_existing_key') FROM users;

Wynik:

Podsumowanie

Funkcja JSON_EXTRACT w języku SQL to potężne narzędzie, które pozwala na skuteczne wydobywanie danych z obiektów JSON. Dzięki niej możemy:

• Pobierać pojedyncze wartości
• Przetwarzać zagnieżdżone obiekty
• Uzyskiwać dostęp do elementów tablic

Mam nadzieję, że teraz już wiesz, jak działa JSON_EXTRACT w języku SQL i jak go używać w praktyce.