Sql Json veriler üzeridne çalisma

1. JSON ve JSONB Farkı

JSON: Ham JSON string saklar. (Daha yavaş, ama orijinal format korunur.)
JSONB: Binary formda saklar. (Daha hızlı arama, indexleme yapılır, genelde tercih edilen bu.)

Örnek kayıt:

2. JSON Veri Erişimi

-> : JSON objesi/dizisine erişir (JSON döner)
->> : Text olarak döner
#> : Derin JSON objesine erişim (JSON döner)
#>> : Derin JSON objesine erişim (Text döner)

Örnekler:

3. JSON Filtreleme (WHERE şartı)

(
SELECT addr->>’city’
FROM jsonb_array_elements(u.profile->’adresses’) addr
WHERE (addr->>’primary’)::boolean = true
LIMIT 1
) AS primary_city

FROM users u
WHERE EXISTS (
SELECT 1
FROM books o
WHERE o.user_id = u.id
AND o.created_at >= now() – interval ’30 days’
);

4. JSON İçinde Arama (Exist / Contain)

@> : İçeriyor mu? (contain)
? : Key var mı?
?| : Bu keylerden biri var mı?
?& : Bu keylerin hepsi var mı?

5. JSON Güncelleme

PostgreSQL 9.5+ sürümlerinde jsonb_set var:

SELECT
u.id AS user_id,

— En çok okunan kitap—–?
(
SELECT product_name
FROM (
SELECT
(item->>’product_name’) AS product_name,
SUM((item->>’ktp’)::int) AS total_ktp,
ROW_NUMBER() OVER (ORDER BY SUM((item->>’ktp’)::int) DESC) AS rn
FROM books o
CROSS JOIN LATERAL jsonb_array_elements(o.items->’items’) AS item
WHERE o.user_id = u.id
AND o.created_at >= now() – interval ’30 days’
GROUP BY item->>’product_name’
) t
WHERE rn = 1
) AS top_product,

6. JSON Dizileriyle Çalışma

-- skills dizisini patlat (unnest)

SELECT jsonb_array_elements(profile->'skills') AS skill

FROM users;

— Dizide “SQL” olan kullanıcıları getir
SELECT * FROM users
WHERE profile–>‘skills’ @> ‘[“SQL”]’;

———–

SELECT
u.id AS user_id,

— En çok okunan kitap—–?
(
SELECT product_name
FROM (
SELECT
(item->>’product_name’) AS product_name,
SUM((item->>’ktp’)::int) AS total_ktp,
ROW_NUMBER() OVER (ORDER BY SUM((item->>’ktp’)::int) DESC) AS rn
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.items->’items’) AS item
WHERE o.user_id = u.id
AND o.created_at >= now() – interval ’30 days’
GROUP BY item->>’product_name’
) t
WHERE rn = 1
) AS top_product,

— Kullanıcının primary_city’si
(
SELECT addr->>’city’
FROM jsonb_array_elements(u.profile->’adresses’) addr
WHERE (addr->>’primary’)::boolean = true
LIMIT 1
) AS primary_city

FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
AND o.created_at >= now() – interval ’30 days’
);

—————

7. JSONB Indexleme (Performans için)

Büyük verilerde mutlaka index kullan:

8. JSON Path (PostgreSQL 12+)

XPath benzeri sorgular yazabilirsin: