JSON

JSONB_EXTRACT_PATH

PostgreSQL

Mengekstrak nilai dari nested JSON menggunakan path. EXTRACT_PATH mengembalikan jsonb, EXTRACT_PATH_TEXT mengembalikan text. Equivalent dengan operator #> dan #>>.

Tipe hasil: jsonb / text

Syntax

SQL

JSONB_EXTRACT_PATH(jsonb_data, VARIADIC path_elements text[])
JSONB_EXTRACT_PATH_TEXT(jsonb_data, VARIADIC path_elements text[])

Parameter

jsonb_datajsonbwajib

Data JSON sumber

path_elementstext[]wajib

Path ke nilai yang diinginkan

Contoh Penggunaan

Ekstrak Nested Value

SQL

1 SELECT JSONB_EXTRACT_PATH(
2   {"user": {"name": "John", "address": {"city": "Jakarta"}}}::jsonb,
3   user, address, city
4 );

Mengekstrak nilai city dari nested object.

Hasil

jsonb_extract_path
"Jakarta"

Sebagai Text

SQL

1 SELECT JSONB_EXTRACT_PATH_TEXT(
2   {"user": {"name": "John"}}::jsonb,
3   user, name
4 ) AS user_name;

EXTRACT_PATH_TEXT mengembalikan text tanpa quotes.

Hasil

user_name
John

Equivalent Operator

SQL

1 -- Kedua query ini equivalent
2 SELECT data #> {user,name} FROM users;
3 SELECT JSONB_EXTRACT_PATH(data, user, name) FROM users;

Operator #> adalah shorthand untuk JSONB_EXTRACT_PATH.

Fungsi Equivalen di Database Lain

JSON_EXTRACT

MySQL

GET_PATH

Snowflake

JSON_EXTRACT

BigQuery

JSON_EXTRACT

DuckDB

Fungsi Terkait

JSON_EXTRACT_PATHJSON_EXTRACT_PATH_TEXTJSONB_EXTRACT_PATH_TEXT

Kembali ke PostgreSQL Functions

1	SELECT JSONB_EXTRACT_PATH(
2	{"user": {"name": "John", "address": {"city": "Jakarta"}}}::jsonb,
3	user, address, city
4	);

1	SELECT JSONB_EXTRACT_PATH_TEXT(
2	{"user": {"name": "John"}}::jsonb,
3	user, name
4	) AS user_name;

1	-- Kedua query ini equivalent
2	SELECT data #> {user,name} FROM users;
3	SELECT JSONB_EXTRACT_PATH(data, user, name) FROM users;