Product Documentation

Appearance

All platforms

JSON_EXTRACT ​

Prerequisite: This function can be used by setting the enable_full_json configuration parameter. For more details, refer to JSON Functions.

Extracts values from a JSONB expression and returns the result as JSONB.

sql
JSON_EXTRACT(jsonb_expression, jsonpath)
jsonb_expression
Any expression that evaluates to JSONB, including a column reference.
jsonpath
Any valid JSON path expression. This must be a constant string literal; otherwise, an error will be thrown.

Examples ​

sql
select json_extract('{
  "INTC": {
    "name": "Intel Corporation",
    "open": 49.20,
    "high": 49.38,
    "low": 47.45,
    "recent_closing_prices": [50.25,50.39,50.76,50.50,48.00]
  }
}', '$.INTC.recent_closing_prices[2]');
-- 50.76

For more examples of valid JSON path expression, refer to JSON Path Expressions.