JSON

JSONB_OBJECT_KEYS

PostgreSQL

Mengembalikan semua keys (nama property) dari JSON object sebagai set of text. Berguna untuk introspeksi struktur JSON dinamis.

Tipe hasil: setof text

Syntax

SQL

JSONB_OBJECT_KEYS(jsonb_object)

Parameter

jsonb_objectjsonbwajib

JSON object yang akan diekstrak key-nya

Contoh Penggunaan

List Semua Keys

SQL

1 SELECT JSONB_OBJECT_KEYS({"name": "John", "age": 30, "city": "Jakarta"}::jsonb);

Mengembalikan semua key dari object.

Hasil

jsonb_object_keys
name
age
city

Cek Keberadaan Key

SQL

1 SELECT EXISTS (
2   SELECT 1 
3   FROM JSONB_OBJECT_KEYS(data) AS k 
4   WHERE k = email
5 ) AS has_email
6 FROM users;

Mengecek apakah key tertentu ada dalam JSON.

Hasil

has_email
true

Analisis Schema JSON

SQL

1 SELECT 
2   key,
3   COUNT(*) AS occurrences
4 FROM 
5   documents,
6   JSONB_OBJECT_KEYS(metadata) AS key
7 GROUP BY key
8 ORDER BY occurrences DESC;

Menganalisis key apa saja yang ada dalam kolom JSON.

Kembali ke PostgreSQL Functions

1	SELECT EXISTS (
2	SELECT 1
3	FROM JSONB_OBJECT_KEYS(data) AS k
4	WHERE k = email
5	) AS has_email
6	FROM users;

1	SELECT
2	key,
3	COUNT(*) AS occurrences
4	FROM
5	documents,
6	JSONB_OBJECT_KEYS(metadata) AS key
7	GROUP BY key
8	ORDER BY occurrences DESC;