JSON_OBJECT_PICK (Lakehouse v1)
Creates a new JSON object containing only the specified keys from the input JSON object. If a specified key doesn’t exist in the input object, it is omitted from the result.
SQL Syntax
Section titled “SQL Syntax”json_object_pick(<json_object>, <key1> [, <key2>, ...])Parameters
Section titled “Parameters”| Parameter | Description |
|---|---|
| json_object | A JSON object (VARIANT type) from which to pick keys. |
| key1, key2, … | One or more string literals representing the keys to be included in the result object. |
Return Type
Section titled “Return Type”Returns a VARIANT containing a new JSON object with only the specified keys and their corresponding values.
SQL Examples
Section titled “SQL Examples”Pick a single key:
SELECT json_object_pick('{"a":1,"b":2,"c":3}'::VARIANT, 'a');-- Result: {"a":1}Pick multiple keys:
SELECT json_object_pick('{"a":1,"b":2,"d":4}'::VARIANT, 'a', 'b');-- Result: {"a":1,"b":2}Pick with non-existent key (non-existent keys are ignored):
SELECT json_object_pick('{"a":1,"b":2,"d":4}'::VARIANT, 'a', 'c');-- Result: {"a":1}