JSON

JSONB_TYPEOF

PostgreSQL

Mengembalikan tipe data dari nilai JSON sebagai text. Tipe yang mungkin: object, array, string, number, boolean, null.

Tipe hasil: text

Syntax

SQL

JSONB_TYPEOF(jsonb_value)

Parameter

jsonb_valuejsonbwajib

Nilai JSON yang akan dicek tipenya

Contoh Penggunaan

Cek Tipe JSON

SQL

1 SELECT 
2   JSONB_TYPEOF({}::jsonb) AS obj,
3   JSONB_TYPEOF([]::jsonb) AS arr,
4   JSONB_TYPEOF("hello"::jsonb) AS str,
5   JSONB_TYPEOF(123::jsonb) AS num,
6   JSONB_TYPEOF(true::jsonb) AS bool,
7   JSONB_TYPEOF(null::jsonb) AS nil;

Mengecek berbagai tipe nilai JSON.

Hasil

obj	arr	str	num	bool	nil
object	array	string	number	boolean	null

Validasi Tipe Data

SQL

1 SELECT *
2 FROM api_responses
3 WHERE JSONB_TYPEOF(data->items) = array;

Filter records yang field items-nya adalah array.

Hasil

Hanya mengembalikan rows dimana items adalah array.

Handle Tipe Berbeda

SQL

1 SELECT 
2   id,
3   CASE JSONB_TYPEOF(value)
4     WHEN array THEN JSONB_ARRAY_LENGTH(value)::text
5     WHEN object THEN object
6     WHEN string THEN value #>> {}
7     ELSE value::text
8   END AS processed
9 FROM json_data;

Memproses data berbeda berdasarkan tipe JSON.

Fungsi Equivalen di Database Lain

JSON_TYPE

MySQL

TYPEOF

Snowflake

JSON_TYPE

BigQuery

JSON_TYPE

DuckDB

Fungsi Terkait

JSON_TYPEOFJSON_ARRAY_LENGTH

Kembali ke PostgreSQL Functions

1	SELECT
2	JSONB_TYPEOF({}::jsonb) AS obj,
3	JSONB_TYPEOF([]::jsonb) AS arr,
4	JSONB_TYPEOF("hello"::jsonb) AS str,
5	JSONB_TYPEOF(123::jsonb) AS num,
6	JSONB_TYPEOF(true::jsonb) AS bool,
7	JSONB_TYPEOF(null::jsonb) AS nil;

1	SELECT *
2	FROM api_responses
3	WHERE JSONB_TYPEOF(data->items) = array;

1	SELECT
2	id,
3	CASE JSONB_TYPEOF(value)
4	WHEN array THEN JSONB_ARRAY_LENGTH(value)::text
5	WHEN object THEN object
6	WHEN string THEN value #>> {}
7	ELSE value::text
8	END AS processed
9	FROM json_data;