jsonb_extract

jsonb_extract functions extract field specified by json_path from JSONB. A series of functions are provided for different datatype.

  • jsonb_extract extract and return JSONB datatype
  • jsonb_extract_bool extract and return BOOLEAN datatype
  • jsonb_extract_int extract and return INT datatype
  • jsonb_extract_bigint extract and return BIGINT datatype
  • jsonb_extract_double extract and return DOUBLE datatype
  • jsonb_extract_STRING extract and return STRING datatype

Exception handling is as follows:

  • if the field specified by json_path does not exist, return NULL
  • if datatype of the field specified by json_path is not the same with type of jsonb_extract_t, return t if it can be cast to t else NULL

Syntax

BOOLEAN jsonb_extract_bool(JSONB j, VARCHAR json_path)

BIGINT jsonb_extract_bigint(JSONB j, VARCHAR json_path)

DOUBLE jsonb_extract_double(JSONB j, VARCHAR json_path)

jsonb_exists_path and jsonb_type

There are two extra functions to check field existence and type

  • jsonb_exists_path get the type as follows of the field specified by json_path, return NULL if it does not exist
    • object
    • array
    • null
    • bool
    • int
    • bigint
    • string

BOOLEAN jsonb_exists_path(JSONB j, VARCHAR json_path)

STRING jsonb_type(JSONB j, VARCHAR json_path)

refer to jsonb tutorial for more.