JSON

JSON_EXTRACT_PATH

DuckDB

Mengekstrak nilai dari JSON menggunakan sequence of keys. PostgreSQL-compatible syntax.

Tipe hasil: JSONDiperbarui: 6 Jan 2026

Syntax

SQL

JSON_EXTRACT_PATH(json, path1, path2, ...)

Parameter

jsonJSON/VARCHARwajib

JSON object sumber

path1, path2, ...VARCHARwajib

Sequence of keys untuk navigate ke nilai

Contoh Penggunaan

Extract Nested Value

SQL

1 SELECT JSON_EXTRACT_PATH(
2   '{"user": {"profile": {"name": "Budi", "city": "Jakarta"}}}',
3   'user', 'profile', 'name'
4 ) as name;

Navigate nested JSON dengan sequence of keys.

Hasil

name
"Budi"

Extract dari Kolom JSON

SQL

1 SELECT
2   id,
3   JSON_EXTRACT_PATH(data, 'config', 'settings', 'theme') as theme,
4   JSON_EXTRACT_PATH(data, 'config', 'settings', 'language') as language
5 FROM app_configs;

Extract multiple nested values dari kolom JSON.

Hasil

id	theme	language
1	"dark"	"id"
2	"light"	"en"

Handling Missing Keys

SQL

1 SELECT
2   JSON_EXTRACT_PATH('{"a": {"b": 1}}', 'a', 'b') as found,
3   JSON_EXTRACT_PATH('{"a": {"b": 1}}', 'a', 'c') as not_found,
4   COALESCE(
5     JSON_EXTRACT_PATH('{"a": {"b": 1}}', 'a', 'c'),
6     JSON('"default"')
7   ) as with_default;

Key yang tidak ada mengembalikan NULL.

Hasil

found	not_found	with_default
1	NULL	"default"

Fungsi Terkait

JSON_EXTRACT_PATH_TEXTJSON_EXTRACT

Kembali ke DuckDB Functions

1	SELECT JSON_EXTRACT_PATH(
2	'{"user": {"profile": {"name": "Budi", "city": "Jakarta"}}}',
3	'user', 'profile', 'name'
4	) as name;

1	SELECT
2	id,
3	JSON_EXTRACT_PATH(data, 'config', 'settings', 'theme') as theme,
4	JSON_EXTRACT_PATH(data, 'config', 'settings', 'language') as language
5	FROM app_configs;

1	SELECT
2	JSON_EXTRACT_PATH('{"a": {"b": 1}}', 'a', 'b') as found,
3	JSON_EXTRACT_PATH('{"a": {"b": 1}}', 'a', 'c') as not_found,
4	COALESCE(
5	JSON_EXTRACT_PATH('{"a": {"b": 1}}', 'a', 'c'),
6	JSON('"default"')
7	) as with_default;